I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops:
with cteActs as ( select a.id as [id], aa.TASKNEXTID as [childid] from TASK a inner join TASKNEXT aa on a.id = aa.TASKPARENTID where a.id != aa.TASKNEXTID ), cteNext as ( select a.* from cteActs as a where a.id=42 union all select a.* from cteActs as a inner join cteNext as n on a.id = n.childid ) select * from cteNext
TASK is a list of tasks whereby 42 is "Start job" for example.
TASKNEXT links 42 to possible subtasks back in the
TASK table. e.g. it may link 42 to 43 which may be "Find materials" e.g.
ID, name, childID 42, Start job, 43 42, Start job, 44 43, Find materials, 200 44, Report to boss, 201 201, Discuss with boss, 202 202, Receive payment, 44
I think the recursion is dying because 44>201>202>44 creates a loop the query doesn't escape from. How can I allow for this? Most of the examples/tutorials I read assume a strict parent>child relationship, where a child can never be the parent of something in its own higher tree.
What I'm trying to get to is a distinct list of
TASKS that originate from flows starting at task 42, or wherever I choose.
This is iteration 2 which may work, but runs so slow:
select a.id as [id], aa.ACTIONACTIVITYID as [childid] into #temp from TASK a inner joinTASKNEXT aa on a.id = aa.TASKPARENTID where a.id != aa.TASKNEXTID create clustered index [hello] on #temp (ID ASC) create nonclustered index [hello2] on #temp (childid ASC) ; with cteNext as ( select a.*, cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path, 0 as [cyc] from #temp as a where a.id=42 union all select a.*, n.Path + cast(a.ID as varchar(10)) + ',', case when n.Path like '%,'+cast(a.ID as varchar(10))+',%' then 1 else 0 end as [cyc] from #temp as a inner join cteNext as n on a.id = n.childid where n.cyc = 0 ) select id, childid from cteNext where cyc =0