What are the challenges you face when working across database platforms? Take the survey
Options

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

  • Options
    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
  • Options
    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.
  • Options
    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
  • Options
    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.