Why can't I use columns based on case down in my where clause

Transact-SQL

Thomas Koelle


I am writing a fairly simple sql, and I would like to write something like

Code Snippet

select

firstname as firstname,

case

when firstname = 'Peter' then 'yes'

else

'no'

end as whatever

from

MyTable

where

whatever = 'yes'

And this should then select out the rows where column number 2 is 'yes'.

It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.

But why



Top

Re: Why can't I use columns based on case down in my where clause

Kent Waldrop Se07


SQL Server does not have that feature. There have been a number of posts in the past in which the need was expressed to reference a column alias from a where clause. There was a similar question earlier this week. For this situation I would recommend writing out your case statement as part of the where clause. If you have a particularly complex reference you might want to create an inline function to use.

You can find a discussion about a similar issue from two days ago here. Here are some other previous discussions:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=89097&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1137002&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=803299&SiteID=1



Top