Oracle SQL/PLSQL: Hierarchical recursive query

Question

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.

SQL Fiddle here

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.

https://stackoverflow.com/questions/49939839/recursive-subquerying-with-sorting _ did something similar here .. in as much as I was able to use depth field to order things. You could use that column as a basis for aggregation. — JGFMK, Feb 04 '20 at 18:01
Please make sure you create table with borders instead. It's difficult to read — django-unchained, Feb 04 '20 at 18:13

score 2 · Accepted Answer · answered Feb 04 '20 at 18:57

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.

The `UNIT_COST` on non-leaf nodes is the cost currently saved in the system. In relation to this question we can ignore those values but my users want to compare those values to the rolled-up value we are trying to calculate here. Its a sense-check exercise in a way but there are other factors involved too. — pwlm, Feb 04 '20 at 19:08
@pwlm Thanks. In that case, I think the query above should give you what you are looking for? — Matthew McPeak, Feb 04 '20 at 19:21
Works a dream, thanks! Quick question: How did you format the sample data so nicely on StackOverflow? — pwlm, Feb 05 '20 at 17:23
I export the data from SQL\*Developer to the clipboard and then paste it into https://ozh.github.io/ascii-tables/. — Matthew McPeak, Feb 05 '20 at 17:32

Oracle SQL/PLSQL: Hierarchical recursive query

1 Answers1

NOTE #1