Re: Need help with embedded CASEs

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