asked on

SQL Help

This is embarrasing but I'm having trouble with a SQL join I was hoping someone could assit with.

I'm writing a report (by item_id) that includes cost, po and invoice information. I'm fine with the item and cost data until I start joining to the po tables for the po data. Since I have to go to po_lines_all to complete the join from the item master to the po_header (for inventory_item_id), I'm now getting multiple records because of the individual po_lines. I don't see any way around this though. Can this not all be done through the same SQL statement?

Here is a small chunk of the code including the problematic join:

SELECT
msi.segment1                         item,
msi.organization_id                        org,
sum(qoh.transaction_quantity)                  qoh, -- total quantity on hand
cic.item_cost                         item_cost, -- frozen cost
max(poh.segment1)                        po_num -- latest PO
FROM
mtl_system_items_b                         msi,
mtl_onhand_quantities                  qoh,
cst_item_costs                        cic,
po_headers_all                        poh,
po_lines_all                        pol
WHERE
msi.inventory_item_id = qoh.inventory_item_id and
msi.inventory_item_id = cic.inventory_item_id and
cic.cost_type_id = 1 and
cic.organization_id = msi.organization_id and
pol.item_id = msi.inventory_item_id and
poh.po_header_id = pol.po_header_id and
msi.organization_id = 18 and
msi.inventory_item_id = 2746
GROUP BY
msi.segment1,
msi.organization_id,
cic.item_cost

Sujith

Does it help?

SELECT
msi.segment1 item,
msi.organization_id org,
sum(qoh.transaction_quantity) qoh, -- total quantity on hand
cic.item_cost item_cost, -- frozen cost
max(poh.segment1) po_num -- latest PO
FROM
mtl_system_items_b msi,
mtl_onhand_quantities qoh,
cst_item_costs cic,
po_headers_all poh,
(select distinct item_id, po_header_id from po_lines_all ) pol
WHERE
msi.inventory_item_id = qoh.inventory_item_id and
msi.inventory_item_id = cic.inventory_item_id and
cic.cost_type_id = 1 and
cic.organization_id = msi.organization_id and
pol.item_id = msi.inventory_item_id and
poh.po_header_id = pol.po_header_id and
msi.organization_id = 18 and
msi.inventory_item_id = 2746
GROUP BY
msi.segment1,
msi.organization_id,
cic.item_cost

schwertner

If I understand you correctly this join

poh.po_header_id = pol.po_header_id

produces many lines.

You have to limit it somehow, e.g.

poh.po_header_id IN (select po_header_id from pol)

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

feign3

ASKER

Markgeer, yours was in the right ball park... It is the multiple po_line_id's that are producing the problem so I added:

pol.po_line_id = (select max(pol.po_line_id)
from po_lines_all pol
where pol.item_id = 2746)

This seems to have fixed things.

Thanks everyone!