Solved

Recursive SQL Infinite loop

Posted on 2011-09-20
7
1,727 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
Industry Leaders: 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

Technology Partners: 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!

Question has a verified solution.

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

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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 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