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 ... <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
select customername as thename, avg(salesamount) as averagesales
where salesdatetime >= (sysdate-30)
group by customername
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.