0

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

  • Add WHERE to base CTE query. – Akina Oct 24 at 12:18
  • link I tried it but didn't work. – user2253258 Oct 24 at 12:29
  • PRODUCT1 contains 2 SUBASSEMBLY1. Each SUBASSEMBLY1 contains 4 PART3. Why desured output shows QTY=2 for PRODUCT1/PART3 ? – Akina Oct 24 at 12:50
0
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
*/
;
  • link It didn't seems to work properly. – user2253258 Oct 24 at 12:42
  • @user2253258 Why? the query selects PRODUCT1/SUBASSEMBLY1 record (its SUBPART is listed in PARTTYPE) and two its childs. For to obtain full list of parts look at my answer to previous your question. – Akina Oct 24 at 12:45
  • @user2253258 Or you may UNION non-asssemblies in main query. Code updated. – Akina Oct 24 at 12:52
  • PRODUCT1 contain 1 PART1, 1 PART2, 2 SUBASSEMBLY, 2 PART3. – user2253258 Oct 24 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.