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