Re: Use derived expression in select statement

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Use derived expression in select statement
Дата
Msg-id 7110.1063514322@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Use derived expression in select statement  ("Richard Sydney-Smith" <richard@ibisaustralia.com>)
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: "Richard Sydney-Smith"
Дата:
Сообщение: Use derived expression in select statement
Следующее
От: Dennis Bjorklund
Дата:
Сообщение: Re: [GENERAL] MD5() function not available ??