How can we help you today? How can we help you today?

GREATEST and LAST_VALUE IGNORE NULLS

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]);
ccparkhill
0

Comments

6 comments

Sort by
  • ATurner
    Hi ccparkhill

    I am just speaking to the team on this matter.
    ATurner
    0
  • ccparkhill
    I saw that IGNORE NULLS was mentioned in the latest release, but I still have the issue detailed above.
    ccparkhill
    0
  • ATurner
    Hi ccparkhill

    The team are still working on that one but the others should be in there.
    ATurner
    0
  • ATurner
    Hi ccparkhill

    This should be in the latest release of SQL Prompt, could you update and check if the issue is still occurring?
    ATurner
    0
  • ccparkhill
    Initially the newest release formatted the above query fine, but then it just seemed to revert to the previous behaviour.
    ccparkhill
    0
  • ATurner
    Hi ccparkhill 
    Apologies for the delayed response, what is the error you are getting now?
    ATurner
    0

Add comment

Please sign in to leave a comment.