Home > database >  Passing down cumulative value in recursive cursor in Oracle PLSQL
Passing down cumulative value in recursive cursor in Oracle PLSQL

Time:07-12

I have a tree structure as below.

Tree Structure

The requirement is to accumulate the Leadtimes of each parent and pass to child.

I currently have below query;

SELECT LEVEL AS struct_level
   ,t.part_no AS parent_part
   ,t.component_part AS comp_part_no
   ,NVL(t.lead_time ,0) AS comp_part_lead_time
   ,NVL(t.lead_time ,0)   NVL(PRIOR t.lead_time ,0) AS cumul_lead_time
FROM   ifsapp.prod_structure_cfv t
START  WITH (t.part_no) IN 'PART A'
CONNECT BY PRIOR (t.component_part) = (t.part_no)
ORDER  SIBLINGS BY t.component_part;

This will give me a data set like below. my data set

But the issue is, the lead time should be calculated from the whole branch. I.e. the correct data set should be, correct data set

As per my understanding, I can only refer PRIOR record values and/or master record (PART A) data via ROOT.

Is there a way to accomplish this please? Maybe using a recursive function?

I tried to reuse the recursive function mentioned in this post, but couldn't get desired results.

CodePudding user response:

You want to use a recursive sub-query factoring clause:

WITH rsqfc (part_no, component_part, lead_time, cumul_lead_time) AS (
  SELECT part_no,
         component_part,
         COALESCE(lead_time, 0),
         COALESCE(lead_time, 0)
  FROM   ifsapp.prod_structure_cfv
  WHERE  part_no = 'PART A'
UNION ALL
  SELECT p.part_no,
         p.component_part,
         COALESCE(p.lead_time, 0),
         r.cumul_lead_time   COALESCE(p.lead_time, 0)
  FROM   rsqfc r
         INNER JOIN ifsapp.prod_structure_cfv p
         ON (r.component_part = p.part_no)
)
SEARCH DEPTH FIRST BY component_part SET order_rn
SELECT part_no,
       component_part,
       lead_time,
       cumul_lead_time
FROM   rsqfc;

Which, for the sample data:

CREATE TABLE prod_structure_cfv (part_no, component_part, lead_time) AS
SELECT 'PART A', 'PART B', 0 FROM DUAL UNION ALL
SELECT 'PART A', 'PART C', 0 FROM DUAL UNION ALL
SELECT 'PART B', 'PART D', 1 FROM DUAL UNION ALL
SELECT 'PART B', 'PART E', 1 FROM DUAL UNION ALL
SELECT 'PART B', 'PART F', 1 FROM DUAL UNION ALL
SELECT 'PART C', 'PART G', 0 FROM DUAL UNION ALL
SELECT 'PART C', 'PART H', 0 FROM DUAL UNION ALL
SELECT 'PART D', 'PART P', 0 FROM DUAL UNION ALL
SELECT 'PART D', 'PART Q', 0 FROM DUAL UNION ALL
SELECT 'PART E', NULL,     2 FROM DUAL UNION ALL
SELECT 'PART F', 'PART R', 1 FROM DUAL UNION ALL
SELECT 'PART G', 'PART X', 3 FROM DUAL UNION ALL
SELECT 'PART G', 'PART Y', 3 FROM DUAL UNION ALL
SELECT 'PART H', NULL,     2 FROM DUAL UNION ALL
SELECT 'PART P', NULL,     2 FROM DUAL UNION ALL
SELECT 'PART Q', NULL,     1 FROM DUAL UNION ALL
SELECT 'PART R', 'PART S', 0 FROM DUAL UNION ALL
SELECT 'PART S', NULL,     3 FROM DUAL UNION ALL
SELECT 'PART X', NULL,     1 FROM DUAL UNION ALL
SELECT 'PART Y', NULL,     2 FROM DUAL;

Outputs:

PART_NO COMPONENT_PART LEAD_TIME CUMUL_LEAD_TIME
PART A PART B 0 0
PART B PART D 1 1
PART D PART P 0 1
PART P null 2 3
PART D PART Q 0 1
PART Q null 1 2
PART B PART E 1 1
PART E null 2 3
PART B PART F 1 1
PART F PART R 1 2
PART R PART S 0 2
PART S null 3 5
PART A PART C 0 0
PART C PART G 0 0
PART G PART X 3 3
PART X null 1 4
PART G PART Y 3 3
PART Y null 2 5
PART C PART H 0 0
PART H null 2 2

db<>fiddle here

  • Related