How to get all levels data using single SQL query for bill of material

Question

I have a lot of bill of material Items and it contains their raw material and semi-finished goods. The semi-finished goods have their own bill of material with raw material and further semi-finished goods and so on.

I want to create a SQL query by which I want to get the details of the bill of material up to the last level. I will put a WHERE clause for the parent bill of material and should get the details up to the last level. It is not limited to 7 levels, some items may even goto 10 or 15 levels.

Below is an example of the hierarchy for example Item 'A'.

Below is the structure of the table and data in it.

Answer 1

Use a hierarchical query and filter to only get the leaf nodes.

Oracle Setup:

CREATE TABLE table_name ( bom_code, rm_code, qty ) AS
  SELECT 'A', 'B', 1 FROM DUAL UNION ALL
  SELECT 'A', 'C', 2 FROM DUAL UNION ALL
  SELECT 'A', 'D', 5 FROM DUAL UNION ALL
  SELECT 'A', 'E', 3 FROM DUAL UNION ALL
  SELECT 'A', 'F', 8 FROM DUAL UNION ALL
  SELECT 'D', 'G', 2 FROM DUAL UNION ALL
  SELECT 'D', 'H', 1 FROM DUAL UNION ALL
  SELECT 'D', 'I', 1 FROM DUAL UNION ALL
  SELECT 'D', 'J', 1 FROM DUAL UNION ALL
  SELECT 'F', 'K', 1 FROM DUAL UNION ALL
  SELECT 'F', 'L', 1 FROM DUAL UNION ALL
  SELECT 'G', 'Z', 1 FROM DUAL UNION ALL
  SELECT 'G', 'AA', 3 FROM DUAL UNION ALL
  SELECT 'I', 'M', 4 FROM DUAL UNION ALL
  SELECT 'I', 'N', 9 FROM DUAL UNION ALL
  SELECT 'I', 'O', 7 FROM DUAL UNION ALL
  SELECT 'N', 'P', 6 FROM DUAL UNION ALL
  SELECT 'N', 'Q', 12 FROM DUAL UNION ALL
  SELECT 'N', 'R', 4 FROM DUAL UNION ALL
  SELECT 'N', 'S', 9 FROM DUAL UNION ALL
  SELECT 'S', 'T', 3 FROM DUAL UNION ALL
  SELECT 'S', 'U', 2 FROM DUAL UNION ALL
  SELECT 'T', 'V', 1 FROM DUAL UNION ALL
  SELECT 'T', 'W', 3 FROM DUAL UNION ALL
  SELECT 'U', 'X', 5 FROM DUAL UNION ALL
  SELECT 'U', 'Y', 8 FROM DUAL;

Query:

SELECT t.*,
       SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,
       LEVEL
FROM   table_name t
WHERE  CONNECT_BY_ISLEAF = 1
START WITH bom_code = 'A'
CONNECT BY PRIOR rm_code = bom_code;

Output:

BOM_CODE | RM_CODE | QTY | PATH           | LEVEL
:------- | :------ | --: | :------------- | ----:
A        | B       |   1 | -A-B           |     1
A        | C       |   2 | -A-C           |     1
G        | AA      |   3 | -A-D-G-AA      |     3
G        | Z       |   1 | -A-D-G-Z       |     3
D        | H       |   1 | -A-D-H         |     2
I        | M       |   4 | -A-D-I-M       |     3
N        | P       |   6 | -A-D-I-N-P     |     4
N        | Q       |  12 | -A-D-I-N-Q     |     4
N        | R       |   4 | -A-D-I-N-R     |     4
T        | V       |   1 | -A-D-I-N-S-T-V |     6
T        | W       |   3 | -A-D-I-N-S-T-W |     6
U        | X       |   5 | -A-D-I-N-S-U-X |     6
U        | Y       |   8 | -A-D-I-N-S-U-Y |     6
I        | O       |   7 | -A-D-I-O       |     3
D        | J       |   1 | -A-D-J         |     2
A        | E       |   3 | -A-E           |     1
F        | K       |   1 | -A-F-K         |     2
F        | L       |   1 | -A-F-L         |     2

Query 2:

If you want the total quantity along the path then use a correlated sub-query to traverse the hierarchy in the reverse direction:

SELECT t.*,
       SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,
       LEVEL,
       ( SELECT SUM( qty )
         FROM   table_name s
         START WITH t.rm_code = s.rm_code
         CONNECT BY PRIOR bom_code = rm_code ) AS total_qty
FROM   table_name t
WHERE  CONNECT_BY_ISLEAF = 1
START WITH bom_code = 'A'
CONNECT BY PRIOR rm_code = bom_code;

Output:

BOM_CODE | RM_CODE | QTY | PATH           | LEVEL | TOTAL_QTY
:------- | :------ | --: | :------------- | ----: | --------:
A        | B       |   1 | -A-B           |     1 |         1
A        | C       |   2 | -A-C           |     1 |         2
G        | AA      |   3 | -A-D-G-AA      |     3 |        10
G        | Z       |   1 | -A-D-G-Z       |     3 |         8
D        | H       |   1 | -A-D-H         |     2 |         6
I        | M       |   4 | -A-D-I-M       |     3 |        10
N        | P       |   6 | -A-D-I-N-P     |     4 |        21
N        | Q       |  12 | -A-D-I-N-Q     |     4 |        27
N        | R       |   4 | -A-D-I-N-R     |     4 |        19
T        | V       |   1 | -A-D-I-N-S-T-V |     6 |        28
T        | W       |   3 | -A-D-I-N-S-T-W |     6 |        30
U        | X       |   5 | -A-D-I-N-S-U-X |     6 |        31
U        | Y       |   8 | -A-D-I-N-S-U-Y |     6 |        34
I        | O       |   7 | -A-D-I-O       |     3 |        13
D        | J       |   1 | -A-D-J         |     2 |         6
A        | E       |   3 | -A-E           |     1 |         3
F        | K       |   1 | -A-F-K         |     2 |         9
F        | L       |   1 | -A-F-L         |     2 |         9

db<>fiddle here