Help Needed with CASE (I think)

[StevenB]

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?

Thanks,

Steve

 

[LKBrwnDBA]

Try:

Select Person_Id, 
    Case Cnt When 1 Then Id 
      Else 'Multiple' 
      End As Status_Id
  From (
    Select Person_Id, MAX(Status_Id) Id
         , Count(Status_Id) Cnt
      From MyTable Group By Person_Id)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[StevenB]

Hrm, it says "FROM keyword not found where expected (ORA-00923)".

Here's the exact SQL I'm using:

Select resume_id, 
    Case Cnt When 1 Then Id 
      Else 'Declined to State' 
      End As eeo_ethnicity_id
  From (
    Select resume_id, 
	       MAX(eeo_ethnicity_id) Id,
           Count(eeo_ethnicity_id) Cnt
      From res_eeo_race Group By resume_id)

(Yeah, I know, not quite as simple as "person id" and "status id" but you get the idea.

I'm not sure if this is relevant, but I'm just trying to run this SQL via SQLPlus, and not via a PL/SQL procedure or anything like that. Does that have any impact?

Thanks!

Steve

 

[LKBrwnDBA]

Maybe your version of 8i does not support CASE?

If not, try this:

Select resume_id, 
    Decode(Cnt,1,'Declined to State') eeo_ethnicity_id
  From (
    Select resume_id, 
           MAX(eeo_ethnicity_id) Id,
           Count(eeo_ethnicity_id) Cnt
      From res_eeo_race Group By resume_id)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Beilstwh]

There is no need to get so complicated. Use the following.

SQL> DESC A
Name Null? Type
----------------------------------------- -------- ----------------------------
PERSON_ID NUMBER
STAT NUMBER

SQL> SELECT * FROM A;

PERSON_ID STAT
---------- ----------
12345 1
55555 1
55555 2
55555 3

SQL> select PERSON_ID, decode(count(stat),1,TO_CHAR(MAX(STAT)),'MULTIPLE') STAT
2 FROM A
3 GROUP BY PERSON_ID;

PERSON_ID STAT
---------- ----------------------------------------
12345 1
55555 MULTIPLE


Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Excellent improvement, Bill. And under Steven's circumstances, I believe you can tighten the code even more to:

select PERSON_ID, decode(count(*),1,'MULTIPLE') STAT
  2  FROM A
  3  GROUP BY PERSON_ID;

...since count(stat) results cannot be null or 0.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[StevenB]

Beautiful! I'm all set, thanks everyone!

Steve