Formatting in CASE WHEN expression removes dot if schemaname is present and dot followed by space

The following code dows not format properly, even not in SQL Prompt 10.0.01:

USE tempdb
GO

CREATE SCHEMA red;
GO

CREATE TABLE red.Gate( ColumnA INT )
GO

SELECT CASE WHEN Rg. ColumnA = 1
            THEN 1
            WHEN Rg. ColumnA = 2
            THEN 2
        END AS Col
FROM red.Gate AS Rg
GO

DROP TABLE red.Gate;
GO

DROP SCHEMA red;
GO


Answers

  • Hi atolmeijer,

    The underlying is due to a parsing/syntax error and thus SQL Prompt is unable to format it.

    You can see this by using the built in SSMS check mark for checking parsing.
    An expression of non-boolean type specified in a context where a condition is expected, near 'ColumnA' - 
    If it helps I found this stackoverflow post that's related to the error message - https://stackoverflow.com/questions/23928619/sql-server-an-expression-of-non-boolean-type-specified-in-a-context-where-a-co

    I hope this helps!

    Thanks,
    Dan


    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

  • Hi Dan, I don't think we're on the same page here. My point is that, after reformatting the above code, indeed the syntax becomes incorrect. However, the syntax before it was reformatted appears to be correct - the SSMS check mark shows success and running the query works without a problem.
    When SQL Prompt reformats the query, it removes the dot that separates the schema name with the column name. It does it only when there is a space between the dot and the column name. So I think this is a reformatting bug.
    Not a serious bug, but I came across it when updating existing queries in a project.
    Kind regards,
    Arno Tolmeijer
  • Hi Arno,

    Apologies! I understand what you mean now, I've been able to replicate the issue exactly. I'm now raising this with the development team as a bug with Prompt.

    Thanks,
    Dan

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our Help Center?

Sign In or Register to comment.