pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUse derived expression in select statement
2+ messages / 2 participants
[nested] [flat]
* Use derived expression in select statement
@ 2003-09-14 04:25 Richard Sydney-Smith <richard@ibisaustralia.com>
0 siblings, 1 reply; 2+ messages in thread
From: Richard Sydney-Smith @ 2003-09-14 04:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Use derived expression in select statement
@ 2003-09-14 04:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Sydney-Smith <richard@ibisaustralia.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-09-14 04:38 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-09-14 04:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-14 04:25 Use derived expression in select statement Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-14 04:38 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox