Options

GREATEST and LAST_VALUE IGNORE NULLS

ccparkhillccparkhill Posts: 17 Bronze 1
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:

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

Sign In or Register to comment.