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
_______________________________