From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Sydney-Smith <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use derived expression in select statement
Date: Sun, 14 Sep 2003 00:38:42 -0400
Message-ID: <7110.1063514322@sss.pgh.pa.us> (raw)
In-Reply-To: <000f01c37a78$4ea53560$076c32d2@athlon2000>
References: <000f01c37a78$4ea53560$076c32d2@athlon2000>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, richard@ibisaustralia.com
Subject: Re: Use derived expression in select statement
In-Reply-To: <7110.1063514322@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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