SMSS Falsely Complaining About Invalid Columns

Question

The following code runs and behaves as expected. However, as the image shows, SSMS is complaining that the columns from the GEN_NEW sub-select are invalid. (Actual error popping up next to the mouse is "Invalid column name".)

Am I doing something wrong? How can I convince the SSMS parser that those columns really are valid? It's annoying to have SSMS barking when the code actually works.

    MERGE
     INTO
           General.Associate  GA
    USING
           (
             SELECT
                      GEN_ID
                     ,GEN_JSON
               FROM
                      OPENJSON( @jsonResult, '$.entities' )
                      WITH (
                              GEN_ID   UNIQUEIDENTIFIER  '$.id'
                             ,GEN_JSON NVARCHAR(MAX)     '$' AS JSON
                           )
           )  GEN_NEW
           ON GA.GEN_ID = GEN_NEW.GEN_ID
     WHEN  MATCHED
           AND GA.GEN_JSON <> GEN_NEW.GEN_JSON
     THEN  UPDATE
              SET
                   GA.GEN_JSON = GEN_NEW.GEN_JSON
     WHEN  NOT MATCHED BY TARGET
     THEN  INSERT (GEN_JSON)
           VALUES (GEN_NEW.GEN_JSON)
    ;

Here's a snapshot of SSMS's complaints:

SSMS Version: 19.0.2 SQL Server Version: 15.0.2101.7

Edit: This is not an intellisense issue as Peter B suggested. The columns in question are part of the sub-query, not something outside the query.

Does this answer your question? [SQL Server. How to refresh the intellisense?](https://stackoverflow.com/questions/7825287/sql-server-how-to-refresh-the-intellisense) — Peter B, May 12 '23 at 21:01
No. I'm very familiar with refreshing intellisense. I'm not sure why that would do it. It's not referencing anything outside the script. But, to confirm, I did it anyway. — StoneGiant, May 12 '23 at 21:05
I think it's ridiculous that this is being voted to close. @PeterB Please actually read the post and review the code. You will see that it's not a duplicate of the indicated question. — StoneGiant, May 12 '23 at 21:13
Not sure what stackoverflow can do with an ssms bug, but i'll bite. Does it help to add a (GEN_ID,GEN_JSON) column names after GEN_NEW subquery alias? — siggemannen, May 12 '23 at 21:18

score 2 · Accepted Answer · answered May 12 '23 at 21:54

Someone reported this issue with OPENJSON here. - Bizarrely the below solves the issue (redundant ColName AS ColName)

USING
       (
         SELECT
                  GEN_ID AS GEN_ID
                 ,GEN_JSON AS GEN_JSON
           FROM
                  OPENJSON( @jsonResult, '$.entities' )
                  WITH (
                          GEN_ID   UNIQUEIDENTIFIER  '$.id'
                         ,GEN_JSON NVARCHAR(MAX)     '$' AS JSON
                       )
       )  GEN_NEW

You can also do GEN_NEW(GEN_ID, GEN_JSON) as suggested by @siggemannen

2

Upvoted the bug report – siggemannen May 12 '23 at 23:32

SMSS Falsely Complaining About Invalid Columns

1 Answers1