Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA7HEEm13789 for ; Wed, 7 Nov 2001 12:14:14 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 8C860D5DB; Wed, 7 Nov 2001 09:14:19 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 804245BDE; Wed, 7 Nov 2001 09:14:19 -0800 (PST) Date: Wed, 7 Nov 2001 09:14:19 -0800 (PST) From: Stephan Szabo 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> Message-ID: <20011107091040.Q49204-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/109 X-Sequence-Number: 5493 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;