Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need help with embedded CASEs

Need help with embedded CASEs

From
Denis Bucher
Date:
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

Re: Need help with embedded CASEs

From
Stephan Szabo
Date:
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_datethen't'::bool else 'f'::bool end as flag_today) intable;

Re: Need help with embedded CASEs

From
Tom Lane
Date:
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

Re: Need help with embedded CASEs

From
Peter Eisentraut
Date:
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
ELSETIME '00:00' END       AS time_iftoday;
 

-- 
Peter Eisentraut   peter_e@gmx.net

Solution found ! Re: Need help with embedded CASEs

From
Denis Bucher
Date:
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

Re: Need help with embedded CASEs

From
"John D. Rozeboom"
Date:
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