Date: Wed, 28 Aug 2002 11:25:28 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Haddon <tom@betterhealthfoundation.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Case Statement
In-Reply-To: <NEBBIHDGCLBEJMPFAMLAOEHJCIAA.tom@betterhealthfoundation.org>
Message-ID: <20020828112325.S87525-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 28 Aug 2002, Tom Haddon wrote:

> Hi Folks,
>
> I am having troubles with a case statement in that I want to have the query
> select only those records that match a particular case. Here's my query:
>
> SELECT
> agency_contact_info.id,organization,department,city,state,description_of_ser
> vices, CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END + CASE WHEN
> agency_contact_info.languages_other_text ~ 'Mien' THEN 1 ELSE 0 END AS
> relevance
> FROM agency_contact_info WHERE (agency_contact_info.guideregion=1 AND
> list_online IS TRUE AND (agency_contact_info.id > 0 OR
> agency_contact_info.languages_other_text ~ 'Mien' ))
> ORDER BY relevance DESC, agency_contact_info.organization
>
> How do I add in the fact that I only want records where the CASE (as
> relevance) > 0? I've tried using it in the WHERE statement adding a HAVING
> statement and it doesn't like either. You will see a fair amount of

I think you'll either need to duplicate the case statement or hide it in
a subselect with the relevance check on the outer query (like
select * from (select ... ) as a where relevance>0.