create table qty_split
(
order varchar2(4),
article_code varchar2(4),
size_1 number(3,0),
size_2 number(3,0),
size_3 number(3,0)
);
insert into qty_split values ('a001', '1111', 123, 165, 85);
commit;
select * from qty_split;
'a001' '1111' 123 165 85
Now I need an Oracle SQL query to split the above row based on upper limit of quantity 99 on columns size_1, size_2 and size_3.
So expected result would be:
'a001' '1111' 99 99 85
'a001' '1111' 24 66 0
You can use a recursive sub-query factoring clause to split the data for any amount:
WITH data ( "ORDER", article, size_1, size_2, size_3 ) AS (
SELECT * FROM qty_split
UNION ALL
SELECT "ORDER",
article,
GREATEST( size_1 - 99, 0 ),
GREATEST( size_2 - 99, 0 ),
GREATEST( size_3 - 99, 0 )
FROM data
WHERE size_1 > 99
OR size_2 > 99
OR size_3 > 99
)
SELECT "ORDER",
article,
LEAST( size_1, 99 ) AS size_1,
LEAST( size_2, 99 ) AS size_2,
LEAST( size_3, 99 ) AS size_3
FROM data
ORDER BY
"ORDER",
article,
ROWNUM;
Which, for the sample data:
create table qty_split (
"ORDER" varchar2(4),
article_code varchar2(4),
size_1 number(3,0),
size_2 number(3,0),
size_3 number(3,0)
);
insert into qty_split
SELECT 'a001', '1111', 123, 165, 85 FROM DUAL UNION ALL
SELECT 'b002', '2222', 312, 45, 17 FROM DUAL UNION ALL
SELECT 'c003', '3333', 0, 0, 417 FROM DUAL;
Outputs:
ORDER | ARTICLE | SIZE_1 | SIZE_2 | SIZE_3 :---- | :------ | -----: | -----: | -----: a001 | 1111 | 99 | 99 | 85 a001 | 1111 | 24 | 66 | 0 b002 | 2222 | 99 | 45 | 17 b002 | 2222 | 99 | 0 | 0 b002 | 2222 | 99 | 0 | 0 b002 | 2222 | 15 | 0 | 0 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 99 c003 | 3333 | 0 | 0 | 21
db<>fiddle here
You can solve this problem with a CONNECT BY query, which may prove faster*** than a recursive query (recursive WITH clause).
In the query below I assume you must do this for one row at a time. If you have a table with many rows that must be processed similarly at the same time, that can be arranged easily. The most efficient way to do that depends on your Oracle version; in Oracle 12.1 or higher that can be done very efficiently with the LATERAL or CROSS APPLY clause, but there is also a different technique that works in all older Oracle versions.
*** EDIT - just out of curiosity, I set up a table with 300,000 rows (combinations of ord and article_code) and values up to 930 in the three "size" columns, resulting in an output of 3 million rows (ten output rows for each input row). The query using the recursive WITH clause runs in 43 seconds on my machine. The CONNECT BY solution runs in 11 seconds. END EDIT
So, here's the CONNECT BY query (for a single row):
select ord, article_code, level as tranche,
case when level < ceil(size_1/99) then 99
when level > ceil(size_1/99) then 0
else mod(size_1 - 1, 99) + 1
end as size_1_chunk,
case when level < ceil(size_2/99) then 99
when level > ceil(size_2/99) then 0
else mod(size_2- 1, 99) + 1
end as size_2_chunk,
case when level < ceil(size_3/99) then 99
when level > ceil(size_3/99) then 0
else mod(size_3 - 1, 99) + 1
end as size_3_chunk
from qty_split
connect by level <= ceil(greatest(size_1, size_2, size_3)/99)
order by ord, article_code, tranche
;
ORD ARTICLE_CODE TRANCHE SIZE_1_CHUNK SIZE_2_CHUNK SIZE_3_CHUNK
---- ------------ ------- ------------ ------------ ------------
a001 1111 1 99 99 85
a001 1111 2 24 66 0
Note a few changes I made: I changed order to ord (since order is a reserved keyword, which will not work as a column name); I changed the column names in the output to size_1_chunk etc., and I added a column tranche to keep track of each tranche or slice in the process; not sure if you will need to refer to this column in further processing.
Using CASE and UNION
Note that I changed name of column order to ordern because order is a reserved word.
select ordern, article_code,
case when size_1 > 99 then 99 else size_1 end as size_1,
case when size_2 > 99 then 99 else size_2 end as size_2,
case when size_3 > 99 then 99 else size_3 end as size_3
from qty_split
union
select ordern, article_code,
case when size_1 > 99 then (size_1 - 99) else 0 end as size_1,
case when size_2 > 99 then (size_2 - 99) else 0 end as size_2,
case when size_3 > 99 then (size_3 - 99) else 0 end as size_3
from qty_split
