[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

why can't you use an alias in a where clause?

Posted on 2011-10-26
6
Medium Priority
?
265 Views
Last Modified: 2012-05-12
why doesn't this work?

select grade as rx from student_work
where rx > 23;

I get this error
Msg 207, Level 16, State 1, Line 1
Invalid column name 'rx'.

I just told it what rx is.   I know there is no reason to do this in this query but I need to know why it wont work because it also doesn't work here:

select sw.*,
(
      select COUNT(*)
      from student_work sw2
      where sw.student_name = sw2.student_name and sw2.grade >= sw.grade
      
) as rx
from student_work sw

where rx = 1


0
Comment
Question by:vrosas_03
6 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 800 total points
ID: 37029742
WHERE clauses are actually evaluated before the select statement is executed so logically speaking it makes no sense to be able to use an alias in the where clause.
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 800 total points
ID: 37029751
0
 
LVL 6

Assisted Solution

by:jonaska
jonaska earned 400 total points
ID: 37029823
You should try:
select *
from student_work sw
join
(
      select COUNT(*) AS rx
      from student_work sw2
      where sw.student_name = sw2.student_name and sw2.grade >= sw.grade
      
) AS sw2 ON sw.student_name = sw2.student_name
where rx = 1

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 total points
ID: 37029936
you can only reference objects in clauses as they are  defined in the scope of the from clause at the same level...
or in a correlated subquery from the parent level.

select x.*
from (
select sw.*,
(
      select COUNT(*) as K
      from student_work sw2
      where sw.student_name = sw2.student_name and sw2.grade >= sw.grade
     
) as rx
from student_work sw
) as x
where x.rx = 1



0
 
LVL 5

Assisted Solution

by:DavidMorrison
DavidMorrison earned 400 total points
ID: 37030010
As the others have eluded to it is to do with the compilation order of your query. In SQL Server column aliases are the last thing to be applied / complied after everything else has been done so it doesn't know what your alias is whilst compiling / parsing the query.

Also as an aside, inline sub queries as you have done in your bottom example perform really badly in sql server, consider doing this a different way


Thanks

Dave


0
 

Author Closing Comment

by:vrosas_03
ID: 37033042
I guess unlike procedural languages what is inside the parentheses is available outside.  And what is outside is unavailable inside.    This is because the from clause comprises a set.  You can't grab out of a set what hasn't been defined inside the set.  

  If a syntax doesn't work it is probably because it introduces ambiguity into the statement.

I'm just trying to get to where I can read and write something besides basic queries.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question