"Format SQL" fails on query hint
HugoKornelis
Posts: 40 Bronze 5
in SQL Prompt
Don't know if this is due to a recent upgrade, or due to a specific query. But here are the steps to reproduce.
(My configuration: SSMS 19.2.56.2; SQL Prompt 10.14.3.4664; SQL Server 2022, RTM-CU8-GDR, 16.0.4080.1, running on locally on my laptop).
Open a new query editor window, connect to AdventureWorks2017, type this query:
SELECT soh.SalesPersonID,
sod.OrderQty,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE sod.UnitPrice * sod.OrderQty > 5000
ORDER BY soh.SalesPersonID;
sod.OrderQty,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE sod.UnitPrice * sod.OrderQty > 5000
ORDER BY soh.SalesPersonID;
Hit Ctrl-K - Ctrl-Y (or select Format SQL from the SQL Prompt menu) - works.
Change the query window to:
SELECT soh.SalesPersonID,
COUNT_BIG (*) AS AmtSales,
SUM (sod.OrderQty) AS TotalQty,
MAX (soh.OrderDate) AS LastSale
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE sod.UnitPrice * sod.OrderQty > 5000
GROUP BY soh.SalesPersonID
ORDER BY soh.SalesPersonID
OPTION (ORDER GROUP);
COUNT_BIG (*) AS AmtSales,
SUM (sod.OrderQty) AS TotalQty,
MAX (soh.OrderDate) AS LastSale
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE sod.UnitPrice * sod.OrderQty > 5000
GROUP BY soh.SalesPersonID
ORDER BY soh.SalesPersonID
OPTION (ORDER GROUP);
Try Ctrl-K, Ctrl-Y (or Format SQL) again. Result: error.
--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Tagged:
Answers
I've managed to reproduce this issue, and so I'm going to check this with the developers
I'll update you accordingly
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?
If you can update to the latest version of SQL Prompt, this has now been fixed
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?