Infinite loop in CTE or one row missing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Infinite loop in CTE or one row missing

Author

Topic

LaurieCox

158 Posts

Posted - 2009-07-29 : 11:35:26

Hi,

I have a table where two columns define a hierarchical structure:

New Rx_ID.

On Reorders and Change orders the new Rx_ID will appear here, and the original Rx_ID will appear in the SrcRx_ID column. On New (Active) and Discontinued orders the SrcRx_ID and the NewRx_ID will be the same.

Source Rx_ID.

On Reorders and Change orders the original Rx_ID will appear here, and the new Rx_ID will appear in the NewRx_ID column. On New (Active) and Discontinued orders the SrcRx_ID and the NewRx_ID will be the same.


newrx_id	srcrx_id	event
5138043	5138043	Active
5192504	5138043	Reorder
5543044	5192504	Reorder
5919239	5543044	Reorder
6408905	5919239	Reorder
6553711	6408905	Reorder
6762847	6553711	Reorder
7353943	6762847	Reorder
7503074	7353943	Change
7503074	7503074	Discontinu

There will always be one "Active" record, but there might not be a "Discontinued" record.

So I have tried to write a CTE that will extract the hierarchy data from the table. I have written the following:


DROP TABLE #Orders
CREATE TABLE #Orders(
     ExpectedLevel int not null,
     newrx_id int null,
     srcrx_id int null,
     event varchar(10) null)

INSERT INTO #Orders (ExpectedLevel,newrx_id,srcrx_id,event)
SELECT 0, 5138043, 5138043,   'Active' UNION ALL
SELECT 1, 5192504, 5138043, 'Reorder' UNION ALL
SELECT 2, 5543044, 5192504, 'Reorder' UNION ALL
SELECT 3, 5919239, 5543044, 'Reorder' UNION ALL
SELECT 4, 6408905, 5919239, 'Reorder' UNION ALL
SELECT 5, 6553711, 6408905, 'Reorder' UNION ALL
SELECT 6, 6762847, 6553711, 'Reorder' UNION ALL
SELECT 7, 7353943, 6762847, 'Reorder' UNION ALL
SELECT 8, 7503074, 7353943, 'Change' UNION ALL
SELECT 9, 7503074, 7503074, 'Discontinu'

;With OrderTree (Newrx_id,Srcrx_id,event,ExpedtedLevel,Level)
as
(
-- Anchor member definition

    Select  ord.Newrx_id, 
                ord.Srcrx_id,
                ord.event,ord.
                ord.ExpectedLevel, 
                0 as level
      from  #Orders as ord
     where event = 'Active' and ord.newrx_id = ord.Srcrx_id
    UNION ALL
-- Recursive member definition
    Select  ord.Newrx_id, 
                ord.Srcrx_id,
                ord.event,ord.
                ord.ExpectedLevel, 
                level + 1
      from  #Orders as ord
    INNER JOIN OrderTree as ot on ot.newrx_id = ord.Srcrx_id 
                    and ord.newrx_id <> ord.Srcrx_id
)

select * from OrderTree OPTION (MAXRECURSION 12)

In the inner join in the Recursive part of the CTE if I have the following:


    INNER JOIN OrderTree as ot on ot.newrx_id = ord.Srcrx_id 
                    and ord.newrx_id <> ord.Srcrx_id

I get this output (that is missing the discontinue record):


Newrx_id	Srcrx_id	event	ExpectedLevel	Level
5138043	5138043	Active	0	0
5192504	5138043	Reorder	1	1
5543044	5192504	Reorder	2	2
5919239	5543044	Reorder	3	3
6408905	5919239	Reorder	4	4
6553711	6408905	Reorder	5	5
6762847	6553711	Reorder	6	6
7353943	6762847	Reorder	7	7
7503074	7353943	Change	8	8

If I use this inner join:


    INNER JOIN OrderTree as ot on ot.newrx_id = ord.Srcrx_id

I get this:


Newrx_id	Srcrx_id	event	ExpectedLevel	Level
5138043	5138043	Active	0	0
5138043	5138043	Active	0	1
5192504	5138043	Reorder	1	1
5543044	5192504	Reorder	2	2
5919239	5543044	Reorder	3	3
6408905	5919239	Reorder	4	4
6553711	6408905	Reorder	5	5
6762847	6553711	Reorder	6	6
7353943	6762847	Reorder	7	7
7503074	7353943	Change	8	8
7503074	7503074	Discontinu	9	9
7503074	7503074	Discontinu	9	10
7503074	7503074	Discontinu	9	11
7503074	7503074	Discontinu	9	12
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 12 
has been exhausted before statement completion.

What can I do to fix this?

Thanks,

Laurie

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-29 : 11:53:30

You can use the OPTION clause and set the MAXRECURSION hint to a value between 0 and 32,767. When 0 is specified, no limit is applied.

LaurieCox

158 Posts

Posted - 2009-07-29 : 12:13:00

quote:
Originally posted by Lamprey

You can use the OPTION clause and set the MAXRECURSION hint to a value between 0 and 32,767. When 0 is specified, no limit is applied.

Hi Lamprey,

Thanks for the reply. But I think I was not clear enough in my description of the problem.

I want the recursion to stop when it reaches the end of the hierarchy and I have no idea how many levels there actually are. In the example data there are 10 levels, but in the real data the number is unknown.

I put the OPTION (MAXRECURSION 12) code to protect it from going into a infinite loop while I get the code right.

The output I want is this:


Newrx_id	Srcrx_id	event	ExpectedLevel	Level
5138043	5138043	Active	0	0
5192504	5138043	Reorder	1	1
5543044	5192504	Reorder	2	2
5919239	5543044	Reorder	3	3
6408905	5919239	Reorder	4	4
6553711	6408905	Reorder	5	5
6762847	6553711	Reorder	6	6
7353943	6762847	Reorder	7	7
7503074	7353943	Change	8	8
7503074	7503074	Discontinu	9	9

Currently the code either does not include the last line or goes into a infinite loop, depending on how I code the inner join of the recursive part of the CTE.

So how do I put a check in the code to stop the infinite loop, but still get the last row.

Thanks,

Laurie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-29 : 19:18:52

sounds like you hit circular reference.

Check this link - Peso came up with a neat way to trap circular references in CTE's. I've used the technique a few different times in unexpected places.

All the best/

http://weblogs.sqlteam.com/peterl/archive/2008/11/27/Expand-network-using-CTE-without-circular-reference.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources