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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <200208281701.g7SH1aI20777@candle.pha.pa.us>
Importance: Normal

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
_______________________________