Records based on 2 status

ashachandrika · #1 (**permalink**) 01-25-13, 11:21

I have a table with below description.

Quote:

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:

Quote:

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:

Quote:

OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW

Can somebody please help.

spacebar · #2 (**permalink**) 01-25-13, 17:04

This is one way:

Code:

with sum as(
  select oid, count(*)
   from  test_exp
  where  status in( 'NEW', 'DISCO' )
  group by oid
  having count( * ) > 1 )
select t.*
 from  test_exp t join sum s on t.oid  =  s.oid
where  t.status in( 'NEW', 'DISCO' )
order by t.oid, t.ioid

tonkuma · #3 (**permalink**) 01-27-13, 13:20

This may be another way:

Code:

SELECT oid , ioid , status
 FROM (SELECT t.*
            , COUNT(*) OVER( PARTITION BY oid ) AS count_status
        FROM  test_exp AS t
        WHERE status IN( 'NEW' , 'DISCO' )
      )
 WHERE count_status >= 2
 ORDER BY
       oid , ioid
;

ashachandrika · #4 (**permalink**) 01-28-13, 12:40

Lets say, i have another value called MOVE.

Now, i should consider both DISCO and MOVE as same priority.

I should fetch records based on below fields.

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

If an OID has only either NEW or DISCO or MOVE , then ignore the same

If an OID has both DISCO and MOVE but there is no NEW associated, then ignore the same

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

I have updated the conditions 2 and 3 as above.

Can you help me to frame the query in that manner?

Internet Services

Web Development

Digital Marketing

Consumer Tech