Referring to results of a sub query in main query

Question

Hi,

I have a sub query that returns one column, showing as GroupType, I then want to do a CASE function on this result within the main query, however I get an invalid column name when using the CASE statement.

Can i do this in SQL to do I have to refer to it by a different name

Answer 1

SELECT  CASE
        WHEN
        (
        SELECT  column
        FROM    othertable
        ) = 1
        THEN '1'
        ELSE '2'
        END
FROM    mytable

To reuse the subquery result:

SELECT  subvalue, CASE subvalue WHEN 1 THEN 1 ELSE 2 END
FROM    (
        SELECT  (
                SELECT  column
                FROM    othertable
                ) AS subvalue
        FROM    mytable
        ) q