[SA 7.2.338 Pro] - Code formatting issue [FIXED]

[SA 7.2.338 Pro] - Code formatting issue [FIXED]

Formatting SELECT statements falters when multiple function calls are involved in SELECT part of the query. The code below is formatted properly with line #5 commented:

Code:

SELECT
j.jarat_id AS id_jarat
,j.nyomvon_id AS id_nyomvon
,j.jarat_szam AS jarat_szam
--#5 ,CAST(STUFF(STUFF(j.jarat_indulas ,5 ,0 ,':') ,3 ,0 ,':') AS TIME) AS jarat_indulas
,j.jarat_tureshatar AS jarat_tureshatar
,j.jarat_helyi_is AS jarat_helyi_is
,j.jarat_korjarat_e AS jarat_korjarat_e
,CAST(j.kozlekedesi_jel_aoeuaoeu_aoeuaoeu AS NVARCHAR(128)) AS kozlekedesi_jel
,j.menetido AS id_menetido
,j.datum_tol AS datum_tol
,j.datum_ig AS datum_ig
,j.volan_id AS id_volan
,j.vonal_id AS id_vonal
FROM
[DATA].dbo.jarat AS j
INNER JOIN
[DATA].dbo.Forda AS f
ON f.jarat_id = j.jarat_id
WHERE 1 = 1
AND j.datum_tol <= GETDATE()
AND (GETDATE() < j.datum_ig + 1 OR j.datum_ig IS NULL)
AND f.datum_tol <= GETDATE()
AND (GETDATE() < f.datum_ig + 1 OR f.datum_ig IS NULL)
AND j.uzletag = ''

but the alias alignment is removed beyond that line when it is not:

Code:

SELECT
j.jarat_id AS id_jarat
,j.nyomvon_id AS id_nyomvon
,j.jarat_szam AS jarat_szam
,CAST(STUFF(STUFF(j.jarat_indulas ,5 ,0 ,':') ,3 ,0 ,':') AS TIME) AS jarat_indulas
,j.jarat_tureshatar AS jarat_tureshatar
,j.jarat_helyi_is AS jarat_helyi_is
,j.jarat_korjarat_e AS jarat_korjarat_e
,CAST(j.kozlekedesi_jel_aoeuaoeu_aoeuaoeu AS NVARCHAR(128)) AS kozlekedesi_jel
,j.menetido AS id_menetido
,j.datum_tol AS datum_tol
,j.datum_ig AS datum_ig
,j.volan_id AS id_volan
,j.vonal_id AS id_vonal
FROM
[DATA].dbo.jarat AS j
INNER JOIN
[DATA].dbo.Forda AS f
ON f.jarat_id = j.jarat_id
WHERE 1 = 1
AND j.datum_tol <= GETDATE()
AND (GETDATE() < j.datum_ig + 1 OR j.datum_ig IS NULL)
AND f.datum_tol <= GETDATE()
AND (GETDATE() < f.datum_ig + 1 OR f.datum_ig IS NULL)
AND j.uzletag = ''

Last edited by gemisigo on Mon Oct 26, 2015 9:17 am; edited 1 time in total

Here is what I get after formatting with all default options, I don't see anything lost here

Code:

/************************************************************
* Code formatted by SoftTree SQL Assistant © v7.3.396
* Time: 9/11/2015 9:21:22 AM
************************************************************/

SELECT j.jarat_id AS id_jarat
,j.nyomvon_id AS id_nyomvon
,j.jarat_szam AS jarat_szam
,CAST(
STUFF(STUFF(j.jarat_indulas ,5 ,0 ,':') ,3 ,0 ,':') AS TIME
) AS jarat_indulas
,j.jarat_tureshatar AS jarat_tureshatar
,j.jarat_helyi_is AS jarat_helyi_is
,j.jarat_korjarat_e AS jarat_korjarat_e
,CAST(j.kozlekedesi_jel_aoeuaoeu_aoeuaoeu AS NVARCHAR(128)) AS
kozlekedesi_jel
,j.menetido AS id_menetido
,j.datum_tol AS datum_tol
,j.datum_ig AS datum_ig
,j.volan_id AS id_volan
,j.vonal_id AS id_vonal
FROM [DATA].dbo.jarat AS j
INNER JOIN [DATA].dbo.Forda AS f
ON f.jarat_id = j.jarat_id
WHERE 1 = 1
AND j.datum_tol<= GETDATE()
AND (GETDATE()<j.datum_ig+1 OR j.datum_ig IS NULL)
AND f.datum_tol<= GETDATE()
AND (GETDATE()<f.datum_ig+1 OR f.datum_ig IS NULL)
AND j.uzletag = ''

note that wrapping moves some aliases to the next line, to avoid this, set the text wrapping option in formatting section to 200 chars or something else, the default is just 80 and in my humble opinion is 80 is not enough in many cases.

I have Line Length for Code Wrapping set to 220 so I don't think that would be the cause. Besides the result you get is ruined as well. Notice how the first three lines have their aliases aligned (though significantly closer to the beginning of the line than in first case with the renegade line commented) in contrast to the following lines that have their aliases not aligned at all.

	Quote:
	Besides the result you get is ruined as well.

I suspect it's the result of (CAST ... AS ... ) that throws off alias alignment in this case. I have opened a bug ticket #SA27087 for that issue.

Probably, but might not be the cause entirely on its own, as there's another line (#9) containing CAST ... AS and that one does not lead to misalignmend.

A fix for this issue is going to be available in version 7.3

That's good news, thank you very much.