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?
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;