Filter based on an aliased column name
You can't reference aliases in a where clause like that... you either have to duplicate the CASE in the WHERE, or you can use a subquery like this:
SELECT id, myAliasFROM( SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias FROM myTable) dataWHERE myAlias IS NOT NULL
Using CTEs is also an option:
;with cte (id, myAlias) as (select id, case when <snip extensive column definition> end as myAlias from myTable) select id, myAlias from cte where myAlias is not null
Put the same CASE
statement in the WHERE
clause:
SELECT id, CASE WHEN <snip extensive column definition> END AS myAliasFROM myTableWHERE CASE WHEN <snip extensive column definition> END IS NOT NULL
EDIT
Another option is to nest the query:
SELECT id, myAliasFROM ( SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias FROM myTable) AS subTableWHERE myAlias IS NOT NULL
(Edit: removed HAVING
option, as this was incorrect (thanks @OMG Ponies))