pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Need 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>
  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 16:53  John D. Rozeboom <jrozeboom@healthaxis.com.nospam>
  parent: Denis Bucher <dbucher@niftycom.com>
  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 17:14  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Denis Bucher <dbucher@niftycom.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 18:06  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Denis Bucher <dbucher@niftycom.com>
  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-08 00:25  Peter Eisentraut <peter_e@gmx.net>
  parent: Denis Bucher <dbucher@niftycom.com>
  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