Referencing a calculated column in a SQL clause

SQL Server
This is our SQL code:  
Select addrid,addrkey,addrline1,addrline2,addrline3,
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 on dbo.address.addrlnkjob = WHERE (addrlocatr = 'g') and addrid = '0000035682
My question is, how do I reference a calculated column in the WHERE clause?

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:

tmpTbl.colx, ...
(select ... <computed column> colx, ... from realTable1, realTable2, ... where ...) tmpTbl
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.

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.

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


Share this item with your network: