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
4else 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 likecase when field1 is true then 1 else 0 end +case when field2
istrue 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