GREATEST and LAST_VALUE IGNORE NULLS
ccparkhill
Posts: 24 Bronze 2
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]);
Tagged:
Answers
I am just speaking to the team on this matter.
The team are still working on that one but the others should be in there.
This should be in the latest release of SQL Prompt, could you update and check if the issue is still occurring?
Apologies for the delayed response, what is the error you are getting now?