Листинг 6.

Запрос, возвращающий транзитивное замыкание циклического ориентированного графа

WITH TC(src_pid, tgt_pid, distance, path)
AS
( SELECT src_pid, tgt_pid, 1,
    CAST(?.? + CAST(src_pid AS varchar (10)) + ?.?
         + CAST(tgt_pid AS varchar (10)) + ?.?
         AS varchar (900))
  FROM Links

  UNION ALL

  SELECT P.src_pid, C.tgt_pid, P.distance + 1,
    CAST(P.path + CAST(C.tgt_pid AS varchar (10)) + ?.?
         AS varchar (900))
  FROM Links AS C
    JOIN TC AS P
      ON C.src_pid = P.tgt_pid
  WHERE P.path NOT LIKE ?%.? + CAST(C.tgt_pid AS varchar (10)) + ?.%? )
SELECT DISTINCT src_pid, tgt_pid FROM TC
ORDER BY src_pid, tgt_pid;