TSQL recursion - max recursion error with duplitace values in table

Question

Related question: TSQL check if specific rows sequence exists

I'm asking second question about recursion, because this time it is different thing (in my opinion) so please don't delete it.

I have table containing values:

ORDER_ID   Previous_STATUS_ID   Next_STATUS_ID  Create_Date
2          null                 1               '2012-01-02'
2          1                    2               '2012-01-03'
2          2                    3               '2012-01-04'
2          3                    1               '2012-01-05'
3          1                    2               '2012-01-06'
2          2                    3               '2012-01-10'
2          3                    5               '2012-01-13'
2          5                    1               '2012-01-22'
2          1                    2               '2012-01-22'

This works fine:

with change_tree as 
(
  SELECT order_id,
         previous_status_id, 
         next_status_id,
         cast(next_status_id as varchar(max)) as status_path
  FROM status_change
  WHERE previous_status_id = 5
    AND order_id = 2

  UNION ALL 

  SELECT sc.order_id,
         sc.previous_status_id,
         sc.next_status_id,
         ct.status_path + ',' + cast(sc.next_status_id as varchar(max))
  FROM status_change sc 
    JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id
)
SELECT *
FROM change_tree
WHERE status_path = '5,1';

But if I modify it like so:

with change_tree as 
(
  SELECT sc.order_id,
         sc.previous_status_id, 
         sc.next_status_id,
         cast(sc.next_status_id as varchar(max)) as status_path,
         sc.Create_Date as StartDate,
         sc.Create_Date as EndDate
  FROM status_change sc
  WHERE previous_status_id = 1
    AND order_id = 2

  UNION ALL 

  SELECT sc.order_id,
         sc.previous_status_id,
         sc.next_status_id,
         ct.status_path + ',' + cast(sc.next_status_id as varchar(max))
         sc.Create_Date as StartDate,
         st.Create_Date as EndDate
  FROM status_change sc 
    JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id
)
SELECT *
FROM change_tree
WHERE status_path = '1,2,3';

I get max recursion error.

This is kind of data that I'm expecting to get:

ORDER_ID   StartDate        EndDate
2          '2012-01-02'     '2012-01-04' 
2          '2012-01-05'     '2012-01-10'

This will be done as a report. So I was thinking about storing results in another table and in the night doing only those orders that are new or for which sequence of statuses has ended.

So in my report table I must have those two records, and after adding next record to status_change table like:

ORDER_ID   Previous_STATUS_ID   Next_STATUS_ID  Create_Date
2          2                    3               '2012-02-25'

My procedure (function should add to report table only last sequence). Hope You'll get my point :)

Andrey Gurinov · Accepted Answer · 2012-03-20T17:04:33.337

You are being catched in a loop because first you select this row:

2          1                    2               '2012-01-22'

then

2          2                    3               '2012-01-10'

then

2          3                    1               '2012-01-05'

and then first line again and again. You need to check is your data consistent enough for your report or not.

Or, maybe you need to add following condition to your CTE:

ct.previous_status_id > sc.previous_status_id

Or, you can change your script by addind parameter that is controling the level of recursion like that:

with change_tree as  
( 
  SELECT sc.order_id, 
         sc.previous_status_id,  
         sc.next_status_id, 
         cast(sc.next_status_id as varchar(max)) as status_path, 
         sc.Create_Date as StartDate, 
         sc.Create_Date as EndDate,
         1 AS deep 
  FROM status_change sc 
  WHERE previous_status_id = 1 
    AND order_id = 2 

  UNION ALL  

  SELECT sc.order_id, 
         sc.previous_status_id, 
         sc.next_status_id, 
         ct.status_path + ',' + cast(sc.next_status_id as varchar(max)) 
         sc.Create_Date as StartDate, 
         st.Create_Date as EndDate,
         ct.deep + 1 AS deep
  FROM status_change sc  
    JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id 
  WHERE deep < 3
) 
SELECT * 
FROM change_tree 
WHERE status_path = '1,2,3';

Or, change your script in this way (this will restrict your recursion from going in wrong ways):

with change_tree as  
( 
  SELECT sc.order_id, 
         sc.previous_status_id,  
         sc.next_status_id, 
         cast(sc.next_status_id as varchar(max)) as status_path, 
         sc.Create_Date as StartDate, 
         sc.Create_Date as EndDate
  FROM status_change sc 
  WHERE previous_status_id = 1 
    AND order_id = 2 

  UNION ALL  

  SELECT sc.order_id, 
         sc.previous_status_id, 
         sc.next_status_id, 
         ct.status_path + ',' + cast(sc.next_status_id as varchar(max)) 
         sc.Create_Date as StartDate, 
         st.Create_Date as EndDate
  FROM status_change sc  
    JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id 
  WHERE 
     '1,2,3' LIKE ct.status_path + ',' + cast(sc.next_status_id as varchar(max)) + '%'
) 
SELECT * 
FROM change_tree 
WHERE status_path = '1,2,3';

This is the exact situation I'm having. I'll check Your answer. One more thing - how to do that report incrementally? So that my procedure won't have to go through whole status_change table every night? - @Andrey Gurinov — Misiu, Mar 21 '12 at 08:19
I believe that could be a subject for another question. But why do you worry about full reprocessing of your table? Is it so big? Does processing take a lot of time? — Andrey Gurinov, Mar 21 '12 at 08:23
I have about 800k+ rows and about 30 patterns to search (different status_path combination). So I think that doing every combination for every row gives me about 24 million operations. But orders are still being added to table and my patterns also grow in number. Report in type 'on demand', so user clicks button and moments later he should get that report. That's why I think that aggregation table is necessary. If I'm wrong please correct me :) @Andrey Gurinov — Misiu, Mar 21 '12 at 08:33
Can your old data have been changed? I mean, does incremental process should only add new records to the aggregate table, or should update/delete old as well? — Andrey Gurinov, Mar 21 '12 at 08:36
In case with 30 patterns I think the last option I suggested will be the most useful. You can combine different status_path combinations with OR operator, both in CTE and outer SELECT. — Andrey Gurinov, Mar 21 '12 at 08:50
In my report I need to know how many orders where with status 1,2,3. How many where with status 2,3,5 etc. Every order can have multiple status_path in history. So I was thinking to do a procedure from Your code with status_path as parameter and then calling it for every order for every pattern. Do I think correct? Or is there a better way? — Misiu, Mar 21 '12 at 08:56

TSQL recursion - max recursion error with duplitace values in table

1 Answers1