[SQL] Case Statement

[Tom Haddon]

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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

FW: [SQL] Case Statement

[Tom Haddon]

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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Stephan Szabo
Sent: Wednesday, July 31, 2002 4:17 PM
To: Tom Haddon
Cc: [EMAIL PROTECTED]
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 [EMAIL PROTECTED]

[SQL] Add Primary Key to a Table

[Tom Haddon]

Hi Folks,

I'm trying to use the ALTER TABLE command to add a primary key to a table.

The command I am using is:

ALTER TABLE "agency_contact_info" ADD CONSTRAINT "agency_contact_info_pkey"
PRIMARY KEY ("id");

I get a message saying "ALTER TABLE / ADD CONSTRAINT not implemented for
that constraint type". Is this a bug, or am I doing something wrong?


The reason I am trying to do this is that I want to split a table with a
large number of fields up into a few tables with a sub-set of fields from
the original table, linked by FOREIGN KEY on "id". I had thought the best
way to do this was to use SELECT INTO and then try to alter the tables to
include PRIMARY and FOREIGN keys where applicable. Is there a better way to
do this?

Thanks, Tom

___
Tom Haddon
IT Director
The Better Health Foundation
414 Thirteenth Street, Suite 450
Oakland, CA 94612
(510) 444-5096
www.betterhealthfoundation.org
___


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Add Primary Key to a Table

[Tom Haddon]

Hi Josh,

I think that solves it for me - I'm using 7.1.3 - will upgrade
appropriately.

Thanks, Tom

-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 02, 2002 4:20 PM
To: Tom Haddon; [EMAIL PROTECTED]
Subject: Re: [SQL] Add Primary Key to a Table



Tom,

> ALTER TABLE "agency_contact_info" ADD CONSTRAINT
"agency_contact_info_pkey"
> PRIMARY KEY ("id");
>
> I get a message saying "ALTER TABLE / ADD CONSTRAINT not implemented for
> that constraint type". Is this a bug, or am I doing something wrong?

What version are you using?  That command is supported with Postgres 7.2,
but
not with earlier versions.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Case Statement

[Tom Haddon]

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
___


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Sum of Every Column

[Tom Haddon]

Hi Folks,

I'm hoping to put together a query that generates a report on a table with
a large number of boolean fields. This report has to be able to adapt to
the number of fields in the table. Essentially, I want it to provide the
sum of TRUE values for each field for a given subset of the table. I've
got the query that returns the subset of the table (this is based on a
relationship with another table):

SELECT breast_cancer_resources.*
FROM breast_cancer_resources, agency_contact_info
WHERE breast_cancer_resources.id=agency_contact_info.id
AND agency_contact_info.guideregion=1
AND agency_contact_info.list_online=TRUE

But I'm not sure how to generate the sum for each column. Should I be
looking elsewhere than SQL to do this for me, such as php (this is for a
web-based report)?

Thanks, Tom




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Sum of Every Column

[Tom Haddon]

Hi Folks,

I'm hoping to put together a query that generates a report on a table with
a large number of boolean fields. This report has to be able to adapt to
the number of fields in the table. Essentially, I want it to provide the
sum of TRUE values for each field for a given subset of the table. I've
got the query that returns the subset of the table (this is based on a
relationship with another table):

SELECT breast_cancer_resources.*
FROM breast_cancer_resources, agency_contact_info
WHERE breast_cancer_resources.id=agency_contact_info.id
AND agency_contact_info.guideregion=1
AND agency_contact_info.list_online=TRUE

But I'm not sure how to generate the sum for each column. Should I be
looking elsewhere than SQL to do this for me, such as php (this is for a
web-based report)?

Thanks, Tom




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]