By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE "Order" (
"id" NUMBER(10) PRIMARY KEY,
"order_number" VARCHAR2(255),
"external_id" NUMBER(10),
"version" NUMBER(10),
"description" VARCHAR2(255),
"create_ts" TIMESTAMP
);
CREATE TABLE "Partner" (
"id" NUMBER(10) PRIMARY KEY,
"order_id" NUMBER(10),
"partner_code" VARCHAR2(255),
"partner_name" VARCHAR2(255),
"identifier" VARCHAR2(255),
"type" VARCHAR2(255),
"value" NUMBER(10),
FOREIGN KEY ("order_id") REFERENCES "Order"("id")
);
CREATE TABLE "Fees" (
"id" NUMBER PRIMARY KEY,
"order_id" NUMBER,
"identifier" VARCHAR2(255),
"type" VARCHAR2(255),
"value" NUMBER,
FOREIGN KEY ("order_id") REFERENCES "Order"("id")
);
CREATE TABLE "order_attributes" (
"id" NUMBER PRIMARY KEY,
"order_number" VARCHAR2(255),
"external_id" NUMBER,
"partner_code" VARCHAR2(255),
"identifier" VARCHAR2(255),
"type" VARCHAR2(255),
"value" NUMBER
);
INSERT INTO "Order" ("id", "order_number", "external_id", "version", "description", "create_ts")
VALUES
(1, 'ORD001', 1001, 1, 'External id 1', TIMESTAMP '2024-05-05 10:00:00'),
(2, 'ORD001', 1002, 1, 'External Id 2', TIMESTAMP '2024-05-05 10:00:00'),
(3, 'ORD001', 1001, 2, 'External id 1', TIMESTAMP '2024-05-05 11:00:00'),
(4, 'ORD001', 1002, 2, 'External Id 2', TIMESTAMP '2024-05-05 11:00:00'),
(5, 'ORD001', 1001, 3, 'External id 1', TIMESTAMP '2024-05-05 12:00:00'),
(6, 'ORD001', 1002, 3, 'External Id 2', TIMESTAMP '2024-05-05 12:00:00');
6 rows affected
-- Sample data for Partner table
INSERT INTO "Partner" ("id", "order_id", "partner_code", "partner_name", "identifier", "type", "value")
VALUES
-- For identifier = INTERNAL
(1, 5, 'P001', 'Partner 1', 'INTERNAL', 'A', 150),
(2, 5, 'P001', 'Partner 1', 'INTERNAL', 'B', 170),
(3, 5, 'P001', 'Partner 1', 'INTERNAL', 'C', 101),
(4, 5, 'P001', 'Partner 1', 'INTERNAL', 'D', 105),
(5, 5, 'P001', 'Partner 1', 'INTERNAL', 'E', 155),
-- For identifier = EXTERNAL
(6, 5, 'P001', 'Partner 1', 'EXTERNAL', 'A', 1600),
(7, 5, 'P001', 'Partner 1', 'EXTERNAL', 'B', 1800),
(8, 5, 'P001', 'Partner 1', 'EXTERNAL', 'C', 1100),
(9, 5, 'P001', 'Partner 1', 'EXTERNAL', 'D', 1350),
(10, 5, 'P001', 'Partner 1', 'EXTERNAL', 'E', 1600),
-- For identifier = LOCAL
(11, 5, 'P001', 'Partner 1', 'LOCAL', 'A', 17000),
(12, 5, 'P001', 'Partner 1', 'LOCAL', 'B', 19000),
(13, 5, 'P001', 'Partner 1', 'LOCAL', 'C', 12000),
(14, 5, 'P001', 'Partner 1', 'LOCAL', 'D', 14500),
(15, 5, 'P001', 'Partner 1', 'LOCAL', 'E', 17000),
(16, 6, 'P002', 'Partner 2', 'INTERNAL', 'A', 250),
(17, 6, 'P002', 'Partner 2', 'INTERNAL', 'B', 207),
(18, 6, 'P002', 'Partner 2', 'INTERNAL', 'C', 201),
(19, 6, 'P002', 'Partner 2', 'INTERNAL', 'D', 205),
(20, 6, 'P002', 'Partner 2', 'INTERNAL', 'E', 205),
-- For identifier = EXTERNAL
(21, 6, 'P002', 'Partner 2', 'EXTERNAL', 'A', 2600),
(22, 6, 'P002', 'Partner 2', 'EXTERNAL', 'B', 2800),
(23, 6, 'P002', 'Partner 2', 'EXTERNAL', 'C', 2100),
(24, 6, 'P002', 'Partner 2', 'EXTERNAL', 'D', 2350),
(25, 6, 'P002', 'Partner 2', 'EXTERNAL', 'E', 2600),
-- For identifier = LOCAL
(26, 6, 'P002', 'Partner 2', 'LOCAL', 'A', 27000),
(27, 6, 'P002', 'Partner 2', 'LOCAL', 'B', 29000),
30 rows affected
INSERT INTO "Fees" ("id", "order_id", "identifier", "type", "value")
VALUES
-- For identifier = EXPENSE
(1, 5, 'EXPENSE', 'F1', 50),
(2, 5, 'EXPENSE', 'F2', 75),
(3, 5, 'EXPENSE', 'F3', 10),
(4, 5, 'EXPENSE', 'F4', 12),
-- Fr identifier = BILLED
(5, 5, 'BILLED', 'F1', 60),
(6, 5, 'BILLED', 'F2', 80),
(7, 5, 'BILLED', 'F3', 11),
(8, 5, 'BILLED', 'F4', 13),
-- For identifier = SETTLED
(9, 5, 'SETTLED', 'F1', 70),
(10, 5, 'SETTLED', 'F2', 90),
(11, 5, 'SETTLED', 'F3', 17),
(12, 5, 'SETTLED', 'F4', 14),
(21, 6, 'EXPENSE', 'F1', 850),
(22, 6, 'EXPENSE', 'F2', 875),
(23, 6, 'EXPENSE', 'F3', 810),
(24, 6, 'EXPENSE', 'F4', 812),
-- Fr identifier = BILLED
(25, 6, 'BILLED', 'F1', 860),
(26, 6, 'BILLED', 'F2', 880),
(27, 6, 'BILLED', 'F3', 811),
(28, 6, 'BILLED', 'F4', 813),
-- For identifier = SETTLED
(29, 6, 'SETTLED', 'F1', 870),
(30, 6, 'SETTLED', 'F2', 880),
(31, 6, 'SETTLED', 'F3', 817),
(32, 6, 'SETTLED', 'F4', 814)
24 rows affected
-- Sample data for "order_attributes" table
INSERT INTO "order_attributes" ("id", "order_number", "external_id", "partner_code", "identifier", "type", "value")
VALUES
('1', 'ORD001', '1001', 'P001', 'CALCULATED', 'X', 123),
('2', 'ORD001', '1001', 'P001', 'CALCULATED', 'Y', 234),
('3', 'ORD001', '1001', 'P001', 'CALCULATED', 'Z', 23432),
('4', 'ORD001', '1001', 'P001', 'EDITED', 'X', 4665),
('5', 'ORD001', '1001', 'P001', 'EDITED', 'Y', 4557),
('6', 'ORD001', '1001', 'P001', 'EDITED', 'Z', 567567),
('7', 'ORD001', '1002', 'P001', 'CALCULATED', 'X', 3423),
('8', 'ORD001', '1002', 'P001', 'CALCULATED', 'Y', 434),
('9', 'ORD001', '1002', 'P001', 'CALCULATED', 'Z', 9897),
('10', 'ORD001', '1002', 'P001', 'EDITED', 'X', 6767),
('11', 'ORD001', '1002', 'P001', 'EDITED', 'Y', 678),
('12', 'ORD001', '1002', 'P001', 'EDITED', 'Z', 7887)
12 rows affected
SELECT
"o"."order_number",
"o"."external_id",
"p"."partner_code",
"o"."version",
MAX("p"."partner_name") as "partner_name",
MAX("o"."create_ts") AS "create_ts",
MAX("o"."description") as "description",
MAX(NVL(CASE WHEN "a"."identifier"='CALCULATED' AND "a"."type"='X' THEN "a"."value" END,
(CASE WHEN "p"."identifier"='INTERNAL' AND "p"."type"='A' THEN "p"."value" END))) AS "value_a",
MAX(NVL(CASE WHEN "a"."identifier"='EDITED' AND "a"."type"='X' THEN "a"."value" END,
(CASE WHEN "p"."identifier"='INTERNAL' AND "p"."type"='B' THEN "p"."value" END))) AS "value_b"
FROM
"Order" "o"
LEFT JOIN
"Partner" "p" ON "o"."id" = "p"."order_id"
LEFT JOIN
"Fees" "f" ON "o"."id" = "f"."order_id"
LEFT JOIN
"order_attributes" "a" ON "o"."order_number" = "a"."order_number"
AND "o"."external_id" = "a"."external_id"
AND "p"."partner_code" = "a"."partner_code"
WHERE
("o"."order_number", "o"."external_id", "o"."version", "o"."create_ts") in (
SELECT "o1"."order_number", "o1"."external_id", MAX("o1"."version"), MAX("o1"."create_ts")
FROM "Order" "o1"
GROUP BY "o1"."order_number", "o1"."external_id")
and "o"."order_number"='ORD001'
GROUP BY
"o"."order_number", "o"."external_id", "o"."version", "p"."partner_code";
| order_number | external_id | partner_code | version | partner_name | create_ts | description | value_a | value_b |
|---|---|---|---|---|---|---|---|---|
| ORD001 | 1001 | P001 | 3 | Partner 1 | 05-MAY-24 12.00.00.000000 | External id 1 | 150 | 4665 |
| ORD001 | 1002 | P002 | 3 | Partner 2 | 05-MAY-24 12.00.00.000000 | External Id 2 | 250 | 207 |