From: Denis Bucher <dbucher@niftycom.com>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Solution found ! Re: Need help with embedded CASEs
Date: Thu, 08 Nov 2001 12:06:14 +0100
Message-ID: <5.1.0.14.0.20011108120107.03333ec0@mail.niftycom.com> (raw)
In-Reply-To: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dbucher@niftycom.com
Subject: Re: Solution found ! Re: Need help with embedded CASEs
In-Reply-To: <5.1.0.14.0.20011108120107.03333ec0@mail.niftycom.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox