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 ISLEAF1000 1 100 300 8889 1 1 >300 01000 1 300 400 1701 1 2 >300>400 11000 1 300 500 970 1 2 >300>500 11000 1 300 600 7 2 2 >300>600 11000 1 300 700 1160 1 2 >300>700 11000 1 300 800 580 1 2 >300>800 11000 1 300 900 96 1 2 >300>900 11000 1 300 1000 350 1 2 >300>1000 11000 1 300 1100 59 4 2 >300>1100 01000 1 1100 1200 28 1 3 >300>1100>1200 11000 1 1100 1300 1 1 3 >300>1100>1300 11000 1 1100 1400 3 1 3 >300>1100>1400 11000 1 1100 1500 4 1 3 >300>1100>1500 11000 1 1100 1600 22 1 3 >300>1100>1600 1I 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_costFROM 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.