Re: Case Statement

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Tom Haddon <tom(at)betterhealthfoundation(dot)org>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Case Statement
Date: 2002-08-28 18:25:28
Message-ID: 20020828112325.S87525-100000@megazone23.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Jeff Eckermann 2002-08-28 18:33:43 Re: Retrieving the new nextval...
Previous Message Tom Haddon 2002-08-28 17:44:57 Case Statement