Oracle SQL "connect by prior" - sum and product of values back up the tree

user_1176987 0
Viewed 133

The following SQL:

WITH vehicle_build_structure (part_no, parent_part, qty_per_assembly) AS (
   SELECT 'LORRY',    '*',     null FROM dual UNION -- if it makes things easier, you may substitute "null" for "1"
   SELECT 'AXLEA',    'LORRY', 3    FROM dual UNION
   SELECT 'BRAKEPAD', 'AXLEA', 2    FROM dual UNION
   SELECT 'WHEEL',    'AXLEA', 4    FROM dual UNION
   SELECT 'CAR',      '*',     null FROM dual UNION
   SELECT 'AXLEB',    'CAR',   2    FROM dual UNION
   SELECT 'BRAKEPAD', 'AXLEB', 2    FROM dual UNION
   SELECT 'WHEEL',    'AXLEB', 2    FROM dual
), exploded_structure AS (
   SELECT level lev, CONNECT_BY_ROOT s.part_no top_level_part, s.part_no, s.qty_per_assembly,
          (
             SELECT sum (sp.qty_per_assembly)
             FROM   vehicle_build_structure sp
             START WITH sp.part_no = s.part_no
             CONNECT BY prior sp.parent_part = sp.part_no
          ) sum_of_structure,
          (
             SELECT exp(sum(ln(sp.qty_per_assembly)))
             FROM   vehicle_build_structure sp
             START WITH sp.part_no = s.part_no
             CONNECT BY prior sp.parent_part = sp.part_no
          ) product_of_structure
   FROM   vehicle_build_structure s
   START WITH s.parent_part = '*'
   CONNECT BY prior s.part_no = s.parent_part
)
SELECT e.lev, e.top_level_part, e.part_no, e.qty_per_assembly,
       e.sum_of_structure, e.product_of_structure
FROM   exploded_structure e

Produces the following grid:

       LEV TOP_LEVE PART_NO  QTY_PER_ASSEMBLY SUM_OF_STRUCTURE PRODUCT_OF_STRUCTURE
---------- -------- -------- ---------------- ---------------- --------------------
         1 CAR      CAR
         2 CAR      AXLEB                   2                2                    2
         3 CAR      BRAKEPAD                2                9                   24
         3 CAR      WHEEL                   2               11                   48
         1 LORRY    LORRY
         2 LORRY    AXLEA                   3                3                    3
         3 LORRY    BRAKEPAD                2                9                   24
         3 LORRY    WHEEL                   4               11                   48

But I need it to produce this grid:

       LEV TOP_LEVE PART_NO  QTY_PER_ASSEMBLY SUM_OF_STRUCTURE PRODUCT_OF_STRUCTURE
---------- -------- -------- ---------------- ---------------- --------------------
         1 CAR      CAR
         2 CAR      AXLEB                   2                2                    2
         3 CAR      BRAKEPAD                2                4                    4
         3 CAR      WHEEL                   2                4                    4
         1 LORRY    LORRY
         2 LORRY    AXLEA                   3                3                    3
         3 LORRY    BRAKEPAD                2                5                    6
         3 LORRY    WHEEL                   4                7                   12

In other words, I need the SQL to tell me that when I build a Car, I need 4 wheels, and that when I build a Lorry I need 12 wheels.

However, because the part WHEEL is part of both the CAR and LORRY structures, the internal connect-by-prior statement is working its way back up both branches, and multiplying the quantities of both structures together (12 * 4 = 48). Obviously when the structure gets very complex this ends up giving me a wildly incorrect answer.

I can't work out how to limit that internal connect-by-prior statement to only work its way back up the branch from where it came.

Can anyone help?

oracle12c oracle sql
2 Answers

I think you should try do something about the duplicate values in column part_no. Expanding the tree beyond 'brakepad' and 'wheel' will be difficult with your table.

That being said, the query you are looking for should look like it (not tested on Oracle, please correct any typo):

WITH vehicle_build_structure (part_no, parent_part, qty_per_assembly) AS (
   SELECT 'LORRY',    '*',     null FROM dual UNION
   SELECT 'AXLEA',    'LORRY', 3    FROM dual UNION
   SELECT 'BRAKEPAD', 'AXLEA', 2    FROM dual UNION
   SELECT 'WHEEL',    'AXLEA', 4    FROM dual UNION
   SELECT 'CAR',      '*',     null FROM dual UNION
   SELECT 'AXLEB',    'CAR',   2    FROM dual UNION
   SELECT 'BRAKEPAD', 'AXLEB', 2    FROM dual UNION
   SELECT 'WHEEL',    'AXLEB', 2    FROM dual
), rec_build_structure (lv, top_part, part_no, qty_per_assembly, qty_sum, qty_product) AS (
    SELECT 1, part_no, part_no, 1, 0, 1
    FROM   vehicle_build_structure
    WHERE  parent_part = '*'
    UNION ALL
    SELECT 1 + r.lv, r.top_part, v.part_no, v.qty_per_assembly,
           v.qty_per_assembly + r.qty_sum,
           v.qty_per_assembly * r.qty_product
    FROM   vehicle_build_structure v
           INNER JOIN rec_build_structure r
                   ON v.parent_part = r.part_no
)
SELECT rbs.*
FROM   rec_build_structure rbs
ORDER BY rbs.top_part, rbs.lv, rbs.part_no
user_106005840

I feel that this is jumping through hoops a little bit, but it does work. Basically, you write out your calculation as you traverse down the tree, and then do an eval() on the calculation that you've defined.

It works, but it still feels to me that there should be a construct in the language that does this a little bit more eloquently.

WITH vehicle_build_structure (part_no, parent_part, qty_per_assembly) AS (
   SELECT 'LORRY',    '*',     null FROM dual UNION
   SELECT 'AXLEA',    'LORRY', 3    FROM dual UNION
   SELECT 'BRAKEPAD', 'AXLEA', 2    FROM dual UNION
   SELECT 'WHEEL',    'AXLEA', 4    FROM dual UNION
   SELECT 'CAR',      '*',     null FROM dual UNION
   SELECT 'AXLEB',    'CAR',   2    FROM dual UNION
   SELECT 'BRAKEPAD', 'AXLEB', 2    FROM dual UNION
   SELECT 'WHEEL',    'AXLEB', 2    FROM dual
), exploded_structure AS (
   SELECT level lev, CONNECT_BY_ROOT s.part_no top_level_part, s.part_no, s.qty_per_assembly,
          nvl (ltrim (sys_connect_by_path (s.qty_per_assembly, '*'), '*'), 0) my_calculation
   FROM   vehicle_build_structure s
   START WITH s.parent_part = '*'
   CONNECT BY prior s.part_no = s.parent_part
)
SELECT e.lev, e.top_level_part, e.part_no, e.qty_per_assembly, e.my_calculation,
       xmlquery (e.my_calculation returning content).getnumberval() my_result
FROM   exploded_structure e

Produces:

       LEV TOP_LEVE PART_NO  QTY_PER_ASSEMBLY MY_CALCULATION                  MY_RESULT
---------- -------- -------- ---------------- ------------------------------ ----------
         1 CAR      CAR                       0                                       0
         2 CAR      AXLEB                   2 2                                       2
         3 CAR      BRAKEPAD                2 2*2                                     4
         3 CAR      WHEEL                   2 2*2                                     4
         1 LORRY    LORRY                     0                                       0
         2 LORRY    AXLEA                   3 3                                       3
         3 LORRY    BRAKEPAD                2 3*2                                     6
         3 LORRY    WHEEL                   4 3*4                                    12
user_11769870
Related