Home » SQL & PL/SQL » SQL & PL/SQL » Sequentially update a column with a decrementing value
Sequentially update a column with a decrementing value [message #689333] |
Fri, 24 November 2023 13:28  |
 |
madfeen
Messages: 5 Registered: November 2023
|
Junior Member |
|
|
I want to update the "QTY_REQ" column in the PLSQL table in the screen cap attached sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to the second row (QTY 2, LOC 10800A01) and enter 2 for QTY_REQ. Then finally go to the third row (QTY 2, LOC 10800B01) and enter 1 for QTY_REQ. So I evenly divide out the values and stop when the total is reached. The number could be 5 and would just fill in the first two rows (4 entered for for QTY_REQ for the first and 1 entered for for QTY_REQ for the second).
Important point; the value that is entered for QTY_REQ cannot be higher than the value that is in the QTY column for that row. If someone can help with this that would be appreciated, thank you.
|
|
|
|
|
Re: Sequentially update a column with a decrementing value [message #689336 is a reply to message #689335] |
Fri, 24 November 2023 14:44   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I will surely not access a site I don't know and download some file.
You can post its content here (don't forget to use code tags).
You can write the INSERT statements for the data you showed in your image, this is a test case (please read the links).
Using the standard EMP table we can play with the salary column asking for a total we want:
SQL> select empno, sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
We request for a total of 10000 or 20000 (for instance):
SQL> def req=10000
SQL> with
2 data as (
3 select empno, sal,
4 row_number() over (order by sal desc, empno) rn
5 from emp
6 ),
7 compute (empno, sal, rn, sal_req, total) as (
8 select empno, sal, rn,
9 case when sal >= &req then &req else sal end sal_req,
10 case when sal >= &req then &req else sal end total
11 from data
12 where rn = 1
13 union all
14 select d.empno, d.sal, d.rn,
15 case when c.total+d.sal >= &req then &req-c.total else d.sal end,
16 c.total + case when c.total+d.sal >= &req then &req-c.total else d.sal end
17 from compute c, data d
18 where d.rn = c.rn + 1
19 and c.total < &req
20 )
21 select empno, sal, sal_req
22 from compute
23 order by sal desc, empno
24 /
EMPNO SAL SAL_REQ
---------- ---------- ----------
7839 5000 5000
7788 3000 3000
7902 3000 2000
3 rows selected.
SQL> def req=20000
SQL> /
EMPNO SAL SAL_REQ
---------- ---------- ----------
7839 5000 5000
7788 3000 3000
7902 3000 3000
7566 2975 2975
7698 2850 2850
7782 2450 2450
7499 1600 725
7 rows selected.
|
|
|
Re: Sequentially update a column with a decrementing value [message #689337 is a reply to message #689336] |
Fri, 24 November 2023 14:51   |
 |
madfeen
Messages: 5 Registered: November 2023
|
Junior Member |
|
|
The code that creates the table is below. I have also attached it as a file
CREATE TABLE "IFSAPP"."MATLIST2"
( "LOC" VARCHAR2(1000 BYTE),
"QTY" VARCHAR2(1000 BYTE),
"LIN" VARCHAR2(1000 BYTE),
"PART_" VARCHAR2(1000 BYTE),
"SHOP_ORD" VARCHAR2(1000 BYTE),
"PICK_KEY" VARCHAR2(1000 BYTE),
"QTY_REQ" VARCHAR2(200 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "IFSAPP_DATA" ;
REM INSERTING into IFSAPP.MATLIST2
SET DEFINE OFF;
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B41','4','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800A01','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B01','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B21','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B61','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800A01','1','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800A21','1','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B21','1','14','1020123','49507','24377',null);
-
Attachment: script.sql
(Size: 1.69KB, Downloaded 1325 times)
|
|
|
|
|
|
|
Re: Sequentially update a column with a decrementing value [message #689342 is a reply to message #689340] |
Sat, 25 November 2023 02:26   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Using pure SQL:
SQL> select * from "MATLIST2" order by qty desc, loc;
LOC QTY LIN PART_ SHOP_ORD PICK_KEY QTY_REQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
10800B41 4 14 1020123 49507 24377
10800A01 2 14 1020123 49507 24377
10800B01 2 14 1020123 49507 24377
10800B21 2 14 1020123 49507 24377
10800B61 2 14 1020123 49507 24377
10800A01 1 14 1020123 49507 24377
10800A21 1 14 1020123 49507 24377
10800B21 1 14 1020123 49507 24377
8 rows selected.
SQL> def req=7
SQL> merge into MATLIST2 m
2 using (
3 with
4 data as (
5 select m.*, rowid rid,
6 row_number() over (order by qty desc, loc) rn
7 from MATLIST2 m
8 ),
9 compute (LOC,QTY, LIN, PART_, SHOP_ORD, PICK_KEY, rid, rn, QTY_REQ, total) as (
10 select LOC, QTY, LIN,PART_, SHOP_ORD, PICK_KEY, rid, rn,
11 case when QTY >= &req then &req else QTY end QTY_REQ,
12 case when QTY >= &req then &req else QTY end total
13 from data
14 where rn = 1
15 union all
16 select d.LOC, d.QTY, d.LIN, d.PART_, d.SHOP_ORD, d.PICK_KEY, d.rid, d.rn,
17 case when c.total+d.QTY >= &req then &req-c.total else d.QTY end,
18 c.total + case when c.total+d.QTY >= &req then &req-c.total else d.QTY end
19 from compute c, data d
20 where d.rn = c.rn + 1
21 and c.total < &req
22 )
23 select LOC, QTY, LIN, PART_, SHOP_ORD, PICK_KEY, QTY_REQ, rid
24 from compute
25 ) c
26 on ( m.rowid = c.rid )
27 when matched then update set QTY_REQ = c.QTY_REQ
28 /
3 rows merged.
SQL> select * from "MATLIST2" order by qty desc, loc;
LOC QTY LIN PART_ SHOP_ORD PICK_KEY QTY_REQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
10800B41 4 14 1020123 49507 24377 4
10800A01 2 14 1020123 49507 24377 2
10800B01 2 14 1020123 49507 24377 1
10800B21 2 14 1020123 49507 24377
10800B61 2 14 1020123 49507 24377
10800A01 1 14 1020123 49507 24377
10800A21 1 14 1020123 49507 24377
10800B21 1 14 1020123 49507 24377
8 rows selected.
|
|
|
Re: Sequentially update a column with a decrementing value [message #689348 is a reply to message #689342] |
Sun, 26 November 2023 13:54   |
Solomon Yakobson
Messages: 3303 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Match recognize solution:
merge
into matlist2 t
using (
select rowid rid,
req_qty
from matlist2
match_recognize(
order by qty desc
measures greatest(0,least(&qty_on_hand + qty - sum(qty),qty)) req_qty
all rows per match
pattern(p+)
define p as 1 = 1
)
) s
on (t.rowid = s.rid)
when matched
then update
set t.qty_req = s.req_qty
/
Enter value for qty_on_hand: 7
old 9: measures greatest(0,least(&qty_on_hand + qty - sum(qty),qty)) req_qty
new 9: measures greatest(0,least(7 + qty - sum(qty),qty)) req_qty
8 rows merged.
SQL> select *
2 from matlist2
3 /
LOC QTY LI PART_ SHOP_ PICK_ QTY_REQ
-------- ---------- -- ------- ----- ----- ----------
10800B41 4 14 1020123 49507 24377 4
10800A01 2 14 1020123 49507 24377 1
10800B01 2 14 1020123 49507 24377 2
10800B21 2 14 1020123 49507 24377 0
10800B61 2 14 1020123 49507 24377 0
10800A01 1 14 1020123 49507 24377 0
10800A21 1 14 1020123 49507 24377 0
10800B21 1 14 1020123 49507 24377 0
8 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 04:45:42 CST 2025
|