You need not specify columns under group by which are using functions.
You remove the following columns from group by

b.ic_quantity,
c.quantity
-----Original Message-----
From: Surinder Kaur
[mailto:oracledba-ezmlmshield-x59726928.[Email address protected]
Sent: Thursday, April 07, 2005 7:29 AM
To: LazyDBA Discussion
Subject: Re: Query Help

remove these columns from group by clause

b.ic_quantity,
c.quantity

----- Original Message -----
From: "Anthony Ballo "
<oracledba-ezmlmshield-x11731413.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, April 06, 2005 9:37 AM
Subject: Query Help


> 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
>
>



--------
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
**************************************************************************
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or
distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard
is appreciated.
**************************************************************************

Oracle LazyDBA home page