Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE PARTLIST (PART VARCHAR(16), 
                      SUBPART VARCHAR(16), 
                      QUANTITY INT, 
                      Line_number INT);
                  
INSERT INTO PARTLIST VALUES 
('PRODUCT1','PART1'    ,1        ,1),
('PRODUCT1','PART2'    ,1        ,2),
('PRODUCT1','SUBASSEMBLY1' ,2        ,3), 
('PRODUCT1','PART3' ,1        ,4),
('SUBASSEMBLY1','PART3' ,4        ,1),
('SUBASSEMBLY1','PART4'    ,4        ,2),
('PRODUCT2','PART5'    ,2        ,2),
('PRODUCT9','PART6'    ,5        ,1);
SELECT * FROM PARTLIST


PART
SUBPART
QUANTITY
LINE_NUMBER


PRODUCT1
PART1
1
1

PRODUCT1
PART2
1
2

PRODUCT1
SUBASSEMBLY1
2
3

PRODUCT1
PART3
1
4

SUBASSEMBLY1
PART3
4
1

SUBASSEMBLY1
PART4
4
2

PRODUCT2
PART5
2
2

PRODUCT9
PART6
5
1


CREATE TABLE PARTTYPE (PART VARCHAR(16), 
                      IS_ASSEMBLY INT);    
INSERT INTO PARTTYPE VALUES 
('PART1',0),
('PART2',0),
('PART3',0),
('PART4',0),
('PART5',0),
('PART6',0),
('PRODUCT1',0),
('PRODUCT2',0),
('SUBASSEMBLY1',1);
SELECT PART FROM PARTTYPE WHERE IS_ASSEMBLY = 0


PART


PART1

PART2

PART3

PART4

PART5

PART6

PRODUCT1

PRODUCT2


WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT
             UNION ALL
              SELECT PARENT.PART, CHILD.SUBPART, 
                     PARENT.QUANTITY*CHILD.QUANTITY
                 FROM RPL PARENT, PARTLIST CHILD
                 WHERE PARENT.SUBPART = CHILD.PART
           )
SELECT PART, SUBPART, QUANTITY AS "QTY"
   FROM RPL
   ORDER BY PART, SUBPART;


PART
SUBPART
QTY


PRODUCT1
PART1
1

PRODUCT1
PART2
1

PRODUCT1
PART3
1

PRODUCT1
PART3
8

PRODUCT1
PART4
8

PRODUCT1
SUBASSEMBLY1
2

PRODUCT2
PART5
2

PRODUCT9
PART6
5

SUBASSEMBLY1
PART3
4

SUBASSEMBLY1
PART4
4

PART	SUBPART	QUANTITY	LINE_NUMBER
PRODUCT1	PART1	1	1
PRODUCT1	PART2	1	2
PRODUCT1	SUBASSEMBLY1	2	3
PRODUCT1	PART3	1	4
SUBASSEMBLY1	PART3	4	1
SUBASSEMBLY1	PART4	4	2
PRODUCT2	PART5	2	2
PRODUCT9	PART6	5	1