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!

Problem with query

Discussion in 'SQL PL/SQL' started by Kentsky, Mar 11, 2010.

  1. Kentsky

    Kentsky Guest

    Hi Guys.

    I hope you can help me solve this problem, i have a query below and i want to convert (+) into join syntax like 'left outer join ? on ? = ?'

    i hope someone can solve this. :confused:

    Code (SQL):
    SELECT debit_note.note_oid,
             debit_note.note_no,
                 debit_note.pocy_oid,
             txn.amt txn_amt
    FROM debit_note,
               settlement,
               settlement_note,
               bf_txn
    WHERE debit_note.note_oid = settlement_note.note_oid_settled(+)
       AND settlement.sett_oid(+) = settlement_note.sett_oid
       AND txn.bftx_oid = debit_note.bftx_oid
       AND ((TO_DATE(TO_CHAR(settlement.settle_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE('12/01/2009','MM/DD/YYYY')
         OR TO_DATE(TO_CHAR(settlement.crt_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE('12/01/2009','MM/DD/YYYY')
         OR TO_DATE(TO_CHAR(settlement.reverse_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE ('12/01/2009','MM/DD/YYYY'))
         OR TO_DATE (TO_CHAR(debit_note.bill_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE('12/01/2009','MM/DD/YYYY'))
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Having no DDL to create the tables nor any sample data this is a stab in the dark but it may return the same results as the original:

    Code (SQL):
    SELECT debit_note.note_oid,
             debit_note.note_no,
                 debit_note.pocy_oid,
             txn.amt txn_amt
    FROM debit_note dn RIGHT OUTER JOIN (settlement s LEFT OUTER JOIN settlement_note sn ON (sn.sett_oid = s.sett_oid)) ON (dn.note_oid = s.note_oid_settled),
               bf_txn bt
    WHERE bt.bftx_oid = dn.bftx_oid
       AND ((TO_DATE(TO_CHAR(s.settle_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE('12/01/2009','MM/DD/YYYY')
         OR TO_DATE(TO_CHAR(s.crt_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE('12/01/2009','MM/DD/YYYY')
         OR TO_DATE(TO_CHAR(s.reverse_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE ('12/01/2009','MM/DD/YYYY'))
         OR TO_DATE (TO_CHAR(dn.bill_date,'MM/DD/YYYY'),'MM/DD/YYYY')
            BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
            AND TO_DATE('12/01/2009','MM/DD/YYYY'));