Db2 – Recursion only if a criteria is met

ctedb2recursive

I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow :

CREATE TABLE PARTLIST (PART VARCHAR(16), 
                      SUBPART VARCHAR(16), 
                      QUANTITY INT, 
                      Line_number INT);

CREATE TABLE PARTTYPE (PART VARCHAR(16), 
                      IS_ASSEMBLY INT); 

INSERT INTO PARTLIST VALUES 
('PRODUCT1','PART1'    ,1        ,1),
('PRODUCT1','PART2'    ,1        ,2),
('PRODUCT1','SUBASSEMBLY1' ,2        ,3), 
('PRODUCT1','PRODUCT2' ,1        ,4),
('SUBASSEMBLY1','PART3' ,4        ,1),
('SUBASSEMBLY1','PART4'    ,4        ,2),
('PRODUCT2','PART3'    ,2        ,2),
('PRODUCT9','PART6'    ,5        ,1);

INSERT INTO PARTTYPE VALUES 
('SUBASSEMBLY1',1);

I'm using this Query to browse the entire hierarchy of the PARTLIST table :

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;

I need some help to only "explode" part that are an assembly (Based on PARTTYPE Table) and to add a column preserving the order of the line in th result.
The desired result should be something like this :

PART         SUBPART         QTY    LINE NUMBER
-----------------------------------------------
PRODUCT1     PART1           1      1
PRODUCT1     PART2           1      2
PRODUCT1     SUBASSEMBLY1    2      3
PRODUCT1     PART3           2      4
PRODUCT2     PART3           2      1
PRODUCT9     PART6           5      1
SUBASSEMBLY1 PART3           4      1
SUBASSEMBLY1 PART4           4      2

fiddle

Best Answer

WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT, PARTTYPE
                 WHERE ROOT.SUBPART = PARTTYPE.PART
             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
/*
UNION ALL
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
LEFT JOIN PARTTYPE ON ROOT.SUBPART = PARTTYPE.PART
WHERE PARTTYPE.PART IS NULL
*/
;
Related Question