One: BOM expands (unfolding to the bottom of any of the formation) to write a simple, depending on the specific requirements. Create Table Table (Levelid Int, Levelname Char (2), Parent Int) Insert Table Select 1, 'AA', 0UNION All SELECT 2, 'BB', 1UNION All SELECT 3, 'CC', 1UNION All SELECT 4, 'DD ', 2UNION All SELECT 5,' EE ', 3UNION ALL SELECT 6,' FF ', 5
create function bom (@name char (2)) returns @tb table (levelid int, levelname char (2), parent int) asbegininsert @tb select levelID, LevelName, parent from table where Levelname = @namewhile @@ rowcount> 0 insert @TB Select LevelID, LevelName, Parent from WHERE PARENT IN (SELECT Levelid from @TB) And LevelId Not in (SELECT Levelid from @TB) Returnend
Select * from dbo.bom ('bb') Levelid LevelName Parent ---------------------------- 2 BB 14 DD 2
(The number of rows affects is 2 lines)
Two: LLC (minimum layers) 1: At least two fields in the material main file item Itemno, LLC2: BOMs have at least two fields (tree) ParentItem, itemno3: Create Procedure LlcasUpdate Material Main File Set LLC = 0 - Prevent all LLCs all clear to 0Declare @i tinyintset @i = 0WHILE @i <@i = 0WHILE @i <= 10 - assuming bus tops only 10 layers of begin update a set a.llc = @i 1 - sub-node LLC plus 1 From material main file a join bom b on a.itemno = B.Itemno join material master file c on c.itemno = b.parentItem where c.llc = @i set @i = @i 1nd
/ *********** USAGE: EXEC LLC ******* /
