In the group by clause you need to specify a.part_code, a.ic_lot_number,
b.ic_status_code columns only...you d not need columns b.ic_quantity and
c.quantity.
Anand Prakash
>>> "Anthony Ballo "
<oracledba-ezmlmshield-x11731413.[Email address protected] 04/06/05 9:37
AM >>>
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?
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
==============================================================================
Oracle LazyDBA home page