Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Error Message:
Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Severity level:
16.

Description:
This error message appears, when a subquery that is not introduced with EXISTS contains more that one expression in the SELECT list.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. You need to remove all but one expression from the SELECT list.

Versions:
All versions of SQL Server.

Example(s):
USE Northwind
SELECT t1.*
  FROM dbo.Orders t1
 WHERE t1.OrderDate =
  (SELECT MAX(OrderDate), CustomerID
     FROM dbo.Orders
    WHERE t1.CustomerID = CustomerID
    GROUP BY CustomerID)

Remarks:
In the above example we try to retrieve the most current order from each customer. The correlation between the subquery and the outer SELECT is created via t1.CustomerID = CustomerID. The error is raised, because we entered two expressions in the SELECT list of the subquery (MAX(OrderDate), CustomerID). To turn the query into a syntactically correct one, we need to modify the query to:
USE Northwind
SELECT t1.*
  FROM dbo.Orders t1
 WHERE t1.OrderDate =
  (SELECT MAX(OrderDate)
     FROM dbo.Orders
    WHERE t1.CustomerID = CustomerID)




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |