Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA7H7Hm13560 for ; Wed, 7 Nov 2001 12:07:17 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA7Grrb31784 for pgsql-sql@postgresql.org; Wed, 7 Nov 2001 11:53:53 -0500 (EST) (envelope-from news) From: "John D. Rozeboom" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Need help with embedded CASEs Date: Wed, 7 Nov 2001 10:53:55 -0600 Organization: HealthAxis, Inc. Lines: 52 Message-ID: <9sbov1$v15$1@news.tht.net> References: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 To: pgsql-sql@postgresql.org X-Archive-Number: 200111/124 X-Sequence-Number: 5508 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" 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