Thread: Problem with join query
View Single Post
  #12 (permalink)  
Old 03-12-10, 11:42
chuck_forbes chuck_forbes is offline
Registered User
  
Join Date: Dec 2003
Posts: 1,072
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 ....)
Reply With Quote