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


Oracle LazyDBA home page