This example uses the following base table as its base to build a simple recursive review definition with a counter to control the possibility of infinite recursion:
partlist | ||
---|---|---|
Part | Subpart | Quantity |
00 | 01 | 5 |
00 | 05 | 3 |
01 | 02 | 2 |
01 | 03 | 3 |
01 | 04 | 4 |
02 | 05 | 7 |
02 | 06 | 6 |
This view is designed to answer questions such as the following: Which parts are required to build part 01?
Notice that as written, this query does not really answer the question because there might be more than 100 levels in the data. Strictly speaking, the question this request asks is this: Which parts, up to a maximum of 100 levels, are required to build part 01?
The recursive view definition is as follows:
CREATE RECURSIVE VIEW rpl (part, subpart, quantity, depth) AS ( SELECT root.part, root.subpart, root.quantity, 0 AS depth FROM partlist AS root WHERE root.part = '01' UNION ALL SELECT child.part, child.subpart, child.quantity, parent.depth + 1 FROM rpl AS parent, partlist AS child WHERE parent.subpart = child.part AND parent.depth <= 100);
The query to answer the question of which parts are required to build part 01 is the following SELECT request:
SELECT part, subpart, quantity FROM rpl ORDER BY part, subpart, quantity;
The result set for this query is the following.
Part | Subpart | Quantity |
---|---|---|
01 | 02 | 2 |
01 | 03 | 3 |
01 | 04 | 4 |
02 | 05 | 7 |
02 | 06 | 6 |