Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed on Conditional Select Statement (CASE?)
Help Needed on Conditional Select Statement (CASE?) [message #187603] Mon, 14 August 2006 11:51 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Howdy folks.

I'm working with an Oracle 8i database. I know just about enough SQL to get myself into trouble, and now I'm stuck. I'm looking for a way to select certain data, and I think it may be possible using CASE, but I can't quite figure it out.

The table I'm selecting from is simple. It has just a person ID and a status ID. One person can have more than one status.

I'm trying to select the values from the table, but with the following "rules":

- if there is just one record for the person_id, return the status_id
- if there are multiple records for one person_id, return the value "multiple" (and return the person_id just once)

So, for example, let's say the table looks like this:

Person_ID  Status_ID
12345      1
55555      1
55555      2
55555      3


I would like the query to return the following:

Person_ID  Status_ID
12345      1
55555      multiple


Any ideas? I'd like to be able to just run a simple SQL statement through TOAD or some such, rather than having a PL/SQL procedure or something like that.

Thanks,

Steve
Re: Help Needed on Conditional Select Statement (CASE?) [message #187618 is a reply to message #187603] Mon, 14 August 2006 13:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select person_id, decode(count(*), 1, to_char(count(*)), 'multiple') status_id
	from test_person
	group by person_id;
Re: Help Needed on Conditional Select Statement (CASE?) [message #187623 is a reply to message #187618] Mon, 14 August 2006 13:48 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
ebrian wrote on Mon, 14 August 2006 13:50

select person_id, decode(count(*), 1, to_char(count(*)), 'multiple') status_id
	from test_person
	group by person_id;



Thanks very much!

I don't think that's quite it, but it's my fault for giving a bad example. Sad I don't want it to return a hard-coded value of "1" for the rows with only 1 value. I want it to return whatever value is in "status_id".

Does that make sense?

Perhaps this is a better example:

Person_ID  Status_ID
12345      5
33333      19
55555      1
55555      2
55555      3


Should return:
Person_ID  Status_ID
12345      5
33333      19
55555      multiple



Thanks again!

Re: Help Needed on Conditional Select Statement (CASE?) [message #187631 is a reply to message #187623] Mon, 14 August 2006 14:10 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select distinct person_id, decode(ct, 1, to_char(status_id),'multiple') status_id
	from (select a.*, count(*) over (partition by person_id) ct from test_person a);
Re: Help Needed on Conditional Select Statement (CASE?) [message #187711 is a reply to message #187631] Tue, 15 August 2006 01:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or a non-analytic approach:

select person_id, decode(count(*), 1, max(status_id), 'multiple') status_id
from test_person
group by person_id;
Re: Help Needed on Conditional Select Statement (CASE?) [message #187735 is a reply to message #187711] Tue, 15 August 2006 05:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
JRowbottom wrote on Tue, 15 August 2006 01:57

Or a non-analytic approach:

select person_id, decode(count(*), 1, max(status_id), 'multiple') status_id
from test_person
group by person_id;



Yes indeed JRowbottm. You'll just need to add a to_char around max:

select person_id, decode(count(*), 1, to_char(max(status_id)), 'multiple') status_id
from test_person
group by person_id;
Re: Help Needed on Conditional Select Statement (CASE?) [message #187752 is a reply to message #187603] Tue, 15 August 2006 06:57 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Beautiful folks, thanks to your help I was able to get it working!

Many, many thanks! Very Happy

Steve
Re: Help Needed on Conditional Select Statement (CASE?) [message #187756 is a reply to message #187735] Tue, 15 August 2006 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Picky picky picky. Cool

My test table had a VC2 status_id column, so I didn't need a to_char.
Re: Help Needed on Conditional Select Statement (CASE?) [message #187758 is a reply to message #187756] Tue, 15 August 2006 07:12 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Ahhhhh subtleties...yeah it's definitely going to depend on the OP's configuration.
Previous Topic: Export data to an excel file
Next Topic: PL/SQL Cursor Fetch error
Goto Forum:
  


Current Time: Wed Dec 07 10:31:24 CST 2016

Total time taken to generate the page: 0.13180 seconds