From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Haddon <tom@betterhealthfoundation.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Case Statement
Date: Wed, 28 Aug 2002 11:25:28 -0700 (PDT)
Message-ID: <20020828112325.S87525-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <NEBBIHDGCLBEJMPFAMLAOEHJCIAA.tom@betterhealthfoundation.org>
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.
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: sszabo@megazone23.bigpanda.com, tom@betterhealthfoundation.org
Subject: Re: Case Statement
In-Reply-To: <20020828112325.S87525-100000@megazone23.bigpanda.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