From: Tom Haddon <tom@betterhealthfoundation.org>
To: pgsql-sql@postgresql.org
Subject: Case Statement
Date: Wed, 28 Aug 2002 10:44:57 -0700
Message-ID: <NEBBIHDGCLBEJMPFAMLAOEHJCIAA.tom@betterhealthfoundation.org> (raw)
In-Reply-To: <200208281701.g7SH1aI20777@candle.pha.pa.us>
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
redundancy in the statement above such as "CASE WHEN agency_contact_info.id
> 0 THEN 0 ELSE 0 END" and "agency_contact_info.id > 0" - this is because it
is being built dynamically, and it makes it easier to build the addition
blocks of the statement.
Thanks in advance, Tom
_______________________________
Tom Haddon
IT Director
The Better Health Foundation
414 Thirteenth Street, Suite 450
Oakland, CA 94612
(510) 444-5096
www.betterhealthfoundation.org
_______________________________
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: tom@betterhealthfoundation.org
Subject: Re: Case Statement
In-Reply-To: <NEBBIHDGCLBEJMPFAMLAOEHJCIAA.tom@betterhealthfoundation.org>
* 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