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

atolmeijer

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

DanC

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

Arno Tolmeijer

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

DanC

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