Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2013-03-12 : 13:02:02
|
| Hi folks!Consider the following query:Select Account, RTRIM(ClientName) as CN from Clients Where CN = 'Johnson'This query does not work because it refuses to have the alias CN in the Where clause. My only solution thus far has been to redefine the search parameter:Select Account, RTRIM(ClientName) as CN from Clients Where RTRIM(ClientName) = 'Johnson'The above is a very simple example but in real life this issue can lead to some pretty long-texted queries. So is there any special way to include an alias in a where clause? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-12 : 13:09:43
|
The behavior you observed is a result of the sequence in which the query is logically processed - see this page for the logical query processing phases. http://msdn.microsoft.com/en-us/library/ms189499%28v=sql.105%29.aspx The select clause comes after the where clause, so an alias defined in select is not available to the where clause.The only workaround, other than repeating the expression, is to use a subquery or cte - for example:SELECT * FROM( Select Account, RTRIM(ClientName) as CN from Clients ) sWhere CN = 'Johnson' But that is likely to have performance impact. |
 |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2013-03-12 : 14:02:45
|
| Crystal clear. So I'll keep on taking the long way home. Thanks for your time James!!! |
|
|
|
|
|
|
Select statement with alias in where clause