Explanation of SQL
This is used to determine any part categories that may be incorrectly set in a circular loop.
This means that a sub-category has accidentally been set as the parent group for the group that it is in itself.
An example of this is explained below:
Using the above picture as the example, if you accidentally set the Category (in Part Setup) for Cabinet Components to Cabinet Extruded Corners, you would now be in a loop.
The reason is that the Category for Cabinet Extruded Corners is Cabinet Components.
Risk of Data Corruption
None. This is simply a SQL that will determine which part categories are affected by this issue.
DECLARE @AllSubCats TABLE( ID int, ParentID int ) --subcategories that are part of a loop DECLARE @Blacklist TABLE ( ID int ) --subcategories whose ancestry ends at a root node DECLARE @Whitelist TABLE ( ID int ) --iterator DECLARE @i int; SET @i = 0; DECLARE @parent int; DECLARE @id int; DECLARE @nextAncestor int; INSERT into @AllSubCats select ID,ParentID from PricingElement where ClassTypeID = 12035 and parentID is not null and ParentClassTypeID is not null DECLARE @l int; SET @l = (select COUNT(*) from @AllSubCats); --iterate over all categories WHILE @i < @l BEGIN SET @i = @i + 1; DECLARE @visited TABLE ( Node int ); SET @parent = (select top 1 ParentID from @AllSubCats); SET @id = (select top 1 ID from @AllSubCats); --pop this from all categories DELETE from @AllSubCats where ParentID = @parent and ID = @id; INSERT into @visited values (@id); --seed the traversal with the parent SET @nextAncestor = @parent; if exists(select ID from @Whitelist where ID = @parent) BEGIN INSERT into @Whitelist values (@id); END --skip this if we have already been blacklisted ELSE if not exists(select ID from @Blacklist where ID = @id) BEGIN --loop until we have either reached the top or determined this is a loop while @nextAncestor is not null BEGIN if not exists(select Node from @visited where Node = @nextAncestor) BEGIN --continuing traversing upwards INSERT into @visited values (@nextAncestor); SET @nextAncestor = (select ParentID from PricingElement where ClassTypeID = 12035 and ID = @nextAncestor); --unless we have reached a root node if @nextAncestor is null BEGIN --then whitelist the entire traversed chain INSERT into @Whitelist select Node from @visited v LEFT JOIN @Blacklist t on v.Node = t.ID where t.ID IS NULL END END ELSE BEGIN --all the nodes we visited are in a loop, so copy our table, INSERTing any IDs into output table that have not been INSERTed already INSERT into @Blacklist select Node from @visited v LEFT JOIN @Blacklist t on v.Node = t.ID where t.ID IS NULL --debugging prints --DECLARE @cycleIDString varchar(100) = null; --SELECT @cycleIDString = COALESCE(@cycleIDString + '', '', '''') + CAST(Node AS varchar(5)) FROM @visited --Print(''Found cycle IDs: ''+@cycleIDString); --DECLARE @cycleString varchar(100) = null; --SELECT @cycleString = COALESCE(@cycleString + '' -> '', '''') + p.ElementName FROM @visited v inner join PricingElement p on p.ID = v.Node; --Print(''Found cycle: ''+@cycleString); --escape the while loop SET @nextAncestor = null; END END END DELETE from @visited where Node is not null; --cleanup the visited table END --return the blacklist select t.ID, p.ElementName from @Blacklist t inner join PricingElement p on t.ID = p.ID order by ElementName
Note: This issue has been resolved in 5.1, and additional code has been put into place to auto correct this situation if it is detected.