"Richard Sydney-Smith" <richard@ibisaustralia.com> writes:
> select dy_c , dy_sect as tsect, (cy_bfwd + dy_p1T4) as curr_bal from fclitot
> where dy_yr = 0 and (curr_bal) <-0.005
> In postgres it tells me curr_bal is not found.
As it should --- this is completely illegal according to the SQL
standard. It's not even well-defined. The SQL evaluation model
is that WHERE clause processing is done *before* evaluation of
the select list.
The usual way to avoid writing common subexpressions is to use a
sub-select, for example
select dy_c , dy_sect as tsect, curr_bal
from (select *, (cy_bfwd + dy_p1T4) as curr_bal from fclitot) as ss
where dy_yr = 0 and (curr_bal) <-0.005
This doesn't necessarily save you from evaluating the curr_bal
expression twice, mind you. It just saves you from writing it out
twice.
regards, tom lane
