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)

Solution 1

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

Solution 2

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

Solution 3

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))

Solution 4

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.

View more solutions

Comments

[Nathan Koop]

@KM, my worry was more about me updating one CASE and forgetting to update the second. Like you I expect that SQL Server will optimize out the second call

[ZygD]

@OMG ponies: try it in SQL Server. without group by, it's the same as WHERE pretty much... msdn.microsoft.com/en-us/library/ms180199%28SQL.90%29.aspx So Codesleuth's solution may have been correct without use of derived table

[Nathan Koop]

Thanks Philip, I'm not familiar with CTE's, it looks as though it's a temp view. Would that be a fair statement?

[OMG Ponies]

@gbn: Weird! Will definitely try when I get to work. Sorry about that, Codesleuth.

[ZygD]

@OMG ponies: At least this is documented :-)

[Nathan Koop]

Alas, I hoped it would be simpler.

[Codesleuth]

I can't get it to work as I originally intended; "OMG Ponies" was right as far as I can tell :)

[Nathan Koop]

Thanks I'll take a look into it

[Nathan Koop]

@OMG Ponies, @gbn, I can't get it work like you are stating in SQL2005 I presume you're using SQL2008?

[Nick N.]

Me too, there should be a more generic solution actually