Recursive SQL Calculation - Recursive Calculation Cannot Be Performed

Question

I am working on an Oracle database instance trying to calculate a recursive bill of materials.

My dataset looks like this (there are additional features I don't really need):

| Root_Part_No | Sub_Part_No | Sub_Part_Quant |
|--------------|-------------|----------------|
| 132EER       | 122FYY      | 4              |
| 132EER       | 766WWW      | 2              |
| 132EER       | 001EWW      | 1              |
| 132EER       | 472WQA      | 1              |
| 132EER       | 632OIR      | 1              |
| 132EER       | 874RTG      | 1              |
| 132EER       | 888III      | 10             |
| 132EER       | 235CVV      | 5              |

I can calculate the first-level BOM well using the following query:

SELECT 
    Root_Part_No, 
    TRIM(Sub_Part_No) AS "Part", 
    Sub_Part_Qty

FROM 
    TblBOM 

WHERE
    Root_Part_No LIKE "132EE%" 
    AND isEmptyInd != 'Yes'

ORDER BY Root_Part_No, Sub_Part_Qty;

However, what I need to accomplish is the ability to subsequently calculate the BOM for each of the Sub_Part_No that are returned for the item I enter, to ultimately get a table that looks like:

| Root_Part_No | Sub_Part_No | Sub_Part_Quant |
|--------------|-------------|----------------|
| 132EER       | 122FYY      | 4              |
| 132EER       | 766WWW      | 2              |
| 132EER       | 001EWW      | 1              |
| 132EER       | 472WQA      | 1              |
| 132EER       | 632OIR      | 1              |
| 132EER       | 874RTG      | 1              |
| 132EER       | 888III      | 10             |
| 132EER       | 235CVV      | 5              |
| 122FYY       | 849ZXA      | 2              |
| 122FYY       | 990JUI      | 10             |
| 122FYY       | 211EEW      | 5              |
| 122FYY       | 211VVV      | 2              |
| 766WWW       | 001EWW      | 10             |
| 766WWW       | 176LKJ      | 2              |
...etc

I have tried the following two resources:

• From IBM (This is what I am currently following)
• Recursive Query for Bill Of Materials
• Connect By Prior

I get confused on the join section in the second part of the UNION. Any assistance on a path forward is appreciated.

WITH RPL AS ( SELECT Root_Part_No, TRIM(Sub_Part_No) AS "Part", Sub_Part_Qty FROM TblBOM
WHERE Root_Part_No LIKE "132EE%" AND isEmptyInd != 'Yes'

UNION ALL

SELECT Root_Part_No, TRIM(Sub_Part_No) AS "Part", Sub_Part_Qty
FROM TblBOM
LEFT JOIN -- I am confusion

    )

  SELECT DISTINCT -- I am also confusion

Answer 1

A simple recursive query to get what you want is:

with
i (root_part_no, sub_part_no, sub_part_quant, lvl) as (
  select 
    root_part_no, sub_part_no, sub_part_quant, 1
  from tblbom where root_part_no like '132EE%' and isemptyind <> 'Yes'
  union all -- anchor member above; recursive member below
  select
    p.root_part_no, p.sub_part_no, p.sub_part_quant, i.lvl + 1
  from i
  join tblbom p on p.root_part_no = i.sub_part_no
)
select * 
from i
order by lvl, root_part_no, sub_part_no

Pay attention to the UNION ALL clause. It separates the anchor query -- run only once -- from the recursive query that is run multiple times for every new row that results from it, until it produces no new rows anymore. This way this query can walk multiple levels, not just 2 of them.

Edit:

I tested the query above with the following (made up) data, and I added the LVL column to show the recursive level:

create table tblbom (
  root_part_no varchar2(10),
  sub_part_no varchar2(10),
  sub_part_quant number(6),
  isemptyind varchar2(10) default 'No'
);

insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('132EER', '122FYY', 1);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('132EER', '766WWW', 2);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('122FYY', '849ZXA', 3);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('766WWW', '111111', 4);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('849ZXA', null, 5);
insert into tblbom (root_part_no, sub_part_no, sub_part_quant) values ('111111', null, 6);

The Result (including level) is:

ROOT_PART_NO  SUB_PART_NO  SUB_PART_QUANT  LVL
------------  -----------  --------------  ---
132EER        122FYY       1               1  
132EER        766WWW       2               1  
122FYY        849ZXA       3               2  
766WWW        111111       4               2  
111111        <null>       6               3  
849ZXA        <null>       5               3