Sorry, that should be
GROUP BY a.part_code, a.ic_lot_number, b.ic_status_code, sum(b.ic_quantity);
or
GROUP BY a.part_code, a.ic_lot_number, b.ic_status_code, ROSS;
-----Original Message-----
From: Cardus Ian
[mailto:oracledba-ezmlmshield-x81548110.[Email address protected]
Sent: 07 April 2005 16:18
To: LazyDBA Discussion
Subject: RE: Query Help
Try.......
SELECT.....
.
.
.
GROUP BY a.part_code, a.ic_lot_number, b.ic_status_code, b.ic_quantity;
-----Original Message-----
From: Anthony Ballo
[mailto:oracledba-ezmlmshield-x29770788.[Email address protected]
Sent: 07 April 2005 16:10
To: LazyDBA Discussion
Subject: RE: Query Help
I get the same results either way:
SQL> SELECT a.part_code,
2 a.ic_lot_number,
3 b.ic_status_code as STATUS,
4 sum(b.ic_quantity) as Ross,
5 sum(c.quantity) as API
6 FROM fin_prod.part_batches a,
7 fin_prod.ic_lot_status b,
8 api_prod.api_intellilabel c
9 WHERE a.COMPANY_CODE = '1' and
10 a.COMPANY_CODE = b.COMPANY_CODE and
11 a.COMPANY_CODE = c.COMPANY_CODE and
12 a.WAREHOUSE = '&varWHSE' and
13 a.WAREHOUSE = b.warehouse and
14 a.WAREHOUSE = c.warehouse and
15 a.part_code = b.part_code and
16 a.part_code = c.part_code and
17 a.ic_lot_number = b.ic_lot_number and
18 a.ic_lot_number = c.ic_lot_number and
19 b.ic_status_code <> 'CONFIN' and
20 b.ic_status_code = c.ic_status_code and
21 b.ic_quantity <> 0 and
22 c.quantity <> 0 and
23 c.api_intelli_status NOT IN ('C','V') and
24 a.part_code = '19351'
25 GROUP BY a.part_code, a.ic_lot_number, b.ic_status_code;
Enter value for varwhse: S1
old 12: a.WAREHOUSE = '&varWHSE' and
new 12: a.WAREHOUSE = 'S1' and
PART_CODE IC_LOT_NUMBER STATUS ROSS API
--------------- --------------- -------- ---------- ----------
19351 1935101080605 QOH 16 8
19351 1935101081805 QOH 357 119
19351 1935101082005 QOH 5080 508
19351 1935101082605 QOH 860 215
SQL>
Line #1 should be:
PART_CODE IC_LOT_NUMBER STATUS ROSS API
--------------- --------------- -------- ---------- ----------
19351 1935101080605 QOH 8 8
-----Original Message-----
From: Sreedhar_Bhaskararaju
[mailto:oracledba-ezmlmshield-x60526049.[Email address protected]
Sent: Wednesday, April 06, 2005 10:23 PM
To: LazyDBA Discussion
Subject: RE: Query Help
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.
************************************************************************
**
--------
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 e-mail and its attachments are confidential and are intended for the above named recipient only. If this has come to you in error, please notify the sender immediately and delete this e-mail from your system. You must take no action based on this, nor must you copy or disclose it or any part of its contents to any person or organisation. Statements and opinions contained in this email may not necessarily represent those of Littlewoods. Please note that e-mail communications may be monitored. The registered office of Littlewoods Limited and it's subsidiaries is 100 Old Hall Street, Liverpool, L70 1AB. Registered number of Littlewoods Limited is 262152.
*****************************************************************************
This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com
--------
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