Re: Как правильно ограничивать глубину в CTE

Fri, 01 Jun 2007 00:07:25 -0700 


Tonal пишет:

sasha пишет:

а если через get_context, set_context?

А как это может помочь?

WITH RECURSIVE TR AS (
  SELECT T.*, 1 AS LEV FROM "Tree" T WHERE PARENT_ID IS NULL
  and RDB$SET_CONTEXT('USER_TRANSACTION', 'Level', 0);
  UNION ALL
SELECT T.*, TR.LEV + 1 AS LEV FROM "Tree" T, TR WHERE PARENT_ID = TR.ID AND TR.LEV < 1 and RDB$SET_CONTEXT('USER_TRANSACTION', 'Level', RDB$SET_CONTEXT('USER_TRANSACTION', 'Level') + 1) < 5 
)
SELECT ID, LEV FROM TR

Ошибся чуток:
WITH RECURSIVE TR AS (
  SELECT T.*, 1 AS LEV FROM "Tree" T WHERE PARENT_ID IS NULL
  and RDB$SET_CONTEXT('USER_TRANSACTION', 'Level', 0);
  UNION ALL
  SELECT T.*, TR.LEV + 1 AS LEV FROM "Tree" T, TR WHERE PARENT_ID =
TR.ID AND TR.LEV < 1
  and (
RDB$GET_CONTEXT('USER_TRANSACTION', 'Level') < 5 and RDB$SET_CONTEXT('USER_TRANSACTION', 'Level', RDB$GET_CONTEXT('USER_TRANSACTION', 'Level') + 1)) 
SELECT ID, LEV FROM TR

Ответить