Options

BUG: "Format SQL" introduces syntax errors

HugoKornelisHugoKornelis Posts: 40 Bronze 5
in SQL Prompt
Hi all,

The code below, though ill-formatted, is correct and works without error in SQL Server 2022:

SELECT frsx.ProductKey,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM (CAST (frsx.ShipDateKey AS bigint)) OVER w1 ,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAX (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MIN (frsx.ShipDateKey) OVER w1 ,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COUNT (*) OVER w1 &nbsp;<br>FROM&nbsp;&nbsp; dbo.FactResellerSales AS frsx<br>WINDOW w1 AS&nbsp; (ORDER BY frsx.DueDateKey<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);

When I want to use SQL Prompt to reformat the code (Ctrl-K, Ctrl-Y), it adds an extra AS keyword in some places where it should not exist, and then gives up with an error message:



The code now looks like this:

&nbsp;&nbsp;&nbsp; SELECT frsx.ProductKey,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; SUM (CAST (frsx.ShipDateKey AS bigint)) OVER w1 AS ,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; MAX (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; MIN (frsx.ShipDateKey) OVER w1 AS ,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; COUNT (frsx.RevisionNumber) OVER w1,<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; COUNT (*) OVER w1 AS&nbsp; <br>&nbsp;&nbsp; &nbsp;FROM&nbsp;&nbsp; dbo.FactResellerSales AS frsx<br>&nbsp;&nbsp; &nbsp;WINDOW w1 AS&nbsp; (ORDER BY frsx.DueDateKey<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);

SQL Server will (rightly) throw an error when I try to execute this "reformatted" code.
--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Tagged:
· Share on Twitter

Comments

  • Options
    Jon_KirkwoodJon_Kirkwood Posts: 348 Silver 4

    Hi @HugoKornelis

     

    Thank you for reaching out on the Redgate forums regarding this potential bug in SQL Prompt.

     

    I believe I have replicated the behaviour using SQL Server 2022 using the OVER clause. 

    Initial code executes as anticipated, then when using the formatting it introduces an AS clause causing syntax issues.


     Before formatting:


     After formatting:


    I've documented this and forwarded through to our development team. This forum post will be updated with any responses and/or fixes made available in a future release of SQL Prompt to target this concern.

     

    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
Sign In or Register to comment.