PREVIOUS

The PREVIOUS function (XDB mode only) is used in combination with the LEVEL condition to give queries recursive inference capabilities. One PREVIOUS function is permitted per subselect.

The SUBPART table contains the following records:

The SUBPART column lists the part numbers of all subparts used in assembling a complete product, while the part numbers of all products that contain the assembled subparts appear in the PRODUCT column. For instance, product P8 is made up of subparts P4 and P7. However, since product P7 is also made up of subparts P1, P2 and P6, we can infer that subparts P1, P2 and P6 are also required by a completed product P8. Standard SQL cannot easily detect interrelationships between products and subparts in this table.

The following query uses the PREVIOUS function to determine all the subparts which go into the making of a single product P11.

SELECT subpart
FROM subpart
WHERE product = PREVIOUS(subpart)
  AND LEVEL = BOTTOM
  AND product = "P11"

The PREVIOUS function (in combination with the LEVEL condition) uses recursive inference to "explode" the data in the SUBPART column (see the following figure) to match each product with all the subparts required for its assembly.

See the description of the LEVEL condition for a discussion of the BOTTOM keyword. In the example above, BOTTOM causes only the "bottom" level of subpart numbers (only those subparts that are not themselves composed of other subparts) contained in each recursive branch of the figure to appear in the result.