Solved

Recursive SQL Infinite loop

Posted on 2011-09-20
7
1,584 Views
Last Modified: 2012-05-12
Hi,

I Have a table like below

ProductID          RepProdID
######            ###########
a1                       b1
b1                       c1
c1                        d1

Means, The product a1 is replaced by b1 and b1 by c1 etc

Now, If I query with product ID "a1" then I want the output

b1
c1
d1

Since product "a1" is the source of all this products.

If I query with product ID "b1" then I want the output

c1
d1

I was using the below query to achieve this.

DECLARE @ProductID VARCHAR(5)
SET @ProductID = 'a1'

;WITH CTE
AS
(
      SELECT RepProdID, 0 AS [Level]
      FROM   YourTable
      WHERE ProductID = @ProductID
      UNION ALL
      SELECT  a.RepProdID, [Level] + 1
      FROM   YourTable a INNER JOIN CTE b ON b.RepProdID = a.ProductID
)
SELECT  RepProdID FROM   CTE

All are fine, but if I have a below data in the table, then the recursion enters into infinite loop and the query fails. How can I alter the above query

ProductID          RepProdID
######            ###########
a1                       b1
b1                       a1
0
Comment
Question by:radcaesar
7 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36565814
well you shouldn't allow the table to be updated like that in the first place...
what is it supposed to represent?  (wich is the parent?)


;WITH CTE
AS
(
      SELECT RepProdID, 0 AS [Level]
      FROM   YourTable
      WHERE ProductID = @ProductID
      UNION ALL
      SELECT  a.RepProdID, [Level] + 1
      FROM   YourTable a
      INNER JOIN CTE b
      ON b.RepProdID = a.ProductID
     where not exists (select repprodid from vte as x where a.repprodid=x.repprodid
                                 and x,level < b.level + 1)
)
SELECT  RepProdID FROM   CTE

0
 
LVL 9

Author Comment

by:radcaesar
ID: 36565920
I am getting the below error when I use this,

Recursive member of a common table expression 'CTE' has multiple recursive references.

In the vte is a typo and its CTE right?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36566018
yes vte was supposed to be cte

so it errors?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:deighton
ID: 36566034
the CTE is limited in how it can sub query itself - it strikes me that you could easily have a circular or infinite reference, as in fact you have there

I think instead of a recursive CTE, use a temporary table and a while loop,





0
 
LVL 9

Author Comment

by:radcaesar
ID: 36566111
No I had changed it to CTE and then this error occurs
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36566654
you could try this

it should stop your simple case , it wont stop a recursion chain ..  a - b , b - c , c - a   rhough

you'd need to use the temp table build loop approach...

ideally you should prevent the situation from happening by implementing a trigger to validate...
;WITH CTE
AS
(
      SELECT RepProdID, 0 AS [Level]
            ,0 as OK
      FROM   YourTable
      WHERE ProductID = @ProductID
      UNION ALL
      SELECT  a.RepProdID, [Level] + 1
        ,coalesce((select 1 from yourtable as x where x.repprodid=A.REPPORTID AND X.PRODUCTID=B.REPPRODID),0)
      FROM   YourTable a 
      INNER JOIN CTE b 
      ON b.RepProdID = a.ProductID
      AND B.OK=0
     
)
SELECT  RepProdID 
  FROM   CTE
 ORDER BY [LEVEL],REPPRODID

Open in new window

0
 
LVL 2

Expert Comment

by:grvmohan
ID: 36572194
A very simple solution:

Modify your CTE as:

;WITH CTE
AS
(
      SELECT RepProdID, 0 AS [Level]
      FROM   YourTable
      WHERE ProductID = @ProductID
      UNION ALL
      SELECT  a.RepProdID, [Level] + 1
      FROM   YourTable a INNER JOIN CTE b ON b.RepProdID = a.ProductID
      AND a.ProductID <> @ProductID
)
SELECT  RepProdID FROM   CTE
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now