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
The table 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
where Cycle = 1
from the main query and you should have what you are looking for, I guess? Look at the second answer, not the accepted one.cteActs
in a temp table and use that temp table instead of the CTE in the recursive part of the query. As you have it nowcteActs
is evaluated a lot, I think once for each invocation of the recursive part of the query. . Proper indexing of the temp table will also help.