pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCase Statement
7+ messages / 3 participants
[nested] [flat]
* Case Statement
@ 2002-07-31 22:59 Tom Haddon <tom@betterhealthfoundation.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Haddon @ 2002-07-31 22:59 UTC (permalink / raw)
To: pgsql-sql
Hi Folks,
Pretty basic one here. I'm trying to do a SELECT statement that includes a
CASE structure that will be used in the ORDER BY clause, and I want the CASE
structure to evaluate a bunch of criteria and in each case increment its
value by a certain amount:
SELECT id, description CASE
field1 IS TRUE THEN CASE=CASE+1
field2 IS TRUE THEN CASE=CASE+2
END
FROM table
ORDER BY CASE, description;
Am I misunderstanding what CASE can do here, and if so, how else do I do
what I'm trying to do? I know that that isn't right - any pointers
appreciated.
Thanks, Tom
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Case Statement
@ 2002-07-31 23:17 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Haddon <tom@betterhealthfoundation.org>
0 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-07-31 23:17 UTC (permalink / raw)
To: Tom Haddon <tom@betterhealthfoundation.org>; +Cc: pgsql-sql
On Wed, 31 Jul 2002, Tom Haddon wrote:
> Hi Folks,
>
> Pretty basic one here. I'm trying to do a SELECT statement that includes a
> CASE structure that will be used in the ORDER BY clause, and I want the CASE
> structure to evaluate a bunch of criteria and in each case increment its
> value by a certain amount:
>
> SELECT id, description CASE
> field1 IS TRUE THEN CASE=CASE+1
> field2 IS TRUE THEN CASE=CASE+2
> END
> FROM table
> ORDER BY CASE, description;
>
> Am I misunderstanding what CASE can do here, and if so, how else do I do
> what I'm trying to do? I know that that isn't right - any pointers
> appreciated.
Are you trying to get a 0-3 based on field1 and field2 or some kind of
aggregate? The first you can do that with something like
case when field1 is true then 1 else 0 end +
case when field2 is true then 2 else 0 end
I think. (Extend for more columns)
Or if you've got like 7-8 columns maybe just make a function that does
the work for you for clarity.
You'll also need a comma after description and you'll want to alias the
case expression and use that in order by or use the position number.
^ permalink raw reply [nested|flat] 7+ messages in thread
* FW: Case Statement
@ 2002-07-31 23:32 Tom Haddon <tom@betterhealthfoundation.org>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Haddon @ 2002-07-31 23:32 UTC (permalink / raw)
To: pgsql-sql
Hi Stephan,
I have a lot of fields, so I'm not sure if a function or case is the way to
go. Basically, I have, say 50 boolean fields that are being evaluated, and I
want to have a column which is the sum of the number of "TRUE" values of
those 50 columns, and then ORDER BY that column. So, for example of the 50
fields to be evaluated, the SELECT statement should return records ordered
by those that match the most number of criteria. Does that make sense? Your
example below may actually work for me, on the other hand:
"case when field1 is true then 1 else 0 end +
case when field2 is true then 2 else 0 end +
case when field3 is true then 4 else 0 end
Let's suppose field1 is true and field2, field3 are not, would it return 1,
and if all three are true it would return 7 for the case? If so, I think
that's the way to go, as all this is being dynamically built in any case.
Thanks, Tom
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: Wednesday, July 31, 2002 4:17 PM
To: Tom Haddon
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Case Statement
On Wed, 31 Jul 2002, Tom Haddon wrote:
> Hi Folks,
>
> Pretty basic one here. I'm trying to do a SELECT statement that includes a
> CASE structure that will be used in the ORDER BY clause, and I want the
CASE
> structure to evaluate a bunch of criteria and in each case increment its
> value by a certain amount:
>
> SELECT id, description CASE
> field1 IS TRUE THEN CASE=CASE+1
> field2 IS TRUE THEN CASE=CASE+2
> END
> FROM table
> ORDER BY CASE, description;
>
> Am I misunderstanding what CASE can do here, and if so, how else do I do
> what I'm trying to do? I know that that isn't right - any pointers
> appreciated.
Are you trying to get a 0-3 based on field1 and field2 or some kind of
aggregate? The first you can do that with something like
case when field1 is true then 1 else 0 end +
case when field2 is true then 2 else 0 end
I think. (Extend for more columns)
Or if you've got like 7-8 columns maybe just make a function that does
the work for you for clarity.
You'll also need a comma after description and you'll want to alias the
case expression and use that in order by or use the position number.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Case Statement
@ 2002-08-01 00:10 Otto Hirr <otto.hirr@olabinc.com>
parent: Tom Haddon <tom@betterhealthfoundation.org>
1 sibling, 0 replies; 7+ messages in thread
From: Otto Hirr @ 2002-08-01 00:10 UTC (permalink / raw)
To: pgsql-sql
Just 'cuz you can do something with a language feature does not
necessarily mean that it is a good way to do it. If this is a
common query and you have a large dataset, then this is going to
be computationally intensive.
Why not create a field as part of the table and create a trigger
to update the field with the sum of your true bool fields when the
record is created or updated. This spreads out this computationally
intensive query and then it becomes a simple query...
Just some random thoughts...
.. Otto
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tom Haddon
> Sent: Wednesday, July 31, 2002 4:33 PM
> To: pgsql-sql@postgresql.org
> Subject: FW: Case Statement
>
>
> Hi Stephan,
>
> I have a lot of fields, so I'm not sure if a function or case
> is the way to
> go. Basically, I have, say 50 boolean fields that are being
> evaluated, and I
> want to have a column which is the sum of the number of
> "TRUE" values of
> those 50 columns, and then ORDER BY that column. So, for
> example of the 50
> fields to be evaluated, the SELECT statement should return
> records ordered
> by those that match the most number of criteria. Does that
> make sense? Your
> example below may actually work for me, on the other hand:
>
> "case when field1 is true then 1 else 0 end +
> case when field2 is true then 2 else 0 end +
> case when field3 is true then 4 else 0 end
>
> Let's suppose field1 is true and field2, field3 are not,
> would it return 1,
> and if all three are true it would return 7 for the case? If
> so, I think
> that's the way to go, as all this is being dynamically built
> in any case.
>
> Thanks, Tom
>
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: Wednesday, July 31, 2002 4:17 PM
> To: Tom Haddon
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Case Statement
>
>
> On Wed, 31 Jul 2002, Tom Haddon wrote:
>
> > Hi Folks,
> >
> > Pretty basic one here. I'm trying to do a SELECT statement
> that includes a
> > CASE structure that will be used in the ORDER BY clause,
> and I want the
> CASE
> > structure to evaluate a bunch of criteria and in each case
> increment its
> > value by a certain amount:
> >
> > SELECT id, description CASE
> > field1 IS TRUE THEN CASE=CASE+1
> > field2 IS TRUE THEN CASE=CASE+2
> > END
> > FROM table
> > ORDER BY CASE, description;
> >
> > Am I misunderstanding what CASE can do here, and if so, how
> else do I do
> > what I'm trying to do? I know that that isn't right - any pointers
> > appreciated.
>
> Are you trying to get a 0-3 based on field1 and field2 or some kind of
> aggregate? The first you can do that with something like
> case when field1 is true then 1 else 0 end +
> case when field2 is true then 2 else 0 end
> I think. (Extend for more columns)
>
> Or if you've got like 7-8 columns maybe just make a function that does
> the work for you for clarity.
>
> You'll also need a comma after description and you'll want to
> alias the
> case expression and use that in order by or use the position number.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FW: Case Statement
@ 2002-08-01 00:23 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Haddon <tom@betterhealthfoundation.org>
1 sibling, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-08-01 00:23 UTC (permalink / raw)
To: Tom Haddon <tom@betterhealthfoundation.org>; +Cc: pgsql-sql
On Wed, 31 Jul 2002, Tom Haddon wrote:
> Hi Stephan,
>
> I have a lot of fields, so I'm not sure if a function or case is the way to
> go. Basically, I have, say 50 boolean fields that are being evaluated, and I
> want to have a column which is the sum of the number of "TRUE" values of
> those 50 columns, and then ORDER BY that column. So, for example of the 50
> fields to be evaluated, the SELECT statement should return records ordered
> by those that match the most number of criteria. Does that make sense? Your
> example below may actually work for me, on the other hand:
>
> "case when field1 is true then 1 else 0 end +
> case when field2 is true then 2 else 0 end +
> case when field3 is true then 4 else 0 end
>
> Let's suppose field1 is true and field2, field3 are not, would it return 1,
> and if all three are true it would return 7 for the case? If so, I think
> that's the way to go, as all this is being dynamically built in any case.
Yes, that's what it should do (I did the 2 variable case and all 4
combinations to try getting all 0-3). If you're doing many more selects
than update/inserts to the table where you want to do this (and the fields
you care about is a constant set) then a trigger on insert/update is the
way to go and just store the value.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Case Statement
@ 2002-08-28 17:44 Tom Haddon <tom@betterhealthfoundation.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Haddon @ 2002-08-28 17:44 UTC (permalink / raw)
To: pgsql-sql
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
_______________________________
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Case Statement
@ 2002-08-28 18:25 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Haddon <tom@betterhealthfoundation.org>
0 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-08-28 18:25 UTC (permalink / raw)
To: Tom Haddon <tom@betterhealthfoundation.org>; +Cc: 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-08-28 18:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-31 22:59 Case Statement Tom Haddon <tom@betterhealthfoundation.org>
2002-07-31 23:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-31 23:32 FW: Case Statement Tom Haddon <tom@betterhealthfoundation.org>
2002-08-01 00:10 ` Otto Hirr <otto.hirr@olabinc.com>
2002-08-01 00:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-28 17:44 Case Statement Tom Haddon <tom@betterhealthfoundation.org>
2002-08-28 18:25 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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