Bill of materials (BOM) applications are a common requirement in many business environments. To illustrate the capability of a recursive common table expression for BOM applications, consider a table of parts with associated subparts and the quantity of subparts required by the part. For this example, create the table as follows.
CREATE TABLE PARTLIST
(PART VARCHAR(8),
SUBPART VARCHAR(8),
QUANTITY INTEGER);
In order to give query results for this example, assume the PARTLIST table is populated with the following values.
PART SUBPART QUANTITY -------- -------- ----------- 00 01 5 00 05 3 01 02 2 01 03 3 01 04 4 01 06 3 02 05 7 02 06 6 03 07 6 04 08 10 04 09 11 05 10 10 05 11 10 06 12 10 06 13 10 07 14 8 07 12 8