SQL: Using a column created in the SELECT in the WHERE CLAUSE

30 pts.
Tags:
SELECT statement
SQL
SQL statements
Can I use a column in the WHERE clause that I created with 'AS' in the WHERE clause. I have tried and SQL tells me that the column is not in the table? Thanks, Ray
ASKED: July 7, 2008  7:26 PM
UPDATED: July 9, 2008  3:26 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Sorry, I had a typing error in the question. I ment to say …”with ‘AS’ in the SELECT clause”…
Ray

You need to use the origional Column name and not the alias when referencing the column as part of the WHERE clause. For Example:

<pre>SELECT Col3-Col1 AS Value1
FROM YourTable
WHERE Col3-Col1 > 3</pre>

Alternatively, you can “push down” the query into a nested subquery, and use the column alias in the outer query’s WHERE clause

<pre>SELECT Value1
FROM (
SELECT Col3-Col1 AS Value1
FROM YourTable
) AS dt
WHERE Value1 > 3</pre>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    65,660 pointsBadges:
    report
  • RayT
    Thanks for the feedback. So, is it correct that an alias can't be used in the WHERE clause? The actualy sql statement I am working with is a lot more complicated than my example. It is joining 2 tables to the primary table and depending on the existance of a matching row in table A, it pulls a customer name from table A, otherwise it pulls the name from table B. I was trying to use the case when function to fill a derived variable with the name and then do a WHERE derived name = "user search value". I can send the actual query if you want to see it. I am new at SQL, so I may not be doing this the most efficient way. Thanks again, Ray
    30 pointsBadges:
    report
  • RudyLimeback
    yes, it is correct that you cannot use a column alias in the WHERE clause if the same query have you tried nestiung your entire query in a subquery as i showed in the answer?
    440 pointsBadges:
    report
  • RudyLimeback
    sorry, that should have said in the same query... there appears to be no "preview" feature
    440 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following