Recursive SQL Infinite loop

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
LVL 9
radcaesarAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
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
 
radcaesarAuthor Commented:
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
 
LowfatspreadCommented:
yes vte was supposed to be cte

so it errors?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
deightonprogCommented:
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
 
radcaesarAuthor Commented:
No I had changed it to CTE and then this error occurs
0
 
LowfatspreadCommented:
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
 
grvmohanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.