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-20 17:04:33Z

2

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';

edited Mar 20, 2012 at 17:04

answered Mar 20, 2012 at 16:50

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Misiu Over a year ago

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

Andrey Gurinov Over a year ago

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?

Misiu Over a year ago

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

Andrey Gurinov Over a year ago

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 Over a year ago

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.

|

Collectives™ on Stack Overflow

TSQL recursion - max recursion error with duplitace values in table

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related