spurious error in values statement

stephen HULBERT 1 Reputation point
2021-06-21T09:50:14.483+00:00

The following SQL snippet shows errors in SSMS.
The italics are the underlining from SSMS.
The code runs fine without error.
What is going on?

SELECT
ry.*
FROM (
SELECT
MAX(ry0.ReportingYear) ReportingYear
FROM (
VALUES
(1, 'AAA', 'BBB')
, (2, 'AAA', 'BBB')​
, (3, 'AAA', 'BBB')
, (4, 'AAA', 'BBB')
, (5, 'AAA', 'BBB')
) ry0 ( ID, ReportingYear, Filler1 )
) ry ( ReportingYear )

Severity Code Description Project File Line
Error The multi-part identifier "ry0.ReportingYear" could not be bound. test2106 SQLQuery37.sql 12
Error The column prefix 'ry' does not match with a table name or alias name used in the query. test2106 SQLQuery37.sql 9
Error Incorrect syntax near '​'. Expecting ')', or ','. test2106 SQLQuery37.sql 16
Error Incorrect syntax near 'ReportingYear'. Expecting '(', or SELECT. test2106 SQLQuery37.sql 21
Error Incorrect syntax near 'ID'. Expecting '(', or SELECT. test2106 SQLQuery37.sql 20
Error Incorrect syntax near '5'. test2106 SQLQuery37.sql 19
Error Incorrect syntax near '4'. test2106 SQLQuery37.sql 18
Error Incorrect syntax near '3'. test2106 SQLQuery37.sql 17
Error Incorrect syntax near ','. Expecting AS, ID, or QUOTED_ID. test2106 SQLQuery37.sql 18
Error Incorrect syntax near ''BBB''. test2106 SQLQuery37.sql 17
Error Incorrect syntax near ''BBB''. test2106 SQLQuery37.sql 18
Error Incorrect syntax near ''BBB''. test2106 SQLQuery37.sql 19
Error Incorrect syntax near ''AAA''. test2106 SQLQuery37.sql 17
Error Incorrect syntax near ''AAA''. test2106 SQLQuery37.sql 18
Error Incorrect syntax near ''AAA''. test2106 SQLQuery37.sql 19

Microsoft SQL Server Management Studio 14.0.17289.0
Microsoft Analysis Services Client Tools 14.0.1016.283
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.9600.19963
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.9600

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 40,576 Reputation points
    2021-06-21T10:13:41.947+00:00

    The following SQL snippet shows errors in SSMS.
    The italics are the underlining from SSMS.

    That outlinie comes from SSMS IntelliSense, which is not that intelligent if you work with such "virtual" objects; you can ignore that.

    0 comments No comments

  2. Viorel 111.5K Reputation points
    2021-06-21T10:24:31.69+00:00

    Check this script:

    SELECT
    ry.*
    FROM (
    SELECT
    MAX(ry0.ReportingYear) ReportingYear
    FROM (
    VALUES
    (1, 'AAA', 'BBB')
    , (2, 'AAA', 'BBB')
    , (3, 'AAA', 'BBB')
    , (4, 'AAA', 'BBB')
    , (5, 'AAA', 'BBB')
    ) ry0 ( ID, ReportingYear, Filler1 )
    ) ry ( ReportingYear )
    

    Copy and paste using Clipboard to new SSMS window. (Ignore the errors from Errors window that are related to other files).

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2021-06-22T01:36:52.293+00:00

    Hi @stephen HULBERT

    Welcome to Microsoft Q&A!

    Since this query is working fine, you could ignore these errors.

    Most Microsoft IntelliSense options are on by default. You can turn off an IntelliSense option and instead invoke it through a menu command or keystroke combination.

    To modify Transact-SQL IntelliSense options

    1. On the Tools menu, click Options.
    2. Expand Text Editor, expand Transact-SQL, and then click IntelliSense.
    3. Clear the check boxes for the IntelliSense options that you do not want.

    107943-sample.png

    Then you could copy and paste this code in a new SSMS window and all errors disappear.

    You could refer more details in this article.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments