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!

typical where condition...plz help

Discussion in 'SQL PL/SQL' started by radhi07, Dec 18, 2009.

  1. radhi07

    radhi07 Guest

    sample data of sup table

    ACCOUNT_STATUS BILLING_STATUS SUPPRESSION_FLAG BILLING_STATUS_TEXT
    99999 BB 99999 Unbill
    99999 XX 99999 Unbill
    OK XX 99999 Bill
    PE XX N Unbill

    sample data of ast table

    account_num ACCOUNT_STATUS BILLING_STATUS
    12356566 OK XX
    96785674 AB XX


    Here what my intension is
    for the account '96785674' there was no matching account_status in sup table
    in that case i need to select BILLING_STATUS_TEXT corresponds to sup.account_status='99999'

    For the account '12356566' there is a matching 12356566
    in this case i need to select BILLING_STATUS_TEXT corresponds to sup.account_status=' OK'


    I tried the above scenario with the below query

    select ast.account_num,sup.billing_status_text
    from ast,sup
    where
    (sup.account_status=ast.account_status
    or sup.account_status='99999')
    and ast.billing_status=sup.billing_status

    the output is :
    account_num billing_status_text
    12356566 Bill
    12356566 unbill
    96785674 unbill

    Dulipcates are coming for '12356566'

    Please suggest me a solution to aviod this............................
     
  2. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Hi,

    May not be the best query for this requirement, but you can try this one :

    SELECT A1,B1
    FROM
    (
    (
    select ast.account_num AS a1,
    sup.billing_status_text AS b1
    from test_AST ast,
    test_sup sup
    where sup.account_status =ast.account_status
    and ast.billing_status=sup.billing_status
    )
    union
    (
    select ast1.account_num AS a1,
    sup1.billing_status_text AS b1
    from test_AST ast1,
    test_sup sup1
    where sup1.account_status <> ast1.account_status
    and sup1.account_status = '99999'
    AND ast1.account_num NOT IN (select ast.account_num
    from test_AST ast,
    test_sup sup
    where sup.account_status =ast.account_status
    and ast.billing_status=sup.billing_status
    )
    and ast1.billing_status=sup1.billing_status
    )
    )
    ;

    Regards,
    Mohammed
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Use correlated subquery and join.

    Code (SQL):

    SQL> SELECT * FROM SUP;

    ACCOUNT_STATUS       BILLING_STATUS       SUPPRESSION_FLAG     BILLING_STATUS_TEXT
    -------------------- -------------------- -------------------- --------------------
    99999                XX                   99999                Unbill
    99999                BB                   99999                Unbill
    OK                   XX                   99999                bill
    PE                   XX                   N                    Unbill

    SQL> SELECT * FROM AST;

    ACCOUNT_NUM          ACCOUNT_STATUS       BILLING_STATUS
    -------------------- -------------------- --------------------
    12356566             OK                   XX
    96785674             AB                   XX

    SQL> SELECT ACCOUNT_NUM, nvl(SUP.ACCOUNT_STATUS,'99999') ACCOUNT_STATUS ,AST.BILLING_STATUS,
      2          ( SELECT BILLING_STATUS_TEXT
      3                          FROM SUP SUP1
      4                         WHERE SUP1.ACCOUNT_STATUS = nvl(SUP.ACCOUNT_STATUS,'99999')
      5                           AND SUP1.BILLING_STATUS = AST.BILLING_STATUS )  BILLING_TEXT
      6  FROM SUP ,  AST
      7  WHERE SUP.ACCOUNT_STATUS(+) = AST.ACCOUNT_STATUS
      8  AND SUP.BILLING_STATUS (+)= AST.BILLING_STATUS;

    ACCOUNT_NUM          ACCOUNT_STATUS       BILLING_STATUS       BILLING_TEXT
    -------------------- -------------------- -------------------- --------------------
    12356566             OK                   XX                   bill
    96785674             99999                XX                   Unbill

    SQL>