pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help with embedded CASEs
5+ messages / 5 participants
[nested] [flat]
* Need help with embedded CASEs
@ 2001-11-07 15:58 Denis Bucher <dbucher@niftycom.com>
2001-11-07 16:53 ` Re: Need help with embedded CASEs John D. Rozeboom <jrozeboom@healthaxis.com.nospam>
2001-11-07 17:14 ` Re: Need help with embedded CASEs Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-07 18:06 ` Re: Need help with embedded CASEs Tom Lane <tgl@sss.pgh.pa.us>
2001-11-08 00:25 ` Re: Need help with embedded CASEs Peter Eisentraut <peter_e@gmx.net>
0 siblings, 4 replies; 5+ messages in thread
From: Denis Bucher @ 2001-11-07 15:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with embedded CASEs
2001-11-07 15:58 Need help with embedded CASEs Denis Bucher <dbucher@niftycom.com>
@ 2001-11-07 16:53 ` John D. Rozeboom <jrozeboom@healthaxis.com.nospam>
3 siblings, 0 replies; 5+ messages in thread
From: John D. Rozeboom @ 2001-11-07 16:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with embedded CASEs
2001-11-07 15:58 Need help with embedded CASEs Denis Bucher <dbucher@niftycom.com>
@ 2001-11-07 17:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-11-07 17:14 UTC (permalink / raw)
To: Denis Bucher <dbucher@niftycom.com>; +Cc: pgsql-sql
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_date
then 't'::bool else 'f'::bool end as flag_today) intable;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with embedded CASEs
2001-11-07 15:58 Need help with embedded CASEs Denis Bucher <dbucher@niftycom.com>
@ 2001-11-07 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-11-07 18:06 UTC (permalink / raw)
To: Denis Bucher <dbucher@niftycom.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with embedded CASEs
2001-11-07 15:58 Need help with embedded CASEs Denis Bucher <dbucher@niftycom.com>
@ 2001-11-08 00:25 ` Peter Eisentraut <peter_e@gmx.net>
3 siblings, 0 replies; 5+ messages in thread
From: Peter Eisentraut @ 2001-11-08 00:25 UTC (permalink / raw)
To: Denis Bucher <dbucher@niftycom.com>; +Cc: pgsql-sql
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 ELSE TIME '00:00' END
AS time_iftoday;
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-11-08 00:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-07 15:58 Need help with embedded CASEs Denis Bucher <dbucher@niftycom.com>
2001-11-07 16:53 ` John D. Rozeboom <jrozeboom@healthaxis.com.nospam>
2001-11-07 17:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-07 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-08 00:25 ` Peter Eisentraut <peter_e@gmx.net>
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