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
feign3Asked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Or, maybe you need a sub-query to get the max(po_header_id) from po_lines for that item, then remove the join to po_lines_all from the main query like this:

(Note that I also re-wrote the "where" clauses to put all of the known values on the right, and consistently use mtl_system_items as the "driving" table.)

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,
WHERE
qoh.inventory_item_id          = msi.inventory_item_id and
cic.inventory_item_id           =  msi.inventory_item_id and
cic.cost_type_id                   = 1 and
cic.organization_id               = msi.organization_id and
poh.po_header_id                = (select max pol.po_header_id from po_lines_all pol
                                              where pol.item_id = msi.inventory_item_id) and
msi.organization_id              = 18 and
msi.inventory_item_id           = 2746
GROUP BY
msi.segment1,
msi.organization_id,
cic.item_cost
0
 
SujithData ArchitectCommented:
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
0
 
schwertnerCommented:
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)
0
 
feign3Author Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.