sqlsql-serversql-server-2008-r2

Reference an alias elsewhere in the SELECT list


I have the following query:

select FirstName, LastName,
    case
        when LastName = 'Jones'
        then 'N/A'
    end as Other,
    case 
        when Other is not null 
        then 1 
    else 0 as Flag

The Flag value depends on Other but as Other is an alias, the Flag field does not recognize Other.

I guess, I can use a select within a select. Is there a better way for Flag to recognize the Other alias column?


Solution

  • You can't refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:

    SELECT 
      FirstName, LastName, Other, 
      Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
    FROM 
    (
      SELECT FirstName, LastName,
        CASE WHEN LastName = 'Jones'
         THEN 'N/A'
        END AS Other
      FROM dbo.table_name
    ) AS x;