Competition: What’s your favorite Redgate tool? Enter now.

SQL Format is invalidating JOIN statements

dwilliamsondwilliamson Posts: 3 New member
edited September 5, 2016 6:59AM in SQL Prompt
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
		)

Comments

  • 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 [email protected] ?

    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
  • 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.
  • 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
  • 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
Sign In or Register to comment.