Union ALL

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) AS "Total QTY Used"

FROM RPL

GROUP BY Part, Subpart

ORDER BY Part, Subpart

В приведенном запросе во втором подзапросе рекурсивного общего табличного выражения RPL вычисляется общее количество компонентов, используемых в составе другого компонента. Так как некоторый компонент может входить в состав разных компонентов, необходимо в основном запросе использовать группирование и агрегатную функцию.

В результате выполнения приведенного запроса будут получены следующие данные:

Так, подкомпонент (Subpart) типа ’06’ входит в состав компонента ’01’ непосредственно в количестве 3 экземпляров, а также через компонент ’02’ в количестве 2 * 6 = 12 экземпляров; в общем случае, в компонент ’01’ входит 15 экземпляров подкомпонента ’06’.

Пример 3. В некоторых случаях нет необходимости обрабатывать данные на всю глубину рекурсии; достаточно ограничиться несколькими первыми уровнями. Например, требуется получить только первые два уровня вложенности компонентов в компонент с номером ’01’. Другими словами, в отчет необходимо включить значение, определяющее глубину рекурсии. Это достигается за счет включения во второй запрос дополнительной вычисляемой колонки вида col + 1 (увеличение значения колонки на 1 при каждой следующей итерации); в инициализирующем запросе в соответствующей колонке должно быть указано начальное значение. Эта колонка может использоваться во втором запросе в конструкции WHERE для ограничения глубины рекурсии.

WITH RPL (Level, Part, Subpart, Quantity) AS

(

SELECT 1, ROOT.Part, ROOT.Subpart, ROOT.Quantity

FROM PARTLIST ROOT

WHERE ROOT.Part = '01'

SELECT PARENT.Level + 1, CHILD.Part, CHILD.Subpart, CHILD.Quantity

FROM RPL PARENT, PARTLIST CHILD

WHERE PARENT.Subpart = CHILD.Part AND PARENT.Level < 2

)

SELECT Part, Level, Subpart, Quantity

FROM RPL;

Приведенный запрос аналогичен запросу примера 1. В данном запросе в общее табличное выражение добавлена колонка Level для определения глубины рекурсии. В инициализирующем запросе в данную колонку записывается значение 1. Во втором запросе значение колонки Level, считываемое из таблицы общего табличного выражения RPL (PARENT), увеличивается на 1. Для управления глубиной рекурсии во второй запрос в конструкцию WHERE добавлено условие Level < 2. Это условие гарантирует, что будет рассмотрено только два уровня вложенности компонентов.

В результате выполнения приведенного запроса будут получены следующие данные: