case statement sub query need to return multiple values

Question

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

Answer 1

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.

Answer 2

A subquery used in scalar context like yours -- the CASE expression must return a single (scalar) value, -- should also return a single value. It's hard to guess what you want, since you did not explain your problem in human language, nor did you supply table structures or sample data, but I think, since you already join all the necessary tables, you may want something like this:

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