Using a Case statment inside a Select query to get multiple types.- getting an expression error

Question

Am trying to place a case statement inside a sub query which will allow me to display multiple options depending on which condition is being met.

So far I have wrote something like

(SELECT A.ValueOne, B.Value2, C.Value3
    CASE 
        WHEN A.ValueOne = 0 THEN 'HELLO'
        WHEN A.ValueOne = 1 THEN 'GOOD BYE' 
        WHEN B.Value2 IS NOT NULL THEN 'GOOD MORNING'
        WHEN C.Value3 IS NOT NULL THEN 'GOOD EVENING'
        END 
    FROM TableD D
        LEFT OUTER JOIN TableE E ON D.ID = E.TableDID 
        LEFT OUTER JOIN TableA A ON E.TableEID = B.ID
        LEFT OUTER JOIN TableF ON D.ID = TableF.TableDID
        LEFT OUTER JOIN TableB B ON A.ID = B.ID 
        LEFT OUTER JOIN TableG G ON B.TableGID = G.ID
        LEFT OUTER JOIN TableH H ON TableF.TableHID = TableH.ID
    LEFT OUTER JOIN TableC C ON H.TableC = C.ID
    WHERE D.ID = TableD.ID) 
    AS [Greeting Type],

The problem is am getting an error saying I can only use a single expression in the select list.

Am not sure my code above is even valid, am just experimenting, my question is, is there a way to achieve this

Thanks for any help in advance.

Answer 1

You have a subquery in a context where a scalar subquery is expected. A scalar subquery can return one column and at most one row. Presumably, you want the output of the case statement. If so, this should do what you want:

(SELECT 
    CASE 
        WHEN A.ValueOne = 0 THEN 'HELLO'
        WHEN A.ValueOne = 1 THEN 'GOOD BYE' 
        WHEN B.Value2 IS NOT NULL THEN 'GOOD MORNING'
        WHEN C.Value3 IS NOT NULL THEN 'GOOD EVENING'
        END 
    FROM TableD D
        LEFT OUTER JOIN TableE E ON D.ID = E.TableDID 
        LEFT OUTER JOIN TableA A ON E.TableEID = B.ID
        LEFT OUTER JOIN TableF ON D.ID = TableF.TableDID
        LEFT OUTER JOIN TableB B ON A.ID = B.ID 
        LEFT OUTER JOIN TableG G ON B.TableGID = G.ID
        LEFT OUTER JOIN TableH H ON TableF.TableHID = TableH.ID
    LEFT OUTER JOIN TableC C ON H.TableC = C.ID
    WHERE D.ID = TableD.ID) 
    AS [Greeting Type],

Note that after running this query, you will probably get an error saying that you have more than one row in the result set. You can fix that by using top or apply.

If you actually want the other values as well, you can use the apply method in the from clause for that purpose.