spurious error in values statement

Question

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

Answer 1

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.

Answer 2

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).

Answer 3

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.

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.