Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    18

    Records based on 2 status

    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.

  2. #2
    Join Date
    Feb 2006
    Posts
    172
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;
    Last edited by tonkuma; 01-27-13 at 14:25. Reason: Add ORDER BY clause.

  4. #4
    Join Date
    Jun 2012
    Posts
    18
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •