Referencing a calculated column in a SQL clause

Tags:
SQL Server
T-SQL
This is our SQL code:  
Select addrid,addrkey,addrline1,addrline2,addrline3,
addrcity,addrplace,addrzipcod,addrtype,
addrphone,jobsconame,addrmc1,
CASE month(getdate()) WHEN 1 THEN addrjan WHEN 2 THEN addrfeb WHEN 3 THEN addrmar WHEN 4 THEN addrapr WHEN 5 THEN addrmay WHEN 6 THEN
addrjun WHEN 7 THEN addrjul WHEN 8 THEN addraug WHEN 9 THEN addrsep WHEN
10 THEN addroct WHEN 11 THEN addrnov WHEN 12 THEN addrdec END AS ThisMonth FROM dbo.address left outer join dbo.jobs on dbo.address.addrlnkjob = dbo.jobs.jobskey WHERE (addrlocatr = 'g') and addrid = '0000035682
My question is, how do I reference a calculated column in the WHERE clause?
ASKED: December 18, 2008  7:46 PM
UPDATED: April 19, 2013  3:44 PM

Answer Wiki

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

Generally, you cannot reference a column that is calculated in the Select clause in the Where clause. I have seen code where people have tried to do this and usually wound up duplicating the same calculation expressions in both the Select and Where clauses, which is very costly.

More importantly, you should really try to avoid using functions or dynamically computed values in Where clauses, as in most cases these will cause the Query Optimizer to degrade – often generating Full Table Scans – even on tables that could have been accessed efficiently through indexes.

A better solution is usually to create a temporary table (and appropriate columns) inside the From clause – you can then reference the temporary table columns in the Where clause and the Select clause of the outer query.

The syntax for this is:

select
tmpTbl.colx, ...
from
(select ... <computed column> colx, ... from realTable1, realTable2, ... where ...) tmpTbl
where
tmpTbl.colx = <some value>

Essentially, you are creating a one-time View inside the From clause, and then querying against the view.

Suppose you want to generate a list of preferred customers and their average sales amount over the past 30 days when their average sales amount for that period is greater than $1000. (Assume a simple table with customername, salesdatetime, and salesamount – one row per sales transaction.)

You could do this with (Oracle syntax):

select tmpTbl.thename, tmpTbl.averagesales
from (
select customername as thename, avg(salesamount) as averagesales
from customersales
where salesdatetime >= (sysdate-30)
group by customername
) tmpTbl
where tmpTbl.averagesales > 1000;

This creates a nested table (a temporary view) containing all the customers and their average sales for the past 30 days – one row per customer. Then the outer query returns just the ones where the average sales amount is greater than $1000.

Discuss This Question:  

 
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

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