1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql query

Discussion in 'Oracle Apps Technical' started by tabjula, Feb 22, 2013.

  1. tabjula

    tabjula Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    For a given ID, S_Type values should be same.
    If 'IB' string is mentioned, then s_Type can be either 'IB' or 'RT' for a given ID. If not it is an invalid record.


    CREATE TABLE TEST_1(ID NUMBER,S_TYPE VARCHAR2(20));
    INSERT INTO TEST_1 VALUES(1,'IB');
    INSERT INTO TEST_1 VALUES(1,'RT');
    INSERT INTO TEST_1 VALUES(1,'IB');
    INSERT INTO TEST_1 VALUES(1,'RT');
    INSERT INTO TEST_1 VALUES(1,'UI');
    INSERT INTO TEST_1 VALUES(1,'WE');
    INSERT INTO TEST_1 VALUES(2,'CI');
    INSERT INTO TEST_1 VALUES(2,'CI');
    INSERT INTO TEST_1 VALUES(2,'TY');
    INSERT INTO TEST_1 VALUES(2,'RF');
    INSERT INTO TEST_1 VALUES(3,'KKK');
    INSERT INTO TEST_1 VALUES(4,'OOO');


    REQUIRED DATA:

    ID S_TYPE
    1 UI
    1 WE
    2 TY
    2 RF

    Please help me on this.

    regards,
    dileep.
     
  2. sivaramakrishna.d

    sivaramakrishna.d Active Member

    Messages:
    61
    Likes Received:
    6
    Trophy Points:
    160
    Dear,

    Your requirement is not clear..can u be more clear...?

    Siva Ram
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Dileep

    Find the below query.

    Code (SQL):
    SELECT * FROM TEST_1
    WHERE (id,s_type) IN (SELECT id,s_type FROM TEST_1 GROUP BY id,s_type HAVING COUNT (*) =1 )
    -------------------
    Result:
    --------------

    ID S_TYPE

    1 UI
    1 WE
    2 TY
    2 RF
    3 KKK
    4 OOO



    Regards
    Sambasiva Reddy