6

My CTE runs in an infinite loop for a specific customer and I am not able to find out why.

Here is the query:

;WITH ClassTree
           AS (SELECT ID, NAME, Parent_ID
               FROM   TableName
               WHERE  ID = 1
               UNION ALL
               SELECT T.ID, T.NAME, T.Parent_ID
               FROM   TableName T WITH (NOLOCK)
                      JOIN ClassTree
                        ON Parent_ID = ClassTree.ID
)
SELECT * FROM ClassTree
3
  • 3
    If it does work for some customers, then for those customers it doesn't work for you likely have a hierarchy loop in the data. Apr 5 '12 at 11:15
  • You would find that as your ID is joined to a Parent_ID, and somewhere in that chain the Parent_ID is joined to a same ID. That would be the cause of the loop. Apr 5 '12 at 12:46
  • To avoid infinite loops, you may want to use HierarchyID or materialized path to implement your hierarchy. Your approach is very error-prone, as you have just seen.
    – A-K
    Apr 5 '12 at 18:27
3

I think this should find the problem record for you. It adds a LEVEL to your hierarchy, then looks for an individual ID record that exists at multiple levels.

;WITH ClassTree
           AS (SELECT ID, NAME, Parent_ID, 1 as 'Level'
               FROM   TableName
               WHERE  ID = 1
               UNION ALL
               SELECT T.ID, T.NAME, T.Parent_ID, Level + 1 as 'Level'
               FROM   TableName T WITH (NOLOCK)
                      JOIN ClassTree
                        ON Parent_ID = ClassTree.ID
)

SELECT *
FROM ClassTree c1
WHERE EXISTS (SELECT 1 FROM ClassTree c2
              WHERE c2.id = c1.id
              AND c2.Level > c1.level)
1
  • Thanks everybody for great help... I have found the error where i am doing wrong.. this is a case of circular reference, there is one parent_id which is inserted wrong at the time of insertion and this is created a whole problem as it is pointing wrong reference to parent object, i found it by checking levels for my objects. Thanks everybody.
    – Ashish
    Apr 6 '12 at 5:11
8

Here is a little something that you can use to find your cycles.

declare @T table
(
  ID int,
  Parent_ID int
)

insert into @T values
(1, 3),(2, 1),(3, 2),    -- This is a cycle
(4, 4),                  -- This is a cycle
(5, null),(6, 5),(7, 6)  -- This is not a cycle

;with C as
(
  select T.ID,
         T.Parent_ID,
         cast(',' + cast(ID as varchar(10)) + ',' as varchar(max)) as Path,
         0 Cycle
  from @T as T
  union all
  select T.ID,
         T.Parent_ID,
         C.Path + cast(T.ID as varchar(10)) + ',',
         case when C.Path like '%,'+cast(T.ID as varchar(10))+',%' 
           then 1 
           else 0 
         end
  from @T as T
    inner join C  
      on T.Parent_ID = C.ID
  where C.Cycle = 0
)
select *
from C
where Cycle = 1

Result:

ID          Parent_ID   Path       Cycle
----------- ----------- ---------- -----------
4           4           ,4,4,      1
3           2           ,3,1,2,3,  1
2           1           ,2,3,1,2,  1
1           3           ,1,2,3,1,  1
1

The very first thing I would do is change this:

JOIN ClassTree
     ON Parent_ID = ClassTree.ID 

to this:

JOIN ClassTree                         
     ON t.Parent_ID = ClassTree.ID 

It might be trying to join ClassTree to itself since you didn't specify the table alias in the join.

1
  • Without the table prefix you get Ambiguous column name 'Parent_ID' Apr 5 '12 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.