FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE data (id, amount) AS
  SELECT  22, 11.55 FROM DUAL UNION ALL
  SELECT  33,  5.55 FROM DUAL UNION ALL
  SELECT  44,  3.33 FROM DUAL UNION ALL
  SELECT  55,  2.33 FROM DUAL UNION ALL
  SELECT  66,  1.11 FROM DUAL UNION ALL
  SELECT  77,   .99 FROM DUAL UNION ALL
  SELECT  88, -1.77 FROM DUAL UNION ALL
  SELECT  91,  0.00 FROM DUAL UNION ALL
  SELECT  99, -5.00 FROM DUAL UNION ALL
  SELECT 100,  3.33 FROM DUAL UNION ALL
  SELECT 111,  4.44 FROM DUAL UNION ALL
  SELECT 122,  5.00 FROM DUAL UNION ALL
  SELECT 133,  4.00 FROM DUAL UNION ALL
  SELECT 144,  2.24 FROM DUAL;

SELECT d1.id AS id1,
       d1.amount AS amount1,
       d2.id AS id2,
       d2.amount AS amount2,
       d3.id AS id3,
       d3.amount AS amount3,
       d4.id AS id4,
       d4.amount AS amount4
FROM   data d1
       INNER JOIN data d2
       ON (d1.id < d2.id)
       INNER JOIN data d3
       ON (d2.id < d3.id)
       INNER JOIN data d4
       ON (d3.id < d4.id)
WHERE  d1.amount + d2.amount + d3.amount + d4.amount = 15.68;

SELECT id,
       amount
FROM   (
  SELECT d1.id AS id1,
         d1.amount AS amount1,
         d2.id AS id2,
         d2.amount AS amount2,
         d3.id AS id3,
         d3.amount AS amount3,
         d4.id AS id4,
         d4.amount AS amount4
  FROM   data d1
         INNER JOIN data d2
         ON (d1.id < d2.id)
         INNER JOIN data d3
         ON (d2.id < d3.id)
         INNER JOIN data d4
         ON (d3.id < d4.id)
  WHERE  d1.amount + d2.amount + d3.amount + d4.amount = 15.68
)
UNPIVOT (
  (amount, id) FOR type IN (
    (amount1, id1) AS 1,
    (amount2, id2) AS 2,
    (amount3, id3) AS 3,
    (amount4, id4) AS 4
  )
);

WITH hierarchy (id1, amount1, id2, amount2, id3, amount3, id4, amount4, lvl, total) AS (
  SELECT id, amount,
         CAST(NULL AS NUMBER), CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER), CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER), CAST(NULL AS NUMBER),
         1, amount
  FROM   data
 UNION ALL
  SELECT h.id1,
         h.amount1,
         CASE lvl + 1 WHEN 2 THEN d.id     ELSE h.id2 END,
         CASE lvl + 1 WHEN 2 THEN d.amount ELSE h.amount2 END,
         CASE lvl + 1 WHEN 3 THEN d.id     ELSE h.id3 END,
         CASE lvl + 1 WHEN 3 THEN d.amount ELSE h.amount3 END,
         CASE lvl + 1 WHEN 4 THEN d.id     ELSE h.id4 END,
         CASE lvl + 1 WHEN 4 THEN d.amount ELSE h.amount4 END,
         lvl + 1, amount + total
  FROM   data d
         INNER JOIN hierarchy h
         ON COALESCE(h.id3, h.id2, h.id1) < d.id AND h.lvl < 4
)
SELECT id, amount
FROM   (
  SELECT *
  FROM   hierarchy
  WHERE  lvl = 4
  AND    total = 15.68
)
UNPIVOT (
  (amount, id) FOR type IN (
    (amount1, id1) AS 1,
    (amount2, id2) AS 2,
    (amount3, id3) AS 3,
    (amount4, id4) AS 4
  )
);

ID1	AMOUNT1	ID2	AMOUNT2	ID3	AMOUNT3	ID4	AMOUNT4
111	4.44	122	5	133	4	144	2.24

ID	AMOUNT
111	4.44
122	5
133	4
144	2.24

ID	AMOUNT
111	4.44
122	5
133	4
144	2.24