I have asked very similar questions before but still haven’t achieved the correct result… third times the charm but I now have a stable parent-child relationship to work with.
Every row in the multi-level data set has a cost. For a specific row I need to find the lowest level and roll-up the cost. Then my users will compare the rolled up cost to the row cost and troubleshoot when/if they find differences.
Data Sample:
MODEL_NO MODEL_REV P_SEQ_NO SEQ_NO UNIT_COST QTY LEVEL TREE_PATH ISLEAF 1000 1 100 300 8889 1 1 >300 0 1000 1 300 400 1701 1 2 >300>400 1 1000 1 300 500 970 1 2 >300>500 1 1000 1 300 600 7 2 2 >300>600 1 1000 1 300 700 1160 1 2 >300>700 1 1000 1 300 800 580 1 2 >300>800 1 1000 1 300 900 96 1 2 >300>900 1 1000 1 300 1000 350 1 2 >300>1000 1 1000 1 300 1100 59 4 2 >300>1100 0 1000 1 1100 1200 28 1 3 >300>1100>1200 1 1000 1 1100 1300 1 1 3 >300>1100>1300 1 1000 1 1100 1400 3 1 3 >300>1100>1400 1 1000 1 1100 1500 4 1 3 >300>1100>1500 1 1000 1 1100 1600 22 1 3 >300>1100>1600 1
I am looking for a function or SQL statement, based on a specific row in the data set (MODEL_NO, MODEL_REV, P_SEQ_NO, SEQ_NO make a unique key) would return the cost roll-up.
For example:
Based on this record: 1000, 1, 300, 1100,
I would expect a result of: 232.
This is worked out by multiplying the UNIT_COST of each lowest level child by its QTY, adding the sum of each child together, then multiplying the result by the QTY of the parent, repeating the last step until you each the record entered, in this case SEQ_NO = 1100.
Result workings: (22*1 + 4*1 + 3*1 + 1*1 + 28*1) *4
This example is only 1 level deep, my data can go as deep as 6/7 levels.
Advertisement
Answer
I think you need to do this in two steps.
In step 1, you create the hierarchical query results and explode the quantities down to each level. For example, is you have a path like 100->210->320 and the qty values for those are 2, 3, and 5 respectively, then you have exploded quantities 2, 6, and 30, respectively.
Then, having that, you can multiply those exploded quantities by the unit code to get how much that row will contribute to the overall cost of all its parent levels. You can then summarize those costs with a scalar subquery.
Here is the query:
WITH c ( model_no, model_rev, lvl, p_seq_no, seq_no, unit_cost, qty, exploded_qty, pth) AS
( SELECT model_no, model_rev, 1 lvl, p_seq_no, seq_no, unit_cost, qty, qty exploded_qty, to_char(seq_no) pth
FROM prod_cost_model_struct_tab
WHERE model_no = 1000
AND model_rev = 1
AND seq_no = 300
UNION ALL
SELECT child.model_no, child.model_rev, parent.lvl+1, child.p_seq_no, child.seq_no, child.unit_cost, child.qty, parent.exploded_qty * child.qty exploded_qty, parent.pth || '>' || child.seq_no pth
FROM prod_cost_model_struct_tab child INNER JOIN c parent
ON child.model_no = parent.model_no
AND child.model_rev = parent.model_rev
AND child.p_seq_no = parent.seq_no )
search depth first by seq_no set ord,
exploded_qtys AS (
SELECT lvl,
c.p_seq_no,
c.seq_no,
c.unit_cost,
c.qty,
c.exploded_qty,
c.pth,
c.ord,
case when (lvl - lead(lvl) over (order by ord)) < 0 then
-- It is not a leaf, level contributes nothing (assumption: see question)
-- QUESTION: What does the unit_cost represent on non-leaf rows?
0 ELSE c.exploded_qty * c.unit_cost END level_cost_contribution
FROM c )
SELECT e.*, ( SELECT nvl(sum(level_cost_contribution),0) FROM exploded_qtys e2 WHERE e2.pth LIKE e.pth || '>%' ) + level_cost_contribution aggregate_cost
FROM exploded_qtys e
+-----+----------+--------+-----------+-----+--------------+---------------+-----+-------------------------+----------------+ | LVL | P_SEQ_NO | SEQ_NO | UNIT_COST | QTY | EXPLODED_QTY | PTH | ORD | LEVEL_COST_CONTRIBUTION | AGGREGATE_COST | +-----+----------+--------+-----------+-----+--------------+---------------+-----+-------------------------+----------------+ | 1 | 100 | 300 | 8889 | 1 | 1 | 300 | 1 | 0 | 8783 | | 2 | 300 | 400 | 1701 | 1 | 1 | 300>400 | 2 | 1701 | 1701 | | 2 | 300 | 500 | 970 | 1 | 1 | 300>500 | 3 | 970 | 970 | | 2 | 300 | 600 | 7 | 2 | 2 | 300>600 | 4 | 14 | 14 | | 2 | 300 | 700 | 1160 | 1 | 1 | 300>700 | 5 | 1160 | 1160 | | 2 | 300 | 800 | 580 | 1 | 1 | 300>800 | 6 | 580 | 580 | | 2 | 300 | 900 | 96 | 1 | 1 | 300>900 | 7 | 96 | 96 | | 2 | 300 | 1000 | 350 | 1 | 1 | 300>1000 | 8 | 350 | 350 | | 2 | 300 | 1100 | 59 | 4 | 4 | 300>1100 | 9 | 0 | 232 | | 3 | 1100 | 1200 | 28 | 1 | 4 | 300>1100>1200 | 10 | 112 | 112 | | 3 | 1100 | 1300 | 1 | 1 | 4 | 300>1100>1300 | 11 | 4 | 4 | | 3 | 1100 | 1400 | 3 | 1 | 4 | 300>1100>1400 | 12 | 12 | 12 | | 3 | 1100 | 1500 | 4 | 1 | 4 | 300>1100>1500 | 13 | 16 | 16 | | 3 | 1100 | 1600 | 22 | 1 | 4 | 300>1100>1600 | 14 | 88 | 88 | | 2 | 300 | 1700 | 219 | 1 | 1 | 300>1700 | 15 | 0 | 218 | | 3 | 1700 | 1800 | 10 | 1 | 1 | 300>1700>1800 | 16 | 10 | 10 | | 3 | 1700 | 1900 | 1 | 4 | 4 | 300>1700>1900 | 17 | 4 | 4 | | 3 | 1700 | 2000 | 200 | 1 | 1 | 300>1700>2000 | 18 | 200 | 200 | | 3 | 1700 | 2100 | 4 | 1 | 1 | 300>1700>2100 | 19 | 4 | 4 | | 2 | 300 | 2200 | 1160 | 1 | 1 | 300>2200 | 20 | 1160 | 1160 | | 2 | 300 | 2300 | 1 | 8 | 8 | 300>2300 | 21 | 8 | 8 | | 2 | 300 | 2400 | 1 | 2 | 2 | 300>2400 | 22 | 2 | 2 | | 2 | 300 | 2500 | 1 | 1 | 1 | 300>2500 | 23 | 1 | 1 | | 2 | 300 | 2600 | 2 | 1 | 1 | 300>2600 | 24 | 2 | 2 | | 2 | 300 | 2700 | 4 | 2 | 2 | 300>2700 | 25 | 8 | 8 | | 2 | 300 | 2800 | 103 | 1 | 1 | 300>2800 | 26 | 103 | 103 | | 2 | 300 | 2900 | 95 | 1 | 1 | 300>2900 | 27 | 95 | 95 | | 2 | 300 | 3000 | 0 | 4 | 4 | 300>3000 | 28 | 0 | 0 | | 2 | 300 | 3100 | 2 | 1 | 1 | 300>3100 | 29 | 2 | 2 | | 2 | 300 | 3200 | 0 | 66 | 66 | 300>3200 | 30 | 0 | 0 | | 2 | 300 | 3300 | 0 | 12 | 12 | 300>3300 | 31 | 0 | 0 | | 2 | 300 | 3400 | 0 | 33 | 33 | 300>3400 | 32 | 0 | 0 | | 2 | 300 | 3500 | 0 | 4 | 4 | 300>3500 | 33 | 0 | 0 | | 2 | 300 | 3600 | 0 | 8 | 8 | 300>3600 | 34 | 0 | 0 | | 2 | 300 | 3700 | 0 | 4 | 4 | 300>3700 | 35 | 0 | 0 | | 2 | 300 | 3800 | 0 | 4 | 4 | 300>3800 | 36 | 0 | 0 | | 2 | 300 | 3900 | 0 | 5 | 5 | 300>3900 | 37 | 0 | 0 | | 2 | 300 | 4000 | 0 | 84 | 84 | 300>4000 | 38 | 0 | 0 | | 2 | 300 | 4100 | 0 | 32 | 32 | 300>4100 | 39 | 0 | 0 | | 2 | 300 | 4200 | 0 | 32 | 32 | 300>4200 | 40 | 0 | 0 | | 2 | 300 | 4300 | 1 | 12 | 12 | 300>4300 | 41 | 12 | 12 | | 2 | 300 | 4400 | 2 | 3 | 3 | 300>4400 | 42 | 6 | 6 | | 2 | 300 | 4500 | 145 | 1 | 1 | 300>4500 | 43 | 145 | 145 | | 2 | 300 | 4600 | 48 | 1 | 1 | 300>4600 | 44 | 48 | 48 | | 2 | 300 | 4700 | 2 | 2 | 2 | 300>4700 | 45 | 4 | 4 | | 2 | 300 | 4800 | 1846 | 1 | 1 | 300>4800 | 46 | 0 | 1832 | | 3 | 4800 | 4900 | 169 | 3 | 3 | 300>4800>4900 | 47 | 507 | 507 | | 3 | 4800 | 5000 | 30 | 1 | 1 | 300>4800>5000 | 48 | 30 | 30 | | 3 | 4800 | 5100 | 17 | 1 | 1 | 300>4800>5100 | 49 | 17 | 17 | | 3 | 4800 | 5200 | 169 | 3 | 3 | 300>4800>5200 | 50 | 507 | 507 | | 3 | 4800 | 5300 | 5 | 1 | 1 | 300>4800>5300 | 51 | 5 | 5 | | 3 | 4800 | 5400 | 320 | 1 | 1 | 300>4800>5400 | 52 | 320 | 320 | | 3 | 4800 | 5500 | 25 | 2 | 2 | 300>4800>5500 | 53 | 50 | 50 | | 3 | 4800 | 5600 | 5 | 4 | 4 | 300>4800>5600 | 54 | 20 | 20 | | 3 | 4800 | 5700 | 18 | 3 | 3 | 300>4800>5700 | 55 | 54 | 54 | | 3 | 4800 | 5800 | 139 | 2 | 2 | 300>4800>5800 | 56 | 278 | 278 | | 3 | 4800 | 5900 | 8 | 2 | 2 | 300>4800>5900 | 57 | 16 | 16 | | 3 | 4800 | 6000 | 9 | 1 | 1 | 300>4800>6000 | 58 | 9 | 9 | | 3 | 4800 | 6100 | 19 | 1 | 1 | 300>4800>6100 | 59 | 19 | 19 | | 2 | 300 | 6200 | 25 | 1 | 1 | 300>6200 | 60 | 25 | 25 | | 2 | 300 | 6300 | 9 | 1 | 1 | 300>6300 | 61 | 9 | 9 | +-----+----------+--------+-----------+-----+--------------+---------------+-----+-------------------------+----------------+
NOTE #1
You did not indicate what the unit_cost represents for rows that are not leaf nodes in the hierarchy. Do they add to the cost of their children somehow? That will change the answer.