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

BUG: "Format SQL" introduces syntax errors

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)

Comments

  • Options

    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
  • Options
    Hi there,

    Our development team have advised a fix for this has been included in the recent release of SQL Prompt v10.14.21

    Release notes:
    10.14.21.9884 - Released on 14 June 2024
    Features
    Added support for Synapse Serverless SQL Pools.
    Fixes
    • Fixed Prompt+ in SSMS 18.
    • Fixed few issues which caused data types to be missing/invalid for aliased columns.
    • Fixed Apply Column Alias Style with WINDOW OVER clause.
    • Fixed invalid data type of aliased columns when using arithmetic operators.
    This version can be accessed directly here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.21.9884.exe


    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    sympathizetracksympathizetrack Posts: 2 New member
    Hi there,

    Our development team have advised a fix for this has been included in the recent release of SQL Prompt v10.14.21

    Release notes:
    10.14.21.9884 - Released on 14 June 2024
    Features
    Added support for Synapse Serverless SQL Pools.
    Fixes
    • Fixed Prompt+ in SSMS 18.
    • Fixed few issues which caused data types to be missing/invalid for aliased columns.
    • Fixed Apply Column Alias Style with WINDOW OVER clause.
    • Fixed invalid data type of aliased columns when using arithmetic operators.
    This version can be accessed directly here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.21.9884.exe geometry dash lite
    I have downloaded it successfully. Many thanks!
Sign In or Register to comment.