Thread: Use derived expression in select statement

I am converting a database from Sybase and have found the following command
does not work in the same fashion.

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 the actual command used curr_bal is calculated from a very much longer
expression and rather than repeat the expression I simply refer to it by its
assigned name in sybase.

In postgres it tells me curr_bal is not found. is their a simular shorthand
available in postgres? curr_bal is calc from about 12 fields :{

thanks

Richard

"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