pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Denis Bucher <dbucher@niftycom.com>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Need help with embedded CASEs
Date: Wed, 07 Nov 2001 16:58:31 +0100
Message-ID: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com> (raw)


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 




view thread (5+ messages)  latest in thread

Message-ID: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com>
Permalink:  ../5.1.0.14.0.20011107164717.03153940@mail.niftycom.com/
Also on:    postgresql.org/message-id/5.1.0.14.0.20011107164717.03153940@mail.niftycom.com

 · 

reply

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: Need help with embedded CASEs
  In-Reply-To: <5.1.0.14.0.20011107164717.03153940@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