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

Format fails when column alias matches existing column

I have some SQL similar to the below example and it fails to format because it adds the table alias to the column alias when the column alias is the same as an existing column inside the IF, I need to change the column alias to something else for it to format, e.g. [is_ms_shipped2]. SQL Prompt 11.3.6.21421 on Azure SQL database.

SELECT
    *
  , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CURRENT_DATE, [o].[create_date]) BETWEEN 0 AND 7, 1, [o].[is_ms_shipped]) AS [BIT]) AS [is_ms_shipped]
FROM [sys].[objects] AS [o];
Chris
1

Comments

5 comments

Sort by
  • Christian Perez
    Official comment

    Hi Chris,

     

    Hope you are doing well, sorry to hear you are having a little trouble with your SQL Prompt install with respect to aliases. There should be a setting SQL Prompt > Options > Inserted Code > Aliases that should allow you to either stop Prompt from automatically generating alias names, or to let you manually define an alias to use for a particular object. 

     

    Would the above work for you as a solution or are you frequently running into this issue where the alias is identical?

    Christian Perez
  • Chris

    The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax error. If I change my alias to something different to the name of the column used inside the IIF, e.g. [is_ms_shipped2], then SQL Prompt can format it just fine. I've only noticed it happening with the combination of code above.

    Chris
    0
  • Chris

    Seems to be CURRENT_DATE it doesn't like, this works fine:

    SELECT
       *
     , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, [o].[is_ms_shipped]) AS [BIT]) AS [is_ms_shipped]
    FROM [sys].[objects] AS [o];

    I've also noticed that using CURRENT_DATE breaks intellisense, not sure if that SQL Prompt or SSMS?

    Chris
    0
  • Christian Perez

    Hi Chris,

     

    Thanks for the update, let me test your code and see if I can reproduce this behavior locally.

    Christian Perez
    0
  • Chris

    Also seeing the same behaviour with REGEXP_LIKE:

    SELECT    *  , REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc] FROM [sys].[objects] AS [o];

    Using [type_desc2] formats the code without error, but then SQL Prompt puts REGEXP_REPLACE in square brackets, which is not valid SQL, so fails to execute.

    Chris
    0

Add comment

Please sign in to leave a comment.