Solved

Recursive SQL Infinite loop

Posted on 2011-09-20
7
1,644 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 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