Home » SQL & PL/SQL » SQL & PL/SQL » Table update through nested loop issue in function
 Table update through nested loop issue in function [message #652985] |
Thu, 23 June 2016 06:53  |
 |
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
|
Junior Member |
|
|
Dear Sir,
I have two tables and want to update first table as per second table's data through function using two cursor and pass parameter item_id.
Please help me, I am sending you table script,insert statement and output file.
Required Output show in pdf file attached.
Below mention Table script and data insert statements
*********************************************
create table xxbom
(
COMP_ITEM_ID number,
REQ_QTY number,
CALC_REQ_QTY number,
ASN_QTY number,
ASN_DATE date
);
create table xxasn
(
COMP_ITEM_ID number,
ASN_QTY number,
ASN_DATE date
);
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(4575, 55, TO_DATE('07/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(4676, 2200, TO_DATE('07/15/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(5480, 10, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(5964, 20, TO_DATE('10/15/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(6196, 300, TO_DATE('09/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(7344, 700, TO_DATE('03/17/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(7344, 600, TO_DATE('12/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into XXASN
(COMP_ITEM_ID, ASN_QTY, ASN_DATE)
Values
(7381, 100, TO_DATE('06/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(4575, 50, 50);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(4676, 2000, 2000);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(5480, 20, 20);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(5480, 24, 44);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(5964, 10, 10);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(5964, 31, 41);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(5964, 3, 44);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(6196, 200, 200);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(6196, 90, 290);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(7344, 200, 200);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(7344, 430, 630);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(7344, 97, 727);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(7344, 160, 887);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(7381, 10, 10);
Insert into XXBOM
(COMP_ITEM_ID, REQ_QTY, CALC_REQ_QTY)
Values
(7381, 90, 100);
COMMIT;
*********************************************
Regards,
Nitesh
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Table update through nested loop issue in function [message #652993 is a reply to message #652991] |
Thu, 23 June 2016 07:29  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
nitesh.erp@gmail.com wrote on Thu, 23 June 2016 14:25Oracle version is 11.1.0.7
item-5964 first time required 10 and its stock(XXASN Table)-20, so first record consume 10 as per requirement and second line required 31 but its remaining only 10 (20 was stock and 10 was consumed, so remaining 10 will be move).
There is no time in XXBOM, so why 10, 31, 3 and not 3, 10, 31 or 31, 10, 3?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Table update through nested loop issue in function [message #653008 is a reply to message #653000] |
Thu, 23 June 2016 08:30 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 XXASN1 as (
3 select COMP_ITEM_ID, ASN_QTY, ASN_DATE,
4 sum(ASN_QTY) over
5 (partition by COMP_ITEM_ID order by ASN_DATE
6 rows between unbounded preceding and current row)
7 tot_ASN_QTY,
8 row_number() over (partition by COMP_ITEM_ID order by ASN_DATE desc) rn
9 from XXASN
10 )
11 select b.COMP_ITEM_ID, b.REQ_QTY, b.CALC_REQ_QTY,
12 case
13 when b.CALC_REQ_QTY <= a.tot_ASN_QTY then b.REQ_QTY
14 when b.CALC_REQ_QTY-b.REQ_QTY < a.tot_ASN_QTY
15 then a.tot_ASN_QTY-b.CALC_REQ_QTY+b.REQ_QTY
16 end ASN_QTY,
17 case
18 when b.CALC_REQ_QTY <= a.tot_ASN_QTY
19 or b.CALC_REQ_QTY-b.REQ_QTY < a.tot_ASN_QTY
20 then a.ASN_DATE
21 end ASN_DATE
22 from XXBOM b, XXASN1 a
23 where a.COMP_ITEM_ID = b.COMP_ITEM_ID
24 and a.tot_ASN_QTY =
25 (select max(a2.tot_ASN_QTY) from XXASN1 a2
26 where a2.COMP_ITEM_ID = b.COMP_ITEM_ID
27 and ( ( a2.tot_ASN_QTY >= b.CALC_REQ_QTY
28 and a2.tot_ASN_QTY-a2.ASN_QTY < b.CALC_REQ_QTY )
29 or ( a2.tot_ASN_QTY < b.CALC_REQ_QTY and a2.rn = 1 ) ))
30 order by b.COMP_ITEM_ID, b.CALC_REQ_QTY
31 /
COMP_ITEM_ID REQ_QTY CALC_REQ_QTY ASN_QTY ASN_DATE
------------ ---------- ------------ ---------- -------------------
4575 50 50 50 30/07/2016 00:00:00
4676 2000 2000 2000 15/07/2016 00:00:00
5480 20 20 10 30/08/2016 00:00:00
5480 24 44
5964 10 10 10 15/10/2016 00:00:00
5964 31 41 10 15/10/2016 00:00:00
5964 3 44
6196 200 200 200 30/09/2016 00:00:00
6196 90 290 90 30/09/2016 00:00:00
7344 200 200 200 17/03/2016 00:00:00
7344 430 630 430 17/03/2016 00:00:00
7344 97 727 97 30/12/2016 00:00:00
7344 160 887 160 30/12/2016 00:00:00
7381 10 10 10 30/06/2016 00:00:00
7381 90 100 90 30/06/2016 00:00:00
15 rows selected.[Updated on: Thu, 23 June 2016 08:33] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 15:42:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
