In our previous tip, we looked at an example of a bill-of-materials implementation using common table expressions. In the example, we showed a single-level explosion, where a part and subpart were shown only once. In today's tip, I will again borrow from an example in IBM's SQL Reference "Appendix M. Recursion Example": We will look at a summarized explosion where we want to know how many of each subpart are required to build the highest-level part, which is part '01'.

To do this, we need to multiply the quantity per parent in the subquery of the RPL (recursive partlist) common table expression. We then sum the parts in the second Select statement to get the total of a subpart for the given part. The table, common table expression, and final Select statement are shown below:

The table is defined as follows:

CREATE TABLE PARTLIST (PART VARCHAR(8), SUBPART VARCHAR(8), QUANTITY INTEGER);

The common table expression is:

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