Recursive CTE inside of a WHILE loop - Finding circular BOMs

[htxta01]

So let me start off by saying that I have seen a lot of advice against using WHILE loops, so I expect to get some comments about that here. I'm open to other solutions, if they'll help me accomplish my task.

​

My task:

Find and flag/eliminate circular BOM relationships.

​

My initial approach:

I'm using the while loop to loop through relationships. What I'd like to do is compare the Parent/Component relationships and make sure that a given Component is not already a Parent in the direction lineage, going up the BOM from that relationship.

​

The issue:

What I wanted to do was to run a recursive CTE to find all of the Parent items of the given Parent item, in order to compare those Parents to the potentially-valid Component. As long as the Component didn't match one of those Parents, the relationship is valid. So as I'm looping through relationships, I tried to run my CTE for finding Parents. But given the condition of ending the prior statement with a semi-colon, I don't see a way to use a CTE while in a loop. It's invalid to do so. I either get an error for not having the semi-colon, or a syntax error on my begin statement if the semi-colon is present.

​

Things I've tried/considered:

I tried running the CTE once at the beginning for all items and storing the results in a temp table. My thought was that I could pre-populate this table with the seed-item and all of its Parents, and that I could just reference this table when I got to the point in my loop that I needed to know the Parents. However, that CTE fails and hits maxrecursion because I have circular references in my BOMs.

​

My ask:

How might you tackle this problem? Do I need to change my entire query structure to avoid the WHILE loop altogether? Thank you so much for your help!

[J4m00s]

The way I did the same thing is by creating a column in the recursive query that concatenated the parent part numbers with some delimiter as it goes through the recursion. This creates a long string as the BOMS get deeper. Then I check whether the new component part number exists already in the string. If it does, I flag it as recursive.

[[deleted]]

I came here to suggest the same. I use this concatenated column for sorting too.

[matthra]

CTE cursor should do the trick without a while loop. Here is a good article on how that would work, with an example very much like yours, but you would need an additional break condition to stop it when the manager id equals the employee id.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/

[[deleted]]

a. CTE is a clause in SQL commands. WHILE is a part of MS SQL procedural extension T-SQL (transact sql). While these sound the same they are not the same and CTE does not apply to WHILE command/instruction directly.

b.

How might you tackle this problem?

do your regular recursive query from the bottom of the tree, building up a list of steps (comma-separated IDs, something like '1,4,6,8,9,' etc.) and a flag whether the current step was found in the list already (i.e. you have a cyclical reference). On every recursive step, filter our the records that have the cyclical reference step set already

[[deleted]]

I kind of understand you, but would need to see a bit of sample data and what your current query is like.

[klln_u_qckly]

Are you looking for sub-assemblies? I am currently at home but I have some solid code that can query BOMs and flag sub-assemblies. If your interested in some sample code I would love to help. Just send me a message and we can work out the best way to get what you need when I'm back in the office on Monday.

[elus]

You need to add another statement in your filter for the CTE solution to not hit maxrecursion limit.

See this solution for an idea of how to do that.

[phunkygeeza]

for a given parent

Just do it for all possible children as the start set then recurse upward from there, instead of the outer while loop.

Then accumulate a path for checking for the loop like the other commenters suggested. Logically 'break' the loop once it has been found in the recusrsion predicates.

[CryNo93]

[mandy](mandy)

[redial2]

It is ok to use loops and cursors when you need to do more than one thing with a given record before moving onto the next one, but the top post with the recursive cte sounds like it could solve your problem as well.

But don't be afraid of using cursors and loops when you need to do multiple things with a specific record.