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.

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: <pre>select tmpTbl.colx, ... from (select ... <computed column> colx, ... from realTable1, realTable2, ... where ...) tmpTbl where tmpTbl.colx = <some value></pre> 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): <pre>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;</pre> 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.