SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select statement with alias in where clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WindChaser
Posting Yak Master

224 Posts

Posted - 03/12/2013 :  13:02:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 03/12/2013 :  13:09:43  Show Profile  Reply with Quote
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 ) s
Where CN = 'Johnson'
But that is likely to have performance impact.
Go to Top of Page

WindChaser
Posting Yak Master

224 Posts

Posted - 03/12/2013 :  14:02:45  Show Profile  Reply with Quote
Crystal clear. So I'll keep on taking the long way home. Thanks for your time James!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000