I have a tree structure as below.
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