Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Red face Unanswered: Problem with join query

    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.

    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. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Start with

    Code:
    FROM debit_note,
    settlement_note
    WHERE debit_note.note_oid = settlement_note.note_oid_settled(+)
    . . .
    turning into

    Code:
    FROM debit_note
         left outer join 
           settlement_note on (debit_note.note_oid = settlement_note.note_oid_settled)
    You should be able to figure out the rest by extrapolating from that, or by searching Google for some examples.

    --=Chuck

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chuck_forbes View Post
    Code:
     on (debit_note.note_oid = settlement_note.note_oid_settled)
    just curious, why the unnecessary parentheses?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Can't remember ... some tool in my distant past had a GUI query editor (I'd bet on MSAccess if you held a gun to my head). When it translated the SQL, it put parens around the join conditions. For poorly formatted SQL it hleps make it more legible (to me). For properly formatted SQL, it's just fluff

    Code:
    FROM table1 join table2 on (table1.field = table2.field) left join table3 on (table1.field = table3.field and table3.other_field = 'A')
    vs

    Code:
    FROM table1 
         join 
           table2 on (table1.field       = table2.field) 
         left join 
           table3 on (table1.field       = table3.field and 
                      table3.other_field = 'A')
    vs

    Code:
    FROM table1 
         join 
           table2 on table1.field       = table2.field
         left join 
           table3 on table1.field       = table3.field and 
                     table3.other_field = 'A'

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chuck_forbes View Post
    For poorly formatted SQL it hleps make it more legible (to me).
    okay, i totally understand this, but ...

    1) if you wrote the poorly formatted SQL in the first place, you should be shot

    2) if you're fixing somebody else's poorly formatted SQL, why would you need to stick parentheses in there, why wouldn't you just format it properly?



    by the way, my own style has slightly different rules...
    Code:
      FROM table1 
    INNER
      JOIN table2 
        ON table2.field = table1.field
    LEFT OUTER
      JOIN table3 
        ON table3.field = table1.field
       AND table3.other_field = 'A'
    notice how the tables being joined have the ON conditions written with that table's column first, so that the table names line up visually (blue), and additional conditions in the ON clause also therefore line up (table3.other_field), giving the ON clause a semblance of order

    note also that the column on the right, jutting out like that (red), always refers to a previously-mentioned table

    INNER and LEFT OUTER are always on a line by themselves to make it easy to spot new tables being added (you do this too) -- i've seen other people actuall put the joins at the end of the line (blech) like this --
    Code:
    FROM table1 join 
         table2 on table1.field = table2.field left join 
         table3 on table1.field = table3.field and table3.other_field = 'A'
    as for the parentheses, yes, that was msaccess, the crappiest SQL generation i've ever seen
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    PRETTY COLORS! me likey

    I am all for any consistent type of formatting.
    Much easier to read when someone hands you their code.
    The worst is when someone hands you code and there are NO SPACES (or minimal spaces)
    very annoying.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Mar 2010
    Posts
    2
    Hi chuck forbes

    thank you for the quick reply i did what you told me and it extracted a lot of records the query should only extract a total of 834 records but it extracted hundred thousands.

    this is the query i did, i replaced the (+) with a join syntax:

    SELECT hbs.bf_debit_note.note_oid, hbs.bf_debit_note.note_no,
    hbs.bf_debit_note.pocy_oid
    FROM hbs.bf_debit_note LEFT OUTER JOIN hbs.bf_settlement_note ON
    hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
    hbs.bf_settlement RIGHT OUTER JOIN hbs.bf_settlement_note ON
    hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
    hbs.bf_txn
    WHERE hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
    AND ( ( TO_DATE (TO_CHAR (hbs.bf_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 (hbs.bf_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 (hbs.bf_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 (hbs.bf_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')
    )

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    re-posted in human readable format

    Code:
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note
           LEFT OUTER JOIN hbs.bf_settlement_note
             ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
           hbs.bf_settlement
           RIGHT OUTER JOIN hbs.bf_settlement_note
             ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
           hbs.bf_txn
    WHERE  hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
           AND ((To_date(To_char(hbs.bf_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(hbs.bf_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(hbs.bf_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(hbs.bf_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'))
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    LEFT OUTER JOIN hbs.bf_settlement_note
             ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
           hbs.bf_settlement
           RIGHT OUTER JOIN hbs.bf_settlement_note
             ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
           hbs.bf_txn
    NONE of the tables in the code block above contribute any data to the SELECT clause.
    Therefore they can & should be eliminated out of the FROM clause.
    No data in the result set comes FROM any of the table above.

    If any or all are really required for data filtering, they should be subordinated into WHERE clause.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    WHY ARE YOU CHANGING A DATE TO A CHAR TO A DATE?
    Leave it as date

    Code:
    SELECT 
      hbs.bf_debit_note.note_oid, 
      hbs.bf_debit_note.note_no,
      hbs.bf_debit_note.pocy_oid
    FROM 
      hbs.bf_debit_note LEFT OUTER JOIN hbs.bf_settlement_note ON
      hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
      hbs.bf_settlement RIGHT OUTER JOIN hbs.bf_settlement_note ON
      hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
      hbs.bf_txn
    WHERE hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
    AND ( hbs.bf_settlement.settle_date BETWEEN 
        TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
    OR hbs.bf_settlement.crt_date BETWEEN 
        TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
    OR hbs.bf_settlement.reverse_date BETWEEN 
        TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
    )
    OR hbs.bf_debit_note.bill_date BETWEEN 
        TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
    )
    Last edited by The_Duck; 03-12-10 at 11:19.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    ----------------------------------------------
    Last edited by chuck_forbes; 03-12-10 at 12:22. Reason: wrong advice - reconsidering

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    This would be my original rewrite of your query:

    Code:
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note
           INNER
            JOIN hbs.bf_txn
              ON hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
           LEFT OUTER 
            JOIN hbs.bf_settlement_note
              ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled
           LEFT OUTER 
            JOIN hbs.bf_settlement
             ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
    WHERE  ( hbs.bf_settlement.settle_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
          OR hbs.bf_settlement.crt_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
          OR hbs.bf_settlement.reverse_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
          OR hbs.bf_debit_note.bill_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
    which pushes all the join criteria into the FROM clause. That's not going to get rid of all those extra records, though. Adding "SELECT DISTINCT ..." probably will, but that's not the way I would write the query either (in-line with anacedent's advice on some of those tables not contributing any columns to the SELECT clause).

    This is a better version, as it's indicating your intent a little better, but the UNION is performing the same role as the DISTINCT, so it's still a little sloppy, IMO

    Code:
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note       
           INNER
            JOIN hbs.bf_txn
              ON hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
    WHERE  hbs.bf_debit_note.bill_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
    UNION
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note
           INNER
            JOIN hbs.bf_txn
              ON hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
           LEFT OUTER 
            JOIN hbs.bf_settlement_note
              ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled
           LEFT OUTER 
            JOIN hbs.bf_settlement
             ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
    WHERE  ( hbs.bf_settlement.settle_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
          OR hbs.bf_settlement.crt_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
          OR hbs.bf_settlement.reverse_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
    SO this would be as explicit as you could get

    Code:
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note       
    WHERE  hbs.bf_debit_note.bill_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
       and EXISTS (select 1 from hbs.bf_txn where hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid)
    UNION ALL
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note
    WHERE EXISTS (select 1 from hbs.bf_txn where hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid) and
          EXISTS (SELECT 1 
                  FROM hbs.bf_settlement_note
                       LEFT OUTER 
                       JOIN hbs.bf_settlement
                         ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
                  WHERE hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled 
                    AND ( hbs.bf_settlement.settle_date BETWEEN 
                            TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
                       OR hbs.bf_settlement.crt_date BETWEEN 
                            TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
                       OR hbs.bf_settlement.reverse_date BETWEEN 
                            TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')))
    which could be simplified to

    Code:
    SELECT hbs.bf_debit_note.note_oid,
           hbs.bf_debit_note.note_no,
           hbs.bf_debit_note.pocy_oid
    FROM   hbs.bf_debit_note       
    WHERE  EXISTS (select 1 from hbs.bf_txn where hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid)
       AND (hbs.bf_debit_note.bill_date BETWEEN 
              TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
            OR
            EXISTS (SELECT 1 
                    FROM hbs.bf_settlement_note
                       LEFT OUTER 
                       JOIN hbs.bf_settlement
                         ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
                    WHERE hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled 
                     AND ( hbs.bf_settlement.settle_date BETWEEN 
                            TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
                        OR hbs.bf_settlement.crt_date BETWEEN 
                            TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
                        OR hbs.bf_settlement.reverse_date BETWEEN 
                            TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))))
    (there are probably parens off somewhere in there ....)

Posting Permissions

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