Oracle 11i SQL causing multiple rows to return

Question

If anyone can assist this would be great. I have written the following query, but unless I use distinct it has multiple rows returned. With distinct it works as expected, but without it is very slow and returns many rows.

SELECT distinct ('RCT' || rt.transaction_id) Unique_Receipt_Source_ID,
                hla.location_code warehouse_code,
                    iwm.whse_name warehouse_name,
                pvs.vendor_site_id vendor_site_id,
                pvs.vendor_site_code vendor_site_name,
                pha.vendor_id,
                v.vendor_name,
                pha.segment1 po_num,
                pla.attribute4 container,
                pla.attribute5 fill_level,
                pha.fob_lookup_code inco_terms,
                iwi.fixed_leadtime,
                pla.line_num po_line_num,
                iim.item_no item_no,
                rt.transaction_date receipt_date,
                rt.quantity Receipt_quantity,
                rt.unit_of_measure Receipt_UOM,
                rt.currency_code Receipt_Currency_Code,
                (rt.quantity * pla.unit_price) receipt_amount,
                pla.unit_price PO_Unit_price,
                iim.item_desc1 item_description,
                pla.item_description po_line_description,
                pha.terms_id SYS_pay_terms_id,
                ap.name pay_terms_name,
                ap.description pay_terms_description,
                papf.full_name BUYER,
                plla.country_of_origin_code country_of_origin,
                (pla.quantity * pla.unit_price) PO_LINE_AMOUNT,
                pha.currency_code PO_currency_code,
                pla.quantity PO_LINE_QTY,
                pla.unit_meas_lookup_code PO_LINE_UOM
  FROM po_headers_all pha,
       po_line_locations_all plla,
       po_lines_all pla,
       apps.po_vendor_sites_all pvs,
       apps.po_vendors v,
       rcv_transactions rt,
       apps.ic_whse_inv iwi,
       apps.ic_whse_mst iwm,
       apps.ic_item_mst iim,
       apps.per_all_people_f papf,
       apps.hr_locations_all hla,
       ap_terms ap,
       mtl_system_items_b msi
WHERE  pha.po_header_id = pla.po_header_id
       AND plla.po_header_id = pha.po_header_id
       AND papf.person_id = pha.agent_id
       AND pha.vendor_id = v.vendor_id
       AND pha.vendor_site_id = pvs.vendor_site_id
       AND v.vendor_id = pvs.vendor_id
       AND pla.po_header_id = pha.po_header_id
       AND iim.item_no = msi.segment1 
       and msi.inventory_item_id = pla.item_id    
       and iwi.item_id = iim.item_id   
       AND rt.po_header_id = pha.po_header_id
       AND hla.location_id = pha.ship_to_location_id
       AND rt.po_line_id = pla.po_line_id
       AND iwm.whse_code = iwi.whse_code
       AND hla.location_code = iwi.whse_code      
       AND pha.terms_id = ap.term_id
       AND rt.transaction_type = 'RECEIVE'       
       order by pha.segment1, pla.line_num;

* option a) (better) rewrite using LEFT|OUTER JOIN .. ON syntax * option b) use EXPLAIN and look for CARTESIAN — Lucio M. Tato
– Lucio M. Tato, Commented Dec 1, 2014 at 21:46

Jon Heller · Accepted Answer · 2014-12-02 05:23:04Z

This query is a good example of the advantages of the ANSI join syntax over the implicit-join syntax. The ANSI join syntax forces you to build a result set one step at a time. Keeping the tables and their predicates close together makes the statement easier to understand and debug.

The original query requires remembering dozens of items to understand any one piece of it. The new syntax requires understanding the set, the new table, and the new predicates that add it to the set; repeat those steps until the query is complete.

The rewritten query below shows some more specific benefits of the ANSI join syntax. When the predicates are forced to be ordered it is obvious that one of the predicates is repeated. Is that predicate a meaningless duplicate or is it a typo of a missing condition? Combining this syntax with a modern IDE allows you to quickly find mistakes; highlight a set, run it, check the results, add the next table and predicates to the highlight, run it, check the results; repeat those steps until you find the bug.

  SELECT *
    FROM po_headers_all pha
    JOIN po_line_locations_all plla
      ON pha.po_header_id = plla.po_header_id
    JOIN po_lines_all pla
      ON pha.po_header_id = pla.po_header_id
     AND pha.po_header_id = pla.po_header_id --Repeated predicate - typo?
    JOIN apps.po_vendor_sites_all pvs
      ON pha.vendor_site_id = pvs.vendor_site_id
    JOIN apps.po_vendors v
      ON pha.vendor_id = v.vendor_id
     AND pvs.vendor_id = v.vendor_id
    JOIN rcv_transactions rt
      ON pla.po_line_id = rt.po_line_id
     AND pha.po_header_id = rt.po_header_id
     AND rt.transaction_type = 'RECEIVE' --Not a join, but may be helpful here.
    JOIN apps.hr_locations_all hla
      ON pha.ship_to_location_id = hla.location_id
    JOIN apps.ic_whse_inv iwi
      ON hla.location_code = iwi.whse_code
    JOIN apps.ic_whse_mst iwm
      ON iwi.whse_code = iwm.whse_code
    JOIN mtl_system_items_b msi
      ON pla.item_id = msi.inventory_item_id
    JOIN apps.ic_item_mst iim
      ON msi.segment1 = iim.item_no
     AND iwi.item_id = iim.item_id
    JOIN apps.per_all_people_f papf
      ON pha.agent_id = papf.person_id
    JOIN ap_terms ap
      ON pha.terms_id = ap.term_id
ORDER BY pha.segment1, pla.line_num;

Collectives™ on Stack Overflow

Oracle 11i SQL causing multiple rows to return

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related