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

IGNORE NULLS and RESPECT NULLS not recognized for LAG and LEAD

Hey all!

Today I noticed that SQL Prompt seems to be blissfully unaware of the new IGNORE NULLS and RESPECT NULLS optional keywords for the LAG and LEAD functions. However, when used with FIRST_VALUE or LAST_VALUE, they are recongized just fine.
(In your defense, Management Studio itself bothces this up with both LAG and LEAD, as well as FIRST_VALUE and LAST_VALUE, so you still win ;) )

Repro example, on e.g. AdventureWorks2017 demo database.

Start with this query:
SELECT p.ProductID,<br>       p.Name,<br>       p.ListPrice,<br>       p.ProductLine,<br>       LEAD (p.ListPrice, 2) OVER (PARTITION BY p.ProductLine ORDER BY p.ListPrice) AS LaterPrice<br>FROM   Production.Product AS p;<br>
Between LEAD (p.ListPrice, 2) and OVER, type "ignore nulls". Observe that this keyword is not included in the list of syntax suggestions.

Choose Format SQL (Ctrl-K, Ctrl-Y) and notice that now, the keyword is only partially capitalized:

SELECT p.ProductID,<br>       p.Name,<br>       p.ListPrice,<br>       p.ProductLine,<br>       LEAD (p.ListPrice, 2) ignore NULLS OVER (PARTITION BY p.ProductLine ORDER BY p.ListPrice) AS LaterPrice<br>FROM   Production.Product AS p;<br>
Shown with LEAD above, but the exact same happens with LAG.

It would be nice if this can be fixed in a future version.

Oh, and it would also be nice to not have all these options on one long line, I would prefer the Format SQL to produce output like this:

SELECT p.ProductID,<br>       p.Name,<br>       p.ListPrice,<br>       p.ProductLine,<br>       LEAD (p.ListPrice, 2) IGNORE NULLS<br>                             OVER (PARTITION BY p.ProductLine<br>                                   ORDER BY p.ListPrice) AS LaterPrice<br>FROM   Production.Product AS p;<br>
But that is just an afterthought. The lacking support for IGNORE / RESPECT NULLS is what I actually wanted to report.
--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)

Answers

Sign In or Register to comment.