Обсуждение: Need help with embedded CASEs
Hello ! I came across a very hard SELECT and Postgres refuses it. If someone could help me it would be great ! Here is a simplified version of the problem that I have : SELECT CASE WHEN '2001-11-07' = current_date THEN 't' ELSE 'f' END AS flag_today, CASE WHEN flag_today THEN current_time ELSE '00:00' END AS time_iftoday; Why doesn't it work ? Or how could I use the result of the CASE in another ? Thanks a lot for any help ! Denis Bucher NiftyCom P.S. The real request, for fun, is : SELECT sc.datetime_deliver, SUM( CASE WHEN sc.type_cmd=1 AND b.b='TRUE' THEN sl.number_main WHEN sc.type_cmd=4 AND b.b='TRUE' AND true_nb_shop=sc.nb_shop_main THEN sl.number_main ELSE '0' END ) AS sum_nb_entre, CASE WHEN sc.type_cmd=1 AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=3 AND b.b='TRUE' THEN sc.nb_shop_other WHEN sc.type_cmd=4 AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=4 AND b.b='FALSE' THEN sc.nb_shop_other ELSE '0' END AS true_nb_shop, SUM( CASE WHEN sc.type_cmd=3 AND b.b='TRUE' THEN sl.number_other+sl.number_trash+sl.number_eaten WHEN sc.type_cmd=4 AND b.b='FALSE' THEN sl.number_other ELSE '0' END ) AS sum_nb_sorti FROM stock_cmd sc, stock_lig sl, (SELECT boolean 'TRUE' AS b UNION SELECT 'FALSE') as b WHERE sc.datetime_deliver BETWEEN '2001-01-01 11:00:00' AND '2002-01-01 12:00:00' AND sc.type_cmd<>2 AND sl.id_cmd=sc.id AND (sc.type_cmd=4 OR b.b='TRUE') GROUP BY sc.datetime_deliver, true_nb_shop
On Wed, 7 Nov 2001, Denis Bucher wrote: > > Hello ! > > I came across a very hard SELECT and Postgres refuses it. If someone could > help me it would be great ! > > Here is a simplified version of the problem that I have : > > SELECT CASE WHEN '2001-11-07' = current_date THEN 't' ELSE 'f' END AS > flag_today, CASE WHEN flag_today THEN current_time ELSE '00:00' END AS > time_iftoday; > > Why doesn't it work ? Or how could I use the result of the CASE in another ? My guess is because flag_today isn't a column really, it's an output expression in the select list. The simplified case could probably be written as: select flag_today, case when flag_today then current_time else '00:00'END AS time_iftoday from (select case when '2001-11-07'=current_datethen't'::bool else 'f'::bool end as flag_today) intable;
Denis Bucher <dbucher@niftycom.com> writes:
> SELECT CASE WHEN '2001-11-07' = current_date THEN 't' ELSE 'f' END AS
> flag_today, CASE WHEN flag_today THEN current_time ELSE '00:00' END AS
> time_iftoday;
> Why doesn't it work ?
flag_today is an output variable of this SELECT, not an input.
> Or how could I use the result of the CASE in another ?
AFAIK the only way to avoid writing the expression twice is to use
a sub-select:
SELECT ss.flag_today, CASE WHEN ss.flag_today THEN current_time ELSE '00:00' END AS time_iftoday
FROM
(SELECT CASE WHEN '2001-11-07' = current_date THEN true ELSE false END AS
flag_today) AS ss;
regards, tom lane
Denis Bucher writes: > SELECT CASE WHEN '2001-11-07' = current_date THEN 't' ELSE 'f' END AS > flag_today, CASE WHEN flag_today THEN current_time ELSE '00:00' END AS > time_iftoday; > > Why doesn't it work ? For one thing, flag_today is not a made boolean expression, so the second CASE wouldn't even work on that account. Secondly, the "AS" aliases in the select list aren't available as variables in the other items in the select list. A corrected version would be: SELECT CASE WHEN DATE '2001-11-07' = current_date THEN true ELSE false END AS flag_today, CASE WHEN DATE '2001-11-07'= current_date THEN current_time ELSE TIME '00:00' END AS time_iftoday; Or shorter: SELECT DATE '2001-11-07' = current_date AS flag_today, CASE WHEN DATE '2001-11-07' = current_date THEN current_time ELSETIME '00:00' END AS time_iftoday; -- Peter Eisentraut peter_e@gmx.net
At 16:58 07.11.01 +0100, you wrote: Hello ! I think it is a bug or at least an unimplemented feature of Postgres 7.1... >Here is a simplified version of the problem that I have : >SELECT CASE WHEN '2001-11-07' = current_date THEN 't' ELSE 'f' END AS >flag_today, CASE WHEN flag_today THEN current_time ELSE '00:00' END AS >time_iftoday; Conclusion it doesn't work to use a newly created column in a CASE And the solution are : a) replace the field by the complete expression (can become very complex) b) do sub-request : The erroneous sql query was : SELECT sc.datetime_deliver, SUM( CASE WHEN sc.type_cmd=1 AND b.b='TRUE' THEN sl.number_main WHEN sc.type_cmd=4 AND b.b='TRUE' AND true_nb_shop=sc.nb_shop_main THEN sl.number_main ELSE '0' END ) AS sum_nb_entre, CASE WHEN sc.type_cmd=1 AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=3 AND b.b='TRUE' THEN sc.nb_shop_other WHEN sc.type_cmd=4 AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=4 AND b.b='FALSE' THEN sc.nb_shop_other ELSE '0' END AS true_nb_shop, SUM( CASE WHEN sc.type_cmd=3 AND b.b='TRUE' THEN sl.number_other+sl.number_trash+sl.number_eaten WHEN sc.type_cmd=4 AND b.b='FALSE' THEN sl.number_other ELSE '0' END ) AS sum_nb_sorti FROM stock_cmd sc, stock_lig sl, (SELECT boolean 'TRUE' AS b UNION SELECT 'FALSE') as b WHERE sc.datetime_deliver BETWEEN '2001-01-01 11:00:00' AND '2002-01-01 12:00:00' AND sc.type_cmd<>2 AND sl.id_cmd=sc.id AND (sc.type_cmd=4 OR b.b='TRUE') GROUP BY sc.datetime_deliver, true_nb_shop I change it into : SELECT vi.datetime_deliver, vi.true_nb_shop, SUM( CASE WHEN vi.type_cmd=1 AND vi.b='TRUE' THEN vi.number_main WHEN vi.type_cmd=4 AND vi.b='TRUE' AND vi.true_nb_shop=vi.nb_shop_main THEN vi.number_main ELSE '0' END ) AS sum_nb_entre, SUM( CASE WHEN vi.type_cmd=3 AND vi.b='TRUE' THEN vi.number_other+vi.number_trash+vi.number_eaten WHEN vi.type_cmd=4 AND vi.b='FALSE' THEN vi.number_other ELSE '0' END ) AS sum_nb_sorti FROM (SELECT sc.type_cmd, b.b, sl.number_main, sl.number_other, sl.number_trash, sl.number_eaten, sc.nb_shop_main, sc.datetime_deliver, CASE WHEN sc.type_cmd=1 AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=3 AND b.b='TRUE' THEN sc.nb_shop_other WHEN sc.type_cmd=4 AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=4 AND b.b='FALSE' THEN sc.nb_shop_other ELSE '0' END AS true_nb_shop FROM stock_cmd sc, stock_lig sl, (SELECT boolean 'TRUE' AS b UNION SELECT 'FALSE') as b WHERE sc.datetime_deliver BETWEEN '2001-01-01 11:00:00' AND '2002-01-01 12:00:00' AND sc.type_cmd<>2 AND sl.id_cmd=sc.id AND (sc.type_cmd=4 OR b.b='TRUE')) AS vi GROUP BY vi.datetime_deliver, vi.true_nb_shop And it's great ! It seems to work ;-)) Thanks to all that helped me and gave me the solution ! Denis Bucher NiftyCom
The reason your query doesn't work is that the column alias "flag_today" is simply a way to give the computed column a name. That alias cannot be used anywhere else in the query. "Denis Bucher" <dbucher@niftycom.com> wrote in message news:5.1.0.14.0.20011107164717.03153940@mail.niftycom.com... > > Hello ! > > I came across a very hard SELECT and Postgres refuses it. If someone could > help me it would be great ! > > Here is a simplified version of the problem that I have : > > SELECT CASE WHEN '2001-11-07' = current_date THEN 't' ELSE 'f' END AS > flag_today, CASE WHEN flag_today THEN current_time ELSE '00:00' END AS > time_iftoday; > > Why doesn't it work ? Or how could I use the result of the CASE in another ? > > Thanks a lot for any help ! > > Denis Bucher > NiftyCom > > > P.S. The real request, for fun, is : > SELECT sc.datetime_deliver, SUM( CASE WHEN sc.type_cmd=1 AND b.b='TRUE' > THEN sl.number_main WHEN sc.type_cmd=4 AND b.b='TRUE' AND > true_nb_shop=sc.nb_shop_main THEN sl.number_main ELSE '0' END ) AS > sum_nb_entre, CASE WHEN sc.type_cmd=1 AND b.b='TRUE' THEN sc.nb_shop_main > WHEN sc.type_cmd=3 AND b.b='TRUE' THEN sc.nb_shop_other WHEN sc.type_cmd=4 > AND b.b='TRUE' THEN sc.nb_shop_main WHEN sc.type_cmd=4 AND b.b='FALSE' THEN > sc.nb_shop_other ELSE '0' END AS true_nb_shop, SUM( CASE WHEN sc.type_cmd=3 > AND b.b='TRUE' THEN sl.number_other+sl.number_trash+sl.number_eaten WHEN > sc.type_cmd=4 AND b.b='FALSE' THEN sl.number_other ELSE '0' END ) AS > sum_nb_sorti FROM stock_cmd sc, stock_lig sl, (SELECT boolean 'TRUE' AS b > UNION SELECT 'FALSE') as b WHERE sc.datetime_deliver BETWEEN '2001-01-01 > 11:00:00' AND '2002-01-01 12:00:00' AND sc.type_cmd<>2 AND sl.id_cmd=sc.id > AND (sc.type_cmd=4 OR b.b='TRUE') GROUP BY sc.datetime_deliver, true_nb_shop > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org