Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA7I6lm15861 for ; Wed, 7 Nov 2001 13:06:48 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fA7I6ac05453; Wed, 7 Nov 2001 13:06:36 -0500 (EST) To: Denis Bucher cc: PGSQL-SQL Subject: Re: Need help with embedded CASEs In-reply-to: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com> References: <5.1.0.14.0.20011107164717.03153940@mail.niftycom.com> Comments: In-reply-to Denis Bucher message dated "Wed, 07 Nov 2001 16:58:31 +0100" Date: Wed, 07 Nov 2001 13:06:36 -0500 Message-ID: <5450.1005156396@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/111 X-Sequence-Number: 5495 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 ? 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