question

stephenHULBERT-4384 avatar image
0 Votes"
stephenHULBERT-4384 asked MelissaMa-msft commented

spurious error in values statement

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
This query is fine. You can execute it and it will run well. Make sure that you use the latest version of SSMS and ignore the error you see - simply execute it as it is :-)

0 Votes 0 ·

Hi @stephenHULBERT-4384

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @stephenHULBERT-4384

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.




sample.png (19.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.