How can we help you today? How can we help you today?

SQL Format is invalidating JOIN statements

Running Format SQL is invalidating a previously valid statement. So far I've only encountered this on when joining tables.

Example scenario:
Setting up a sample table 
DECLARE @TestTable TABLE
	(
		ID BIGINT IDENTITY PRIMARY KEY
		,Value VARCHAR(MAX)
	)

INSERT INTO @TestTable (Value)
VALUES	('Test'),('Test'),('Test')
		,('Test'),('Test'),('Test')
		,('Test'),('Test'),(NULL)
SQL Statement prior to formatting 
SELECT	* 
FROM	@TestTable AS tt
	JOIN	@TestTable AS tt2
		ON	tt.ID = tt2.ID
			AND (tt.ID IN (1,2,3) OR tt.Value IS NULL)
SQL Statement after formatting (notice the IS NULL condition) 
SELECT	*
FROM	@TestTable AS tt
	JOIN @TestTable AS tt2
		ON tt.ID = tt2.ID
		   AND
		(
			tt.ID IN
	(
		1, 2, 3
	)
			OR   tt.ValueIS NULL
		)
dwilliamson
0

Comments

4 comments

Sort by
  • Ali D
    Hi

    Thanks for reporting this issue. Unfortunately, I haven’t been able to reproduce this behaviour here. Are you using the latest version of SQL Prompt (v7.3.474)? If not, you can download it from here.

    If the issue is still happening in the latest version, please could you send the style file responsible to SQLPromptTeam@red-gate.com ?

    To find the style file, use the bar at the top of style browser labelled "style folder" (by default it’s saved in %localappdata%Red GateSQL Prompt 7StylesV2 ).

    The style file will have the same name as your formatting style and the extension “.sqlpromptstylev2â€.

    Thanks
    Ali
    Ali D
    0
  • dwilliamson
    I was using version 7.3.0.473, but just issue was recreated with Version 7.3.0.474. I'll send the style file over ASAP.
    dwilliamson
    0
  • Harry Frankish
    Hi dwilliamson

    Thanks for sending the file over. We’ve reproduced the issue and will look into it.

    Other than the missing space between “tt.Value†and “ISâ€, is the statement formatted as you would like?

    Cheers
    Harry
    Harry Frankish
    0
  • David Priddle
    Hi dwilliamson,

    We've got a fix for this in the latest beta, which you can download from here or from Check for updates.

    Please let us know if it solves your problem!

    Best regards,

    David
    David Priddle
    0

Add comment

Please sign in to leave a comment.