By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ORDER_TBL2
(
ORDER_PAY DATE,
ORDER_ID VARCHAR2(10 BYTE),
PRODUCT_ID VARCHAR2(10 BYTE),
QUANTITY NUMBER(5),
PRICE NUMBER(5)
);
Insert into ORDER_TBL2
(ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
Values
(TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD1', 'PROD1', 5, 5);
1 rows affected
Insert into ORDER_TBL2
(ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
Values
(TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD2', 'PROD2', 2, 10);
1 rows affected
Insert into ORDER_TBL2
(ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
Values
(TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD3', 'PROD3', 3, 25);
1 rows affected
WITH
cte (order_id,
product_id,
quantity,
cnt)
AS
(SELECT order_id,
product_id,
1 as quantity,
1 as cnt
FROM order_tbl2
UNION ALL
SELECT a.order_id,
a.product_id,
b.quantity,
b.cnt + 1
FROM order_tbl2 A INNER JOIN cte b ON a.product_id = b.product_id
WHERE b.cnt + 1 < a.quantity)
SELECT order_id, product_id, quantity
FROM cte;
ORA-32044: cycle detected while executing recursive WITH query
WITH
cte (order_id,
product_id,
quantity,
cnt)
AS
(SELECT order_id,
product_id,
1 as quantity,
1 as cnt
FROM order_tbl2
UNION ALL
SELECT a.order_id,
a.product_id,
b.quantity,
b.cnt + 1
FROM order_tbl2 A INNER JOIN cte b
ON a.order_id = b.order_id
AND a.product_id = b.product_id
WHERE b.cnt + 1 < a.quantity)
SELECT order_id, product_id, quantity
FROM cte;
ORA-32044: cycle detected while executing recursive WITH query
WITH
cte (order_id,
product_id,
quantity,
cnt)
AS
(SELECT order_id,
product_id,
quantity,
1 as cnt
FROM order_tbl2
UNION ALL
SELECT b.order_id,
b.product_id,
b.quantity,
b.cnt + 1
FROM cte b
WHERE b.cnt + 1 < b.quantity)
SELECT order_id, product_id, 1 as quantity
FROM cte;
| ORDER_ID | PRODUCT_ID | QUANTITY |
|---|---|---|
| ORD1 | PROD1 | 1 |
| ORD2 | PROD2 | 1 |
| ORD3 | PROD3 | 1 |
| ORD1 | PROD1 | 1 |
| ORD3 | PROD3 | 1 |
| ORD1 | PROD1 | 1 |
| ORD1 | PROD1 | 1 |
WITH
cte (order_id,
product_id,
real_quantity,
quantity,
cnt)
AS
(SELECT order_id,
product_id,
quantity as real_quantity,
1 as quantity,
1 as cnt
FROM order_tbl2
UNION ALL
SELECT b.order_id,
b.product_id,
b.real_quantity,
b.quantity,
b.cnt + 1
FROM cte b
WHERE b.cnt + 1 < b.real_quantity)
SELECT order_id, product_id, quantity
FROM cte;
| ORDER_ID | PRODUCT_ID | QUANTITY |
|---|---|---|
| ORD1 | PROD1 | 1 |
| ORD2 | PROD2 | 1 |
| ORD3 | PROD3 | 1 |
| ORD1 | PROD1 | 1 |
| ORD3 | PROD3 | 1 |
| ORD1 | PROD1 | 1 |
| ORD1 | PROD1 | 1 |