Solved

Recursive SQL Infinite loop

Posted on 2011-09-20
7
1,786 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

717 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