Sql-server – case statement sub query need to return multiple values

casesql serversubquery

Below query need to return multiple values 

select  

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject 
      WHERE StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id)
ELSE (SELECT Stationery.ImpFile from Stationery 
      WHERE Stationery.Stationery_Id = [Order].Stationery_Id)
END AS ImpFile

FROM [Order]
LEFT OUTER JOIN ClientProject ON ClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

but it was showing up an error

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression.

Not sure how to handle it. any help would be highly appreciated

Best Answer

This should work:

select  

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
    THEN StationeryClientProject.ImpFile
ELSE Stationery.ImpFile
END AS ImpFile

FROM [Order]
    LEFT OUTER JOIN ClientProject ON   ClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
    LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

The error message is caused by multiple rows being returned inside the case statement. Since you are already returning the required rows in your FROM clause, you can simply reference the required columns in the case statement.

Related Question