Re: Use derived expression in select statement

"Richard Sydney-Smith" <richard(at)ibisaustralia(dot)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