Home » SQL & PL/SQL » SQL & PL/SQL » Fetch records based on 2 status (Oracle 10g )
Fetch records based on 2 status [message #575677] Fri, 25 January 2013 10:27 Go to next message
ashagang
Messages: 10
Registered: December 2012
Junior Member
I have a table with below description.

create table test_exp (oid number, ioid number, status varchar2(20));

Below are the insert statements.

Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');  

The output looks like below:

OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
1 |102 |CANCELLED
2 |103 |NEW
3 |104 |DISCO
4 |105 |DISCO
4 |106 |NEW
5 |107 |NEW
5 |108 |CANCELLED  

Now my problem is we should fetch the data based on the below rules

If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records

If an OID has only 1 of these status, then ignore the same

If an OID has none of the 2 status, then ignore the same.

The expected output will be like below:
OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW  

Can somebody please help.



[Edit MC: change quote tags to code ones]

[Updated on: Fri, 25 January 2013 10:32] by Moderator

Report message to a moderator

Re: Fetch records based on 2 status [message #575678 is a reply to message #575677] Fri, 25 January 2013 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if there are more than 2 rows with status NEW or DISCO?

Use code tags not quote tags for code. This said thanks for the test case.

Regards
Michel

[Updated on: Fri, 25 January 2013 10:34]

Report message to a moderator

Re: Fetch records based on 2 status [message #575679 is a reply to message #575678] Fri, 25 January 2013 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming there can be only at most 1 row with each status:
SQL> select * from test_exp
  2  where oid in (select oid from test_exp 
  3                where status in ('NEW','DISCO')
  4                group by oid having count(*) = 2)
  5    and status in ('NEW','DISCO')
  6  order by 1, 2, 3
  7  /
       OID       IOID STATUS
---------- ---------- --------------------
         1        100 NEW
         1        101 DISCO
         4        105 DISCO
         4        106 NEW

Regards
Michel
Re: Fetch records based on 2 status [message #575714 is a reply to message #575679] Fri, 25 January 2013 14:31 Go to previous messageGo to next message
ashagang
Messages: 10
Registered: December 2012
Junior Member
Hi,

I got the same query. But i thought of checking to find if we can make it more efficient by using analytical functions or any other way.

I really appreciate your help.
Re: Fetch records based on 2 status [message #575715 is a reply to message #575714] Fri, 25 January 2013 14:58 Go to previous message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
SQL> with 
  2    data as (
  3      select t.*, count(*) over (partition by oid) cnt
  4      from test_exp t
  5      where status in ('NEW','DISCO')
  6    )
  7  select oid, ioid, status
  8  from data
  9  where cnt = 2
 10  order by 1, 2, 3
 11  /
       OID       IOID STATUS
---------- ---------- --------------------
         1        100 NEW
         1        101 DISCO
         4        105 DISCO
         4        106 NEW


Regards
Michel
Previous Topic: External Table for CSV delimited file skipping columns
Next Topic: DBMS_JOBS failure check
Goto Forum:
  


Current Time: Thu Dec 18 21:35:11 CST 2014

Total time taken to generate the page: 0.06236 seconds