Filter based on an aliased column name

Question

I'm using SqlServer 2005 and I have a column that I named.

The query is something like:

SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE myAlias IS NOT NULL

However, this gives me the error:

"Invalid column name 'myAlias'."

Is there a way to get around this? In the past I've included the column definition in either the WHERE or the HAVING section, but those were mostly simple, IE COUNT(*) or whatever. I can include the whole column definition in this ad-hoc query, but if for some reason I needed to do this in a production query I'd prefer to have the column definition only once so I don't have to update both (and forget to do one at some point)

Answer 1

put the case in the where. SQL Server will be smart enough to just evaluate it one time so you aren't really duplicating the code:

SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE CASE WHEN <snip extensive column definition> END IS NOT NULL

you could wrap it in a derived table:

SELECT dt.id, dt.myAlias
    FROM (
          SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
          FROM myTable
         ) dt
    WHERE dt.myAlias IS NOT NULL

However, I try to avoid having derived tables without a restrictive WHERE. You can try it to see if it affects performance or not.

Answer 2

Put the same CASE statement in the WHERE clause:

SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE CASE WHEN <snip extensive column definition> END IS NOT NULL

EDIT

Another option is to nest the query:

SELECT id, myAlias
FROM (
    SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
    FROM myTable
) AS subTable
WHERE myAlias IS NOT NULL

(Edit: removed HAVING option, as this was incorrect (thanks @OMG Ponies))

Answer 3

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, myAlias
FROM
(
    SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
    FROM myTable
) data
WHERE myAlias IS NOT NULL

Answer 4

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