Received: from narn.deckpoint.ch (narn.deckpoint.ch [194.38.160.133]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA8B7fm90414 for ; Thu, 8 Nov 2001 06:07:41 -0500 (EST) (envelope-from dbucher@niftycom.com) Received: from orange.niftycom.com (hidden-user@[213.162.13.76]) by narn.deckpoint.ch (8.11.6/8.11.6) with ESMTP id fA8B7hW18385 for ; Thu, 8 Nov 2001 12:07:43 +0100 Received: from violet.niftycom.com ([192.168.200.4]) by orange.niftycom.com (8.10.2/8.10.2/SuSE Linux 8.10.0-0.3) with ESMTP id fA8B7hI08852 for ; Thu, 8 Nov 2001 12:07:43 +0100 X-Authentication-Warning: orange.niftycom.com: Host [192.168.200.4] claimed to be violet.niftycom.com Message-Id: <5.1.0.14.0.20011108120107.03333ec0@mail.niftycom.com> X-Sender: dbucher@mail.niftycom.com X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Thu, 08 Nov 2001 12:06:14 +0100 To: PGSQL-SQL From: Denis Bucher Subject: Solution found ! Re: Need help with embedded CASEs In-Reply-To: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200111/121 X-Sequence-Number: 5505 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