How is SQL Prompt helping your team? Share your experience.
GREATEST and LAST_VALUE IGNORE NULLS
ccparkhill
Posts: 15 Bronze 1
in SQL Prompt
SQL Prompt incorrectly formats GREATEST and IGNORE NULLS ( as well as PARTITION) with square brackets, which breaks the query, I'm using Azure SQL Database. So you end up with:
[GREATEST] ([x].[ColB], [x].[ColC]) AS [Greatest]
and
, LAST_VALUE([x].[ColA]) [IGNORE] [NULLS] OVER ([PARTITION] BY [x].[ID] ORDER BY [ColC] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [LastValueIgnoreNulls]
instead of:
[GREATEST] ([x].[ColB], [x].[ColC]) AS [Greatest]
and
, LAST_VALUE([x].[ColA]) [IGNORE] [NULLS] OVER ([PARTITION] BY [x].[ID] ORDER BY [ColC] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [LastValueIgnoreNulls]
instead of:
SELECT
[x].[ID]
, [x].[ColA]
, [x].[ColB]
, [x].[ColC]
, LAST_VALUE([x].[ColA]) IGNORE NULLS OVER (PARTITION BY [x].[ID] ORDER BY [ColC] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [LastValueIgnoreNulls]
, GREATEST ([x].[ColB], [x].[ColC]) AS [Greatest]
FROM
(
VALUES
(12345, NULL, -1, 5)
, (12345, 20000, 7, 2)
, (12345, NULL, 1, 1)
, (12345, 1000, 7, 1)
, (12345, 100, 7, 10)
) AS [x] ([ID], [ColA], [ColB], [ColC]);
