SQL*Plus Gurus:

When I run the following query, it's causing values from b.ic_quantity
to duplicate and sum a incorrect number:

SELECT a.part_code,
a.ic_lot_number,
b.ic_status_code as STATUS,
sum(b.ic_quantity) as Ross,
sum(c.quantity) as API
FROM fin_prod.part_batches a,
fin_prod.ic_lot_status b,
api_prod.api_intellilabel c
WHERE a.COMPANY_CODE = '1' and
a.COMPANY_CODE = b.COMPANY_CODE and
a.COMPANY_CODE = c.COMPANY_CODE and
a.WAREHOUSE = '&varWHSE' and
a.WAREHOUSE = b.warehouse and
a.WAREHOUSE = c.warehouse and
a.part_code = b.part_code and
a.part_code = c.part_code and
a.ic_lot_number = b.ic_lot_number and
a.ic_lot_number = c.ic_lot_number and
b.ic_status_code <> 'CONFIN' and
b.ic_status_code = c.ic_status_code and
b.ic_quantity <> 0 and
c.quantity <> 0 and
c.api_intelli_status NOT IN ('C','V') and
a.part_code = '19351'
GROUP BY a.part_code, a.ic_lot_number, b.ic_status_code, b.ic_quantity,
c.quantity;


The problem is that fin_prod.ic_lot_status b has one record per this
query and api_prod.api_intellilabel c can have multiple:

PART_CODE IC_LOT_NUMBER STATUS ROSS API
--------------- --------------- -------- ---------- ----------
19351 1935101080605 QOH 8 1
19351 1935101080605 QOH 8 7

The 8 in line 2 for ROSS is listed twice because of the join.

What I want is:

PART_CODE IC_LOT_NUMBER STATUS ROSS API
--------------- --------------- -------- ---------- ----------
19351 1935101080605 QOH 8 8

Anyway around this or do I have to do in two steps or in PL/SQL?







Oracle LazyDBA home page