Solved

T-SQL Recursive CTE for Bill of Materials

Posted on 2014-03-17
7
1,447 Views
Last Modified: 2014-06-17
I am trying to write sql that will get all levels of this diagram:
BOM tree visual
Here are the tables and data:

CREATE TABLE [dbo].[BOM]
(      [ITEMID] [nvarchar](20) NOT NULL,
      [BOMID] [nvarchar](20) NOT NULL)

INSERT INTO dbo.BOM VALUES
('13089', 'BOM-145100')
,('12382', 'BOM-145100')
,('12386', 'BOM-145100')
,('12375', 'BOM-145100')
,('0107462', 'BOM-145100')
,('10872', 'BOM-145100')
,('0107487', 'BOM-145100')
,('10610', 'BOM-145100')
,('12323', 'BOM-145100')
,('11601', 'BOM-145100')
,('10741', 'BOM-145100')
,('0128770', 'BOM-145100')
,('87701', 'BOM-145100')
,('74675', 'BOM-145100')
,('15518', 'BOM-050751')
,('0124889', 'BOM-050751')
,('14422', 'BOM-050751')
,('30010', 'BOM-050751')
,('74674', 'BOM-050751')
,('16340', 'BOM-050752')
,('10077', 'BOM-050752')
,('74673', 'BOM-050752')
,('11280', 'BOM-050753')
,('12066', 'BOM-050753')
,('74672', 'BOM-050753')
,('89213', 'BOM-050754');

CREATE TABLE [dbo].[BOMversion]
(      [ITEMID] [nvarchar](20) NOT NULL,
      [BOMID] [nvarchar](20) NOT NULL)

INSERT INTO dbo.BOMversion VALUES
('74671', 'BOM-145100')
,('74675', 'BOM-050751')
,('74674', 'BOM-050752')
,('74673', 'BOM-050753')
,('74672', 'BOM-050754')

Here is the latest recursive code that I have tried:

WITH BOMcte (bomID, ItemId, bid, bomLevel)
AS
(
-- Anchor member definition
      SELECT       b.bomID
                  ,b.ITEMID
                  ,bv.BOMID as bid
                  ,1 as BOMLEVEL                  
                                                            
      FROM dbo.BOM b                  
                  
      LEFT JOIN dbo.BOMVERSION bv on bv.ITEMID =b.ITEMID
      
      WHERE b.BOMID = 'BOM-145100'
        
            
 UNION ALL
   
-- Recursive member definition
      SELECT      bm.bomID
                  ,bm.ITEMID
                  ,cte.bomID
                  ,cte.BOMLEVEL+1 as BomLevel
                                                            
      FROM dbo.BOM bm
      
      JOIN dbo.BOMVERSION bomv on bomv.ITEMID =bm.ITEMID

      INNER JOIN BOMcte cte ON cte.bid = bomv.BomID
)

-- Statement that executes the CTE
SELECT *

FROM BOMcte

ORDER BY bomLevel

OPTION (maxrecursion 0)

GO

Here are the results:
Query results
Instead of working down through BOM-050751 (Item #74675) it seems to have gone "up" and found the "parent" (BOM-145100).  I have looked at many, many examples and can not figure out why this is happening.  Can anyone help me please?
0
Comment
Question by:tdisher
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39935869
Is this the expected result?
|      BOMID |  ITEMID |        BID | BOMLEVEL |
|------------|---------|------------|----------|
| BOM-050751 |   15518 |     (null) |        1 |
| BOM-050751 | 0124889 |     (null) |        1 |
| BOM-050751 |   14422 |     (null) |        1 |
| BOM-050751 |   30010 |     (null) |        1 |
| BOM-050751 |   74674 | BOM-050752 |        1 |
| BOM-050751 |   74674 | BOM-050751 |        2 |
| BOM-145100 |   74675 | BOM-050751 |        3 |
| BOM-145100 |   74675 | BOM-145100 |        4 |
		

Open in new window

If so your "anchor" is wrong (currently it returns all records) try this:
WITH BOMcte (bomID, ItemId, bid, bomLevel) 
AS
(
-- Anchor member definition
      SELECT       b.bomID
                  ,b.ITEMID
                  ,bv.BOMID as bid
                  ,1 as BOMLEVEL                  
                                                            
      FROM dbo.BOM b                  
                  
      LEFT JOIN dbo.BOMVERSION bv on bv.ITEMID =b.ITEMID
      
      WHERE b.BOMID = 'BOM-050751' /* 'BOM-145100' */
         
             
 UNION ALL
    
-- Recursive member definition
      SELECT      bm.bomID 
                  ,bm.ITEMID
                  ,cte.bomID
                  ,cte.BOMLEVEL+1 as BomLevel
                                                            
      FROM dbo.BOM bm
      
      JOIN dbo.BOMVERSION bomv on bomv.ITEMID =bm.ITEMID

      INNER JOIN BOMcte cte ON cte.bid = bomv.BomID
)

-- Statement that executes the CTE
SELECT *

FROM BOMcte

ORDER BY bomLevel

OPTION (maxrecursion 0)

Open in new window

0
 

Author Comment

by:tdisher
ID: 39936785
That is close.  I was hoping that the recursive element would work in a way that would also show me what is under the subsequent BOMs.  Here is what I was hoping to accomplish:

Desired results
So, we seed the recursion with BOM-145100.  That returns 14 rows.  One of which is 74675, which has a BID of BOM-050751.  I wanted the recursive element to take that BID and return five rows, one of which has a BID of BOM-050752.  It should then take that BID and return 3 elements, and so on...

I see that I made a copying error when setting up the data for the BOM table.  Here is the corrected code:

CREATE TABLE [dbo].[BOM]
(      [ITEMID] [nvarchar](20) NOT NULL,
      [BOMID] [nvarchar](20) NOT NULL)

INSERT INTO dbo.BOM VALUES
('13089', 'BOM-145100')
,('12382', 'BOM-145100')
,('12386', 'BOM-145100')
,('12375', 'BOM-145100')
,('0107462', 'BOM-145100')
,('10872', 'BOM-145100')
,('0107487', 'BOM-145100')
,('10610', 'BOM-145100')
,('12323', 'BOM-145100')
,('11601', 'BOM-145100')
,('10741', 'BOM-145100')
,('0128770', 'BOM-145100')
,('87701', 'BOM-145100')
,('74675', 'BOM-145100')
,('15518', 'BOM-050751')
,('0124889', 'BOM-050751')
,('14422', 'BOM-050751')
,('30010', 'BOM-050751')
,('74674', 'BOM-050751')
,('16340', 'BOM-050752')
,('10077', 'BOM-050752')
,('74673', 'BOM-050752')
,('11280', 'BOM-050753')
,('12066', 'BOM-050753')
,('74672', 'BOM-050753')
,('89213', 'BOM-050754');
0
 

Author Comment

by:tdisher
ID: 39937180
I have had a breakthrough of sorts.  

The following code:

With cte as 

	(SELECT	 b.bomID
		,b.ITEMID
		,bv.BOMID as bid
														
	FROM dbo.BOM b			
			
	LEFT JOIN dbo.BOMVERSION bv ON bv.ITEMID =b.ITEMID
	
	WHERE b.BOMID = 'BOM-145100'
	
	UNION ALL 
	
	SELECT	 b.bomID
		,b.ITEMID
		,bv.BOMID as bid
															
	FROM dbo.BOM b			
			
	LEFT JOIN dbo.BOMVERSION bv ON bv.ITEMID =b.ITEMID
	
	
)
	
SELECT * FROM cte

Open in new window


Gives the following results:

cte results
So now I am getting the query to go five layers deep, but it is repeating the initial query twice.  Any idea how to stop it from giving me duplicate results for the first BOM?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39938293
You need to find a way to seed the top query with a single relevant record I believe, look at this where I have arbitrarily used "top 1" in the second query:
**Query 1**:

    With cte as 
    
    	(SELECT	b.bomID
    		,b.ITEMID
    		,bv.BOMID as bid
    														
    	FROM dbo.BOM b			
    			
    	LEFT JOIN dbo.BOMVERSION bv ON bv.ITEMID =b.ITEMID
    	
    	WHERE b.BOMID = 'BOM-145100'
    	
    	UNION ALL 
    	
    	SELECT	 b.bomID
    		,b.ITEMID
    		,bv.BOMID as bid
    															
    	FROM dbo.BOM b			
    			
    	LEFT JOIN dbo.BOMVERSION bv ON bv.ITEMID =b.ITEMID
    	
    	
    )
    	
    SELECT * FROM cte
    

**[Results][2]**:
    
    |      BOMID |  ITEMID |        BID |
    |------------|---------|------------|
    | BOM-145100 |   13089 |     (null) |
    | BOM-145100 |   12382 |     (null) |
    | BOM-145100 |   12386 |     (null) |
    | BOM-145100 |   12375 |     (null) |
    | BOM-145100 | 0107462 |     (null) |
    | BOM-145100 |   10872 |     (null) |
    | BOM-145100 | 0107487 |     (null) |
    | BOM-145100 |   10610 |     (null) |
    | BOM-145100 |   12323 |     (null) |
    | BOM-145100 |   11601 |     (null) |
    | BOM-145100 |   10741 |     (null) |
    | BOM-145100 | 0128770 |     (null) |
    | BOM-145100 |   87701 |     (null) |
    | BOM-145100 |   74675 | BOM-050751 |
    | BOM-145100 |   13089 |     (null) |
    | BOM-145100 |   12382 |     (null) |
    | BOM-145100 |   12386 |     (null) |
    | BOM-145100 |   12375 |     (null) |
    | BOM-145100 | 0107462 |     (null) |
    | BOM-145100 |   10872 |     (null) |
    | BOM-145100 | 0107487 |     (null) |
    | BOM-145100 |   10610 |     (null) |
    | BOM-145100 |   12323 |     (null) |
    | BOM-145100 |   11601 |     (null) |
    | BOM-145100 |   10741 |     (null) |
    | BOM-145100 | 0128770 |     (null) |
    | BOM-145100 |   87701 |     (null) |
    | BOM-145100 |   74675 | BOM-050751 |
    | BOM-050751 |   15518 |     (null) |
    | BOM-050751 | 0124889 |     (null) |
    | BOM-050751 |   14422 |     (null) |
    | BOM-050751 |   30010 |     (null) |
    | BOM-050751 |   74674 | BOM-050752 |
    | BOM-050752 |   16340 |     (null) |
    | BOM-050752 |   10077 |     (null) |
    | BOM-050752 |   74673 | BOM-050753 |
    | BOM-050753 |   11280 |     (null) |
    | BOM-050753 |   12066 |     (null) |
    | BOM-050753 |   74672 | BOM-050754 |
    | BOM-050754 |   89213 |     (null) |


**Query 2**:

    With cte as 
    
    	(SELECT	top 1 b.bomID /* change here */
    		,b.ITEMID
    		,bv.BOMID as bid
    														
    	FROM dbo.BOM b			
    			
    	LEFT JOIN dbo.BOMVERSION bv ON bv.ITEMID =b.ITEMID
    	
    	WHERE b.BOMID = 'BOM-145100'
    	
    	UNION ALL 
    	
    	SELECT	 b.bomID
    		,b.ITEMID
    		,bv.BOMID as bid
    															
    	FROM dbo.BOM b			
    			
    	LEFT JOIN dbo.BOMVERSION bv ON bv.ITEMID =b.ITEMID
    	
    	
    )
    	
    SELECT * FROM cte
    

**[Results][3]**:
    
    |      BOMID |  ITEMID |        BID |
    |------------|---------|------------|
    | BOM-145100 |   13089 |     (null) |
    | BOM-145100 |   13089 |     (null) |
    | BOM-145100 |   12382 |     (null) |
    | BOM-145100 |   12386 |     (null) |
    | BOM-145100 |   12375 |     (null) |
    | BOM-145100 | 0107462 |     (null) |
    | BOM-145100 |   10872 |     (null) |
    | BOM-145100 | 0107487 |     (null) |
    | BOM-145100 |   10610 |     (null) |
    | BOM-145100 |   12323 |     (null) |
    | BOM-145100 |   11601 |     (null) |
    | BOM-145100 |   10741 |     (null) |
    | BOM-145100 | 0128770 |     (null) |
    | BOM-145100 |   87701 |     (null) |
    | BOM-145100 |   74675 | BOM-050751 |
    | BOM-050751 |   15518 |     (null) |
    | BOM-050751 | 0124889 |     (null) |
    | BOM-050751 |   14422 |     (null) |
    | BOM-050751 |   30010 |     (null) |
    | BOM-050751 |   74674 | BOM-050752 |
    | BOM-050752 |   16340 |     (null) |
    | BOM-050752 |   10077 |     (null) |
    | BOM-050752 |   74673 | BOM-050753 |
    | BOM-050753 |   11280 |     (null) |
    | BOM-050753 |   12066 |     (null) |
    | BOM-050753 |   74672 | BOM-050754 |
    | BOM-050754 |   89213 |     (null) |



  [1]: http://sqlfiddle.com/#!3/cf2d6/10

  [2]: http://sqlfiddle.com/#!3/cf2d6/10/0

  [3]: http://sqlfiddle.com/#!3/cf2d6/10/1

Open in new window

0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 39940215
Hi tdisher,

Below is a recursive SQL that seems to generate the correct results.  I comes with some explanation.  :)

The TreeView in your example has 5 lines in bold.  These are the sub-assemblies that have their own sub-assemblies and should be treated differently from the items that don't have their own sub-assemblies.  

Within the query, BOMVersion needs to be joined to a part description to determine if that part has sub-assemblies.  Intuitively, if you use an outer join, the resulting rows are all sub-assemblies for the part.  The columns derived from BOMVersion will be non-NULL if the part contains one or more sub-assemblies.

But recursive SQL generally doesn't allow outer joins, and an inner join filters out all of the rows that don't have sub-assemblies.

With that as a premise, the target is to then recursively generate a row for all items with sub-assemblies, then join that back to BOM where the Cartesian product will generate all of the desired rows.

Whew....

The query below will generate 5 rows, one for each of the critical items.

WITH BOMcte (BVbomID, BVItemId, BMBomId, BMItemId, NItemId, NBomId, BomLevel)
AS
(
    SELECT cast (NULL as varchar(20)), cast (NULL as integer), 
           cast (NULL as varchar(20)), cast (NULL as integer), bv.ItemId, bv.BomId, 1
	FROM dbo.BOMVERSION bv
	WHERE bv.BOMID = 'BOM-145100'

 UNION ALL
 
	SELECT cast (NBOMId as varchar (20)), cast (NItemId as integer), 
	       cast (bom.BomID as varchar (20)), cast (bom.ItemId as integer), 
	       bv2.*, BomLevel+1
	FROM BOMcte
	INNER JOIN dbo.BOM
	  ON BOMcte.NBOMid = bom.BOMid
	INNER JOIN dbo.BOMversion bv2
	  ON BOM.ItemId = bv2.Itemid
    WHERE BomLevel < 10
)
SELECT *
FROM BOMcte;

Open in new window


The result is:

BVbomID     BVItemId  BMBomId     BMItemId  NItemId  NBomId      BomLevel
NULL        NULL      NULL        NULL      74671    BOM-145100  1
BOM-145100  74671     BOM-145100  74675     74675    BOM-050751  2
BOM-050751  74675     BOM-050751  74674     74674    BOM-050752  3
BOM-050752  74674     BOM-050752  74673     74673    BOM-050753  4
BOM-050753  74673     BOM-050753  74672     74672    BOM-050754  5

Open in new window


The first 4 columns are "fluff".  Integral to generating the rows, but you don't care about them in your result set.

Now it's a simple matter to join BOM back to those results to get the full line item detail:

WITH BOMcte (BVbomID, BVItemId, BMBomId, BMItemId, NItemId, NBomId, BomLevel)
AS
(
    SELECT cast (NULL as varchar(20)), cast (NULL as integer), 
           cast (NULL as varchar(20)), cast (NULL as integer), bv.ItemId, bv.BomId, 1
	FROM dbo.BOMVERSION bv
	WHERE bv.BOMID = 'BOM-145100'

 UNION ALL
 
	SELECT cast (NBOMId as varchar (20)), cast (NItemId as integer), 
	       cast (bom.BomID as varchar (20)), cast (bom.ItemId as integer), 
	       bv2.*, BomLevel+1
	FROM BOMcte
	INNER JOIN dbo.BOM
	  ON BOMcte.NBOMid = bom.BOMid
	INNER JOIN dbo.BOMversion bv2
	  ON BOM.ItemId = bv2.Itemid
    WHERE BomLevel < 10
)
SELECT *
FROM BOMcte
LEFT JOIN BOM
  on BOMcte.NBomId = BOM.BOMId
ORDER BY bomLevel;

Open in new window


BVbomID     BVItemId  BMBomId     BMItemId  NItemId  NBomId      BomLevel  ITEMID  BOMID
NULL        NULL      NULL        NULL      74671    BOM-145100  1         13089   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         12382   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         12386   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         12375   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         0107462 BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         10872   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         0107487 BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         10610   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         12323   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         11601   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         10741   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         0128770 BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         87701   BOM-145100
NULL        NULL      NULL        NULL      74671    BOM-145100  1         74675   BOM-145100
BOM-145100  74671     BOM-145100  74675     74675    BOM-050751  2         15518   BOM-050751
BOM-145100  74671     BOM-145100  74675     74675    BOM-050751  2         0124889 BOM-050751
BOM-145100  74671     BOM-145100  74675     74675    BOM-050751  2         14422   BOM-050751
BOM-145100  74671     BOM-145100  74675     74675    BOM-050751  2         30010   BOM-050751
BOM-145100  74671     BOM-145100  74675     74675    BOM-050751  2         74674   BOM-050751
BOM-050751  74675     BOM-050751  74674     74674    BOM-050752  3         16340   BOM-050752
BOM-050751  74675     BOM-050751  74674     74674    BOM-050752  3         10077   BOM-050752
BOM-050751  74675     BOM-050751  74674     74674    BOM-050752  3         74673   BOM-050752
BOM-050752  74674     BOM-050752  74673     74673    BOM-050753  4         11280   BOM-050753
BOM-050752  74674     BOM-050752  74673     74673    BOM-050753  4         12066   BOM-050753
BOM-050752  74674     BOM-050752  74673     74673    BOM-050753  4         74672   BOM-050753
BOM-050753  74673     BOM-050753  74672     74672    BOM-050754  5         89213   BOM-050754

Open in new window


You probably need only the last 3 columns,


Good Luck,
Kent
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39942414
Hi Paul,

I saw the question, left my browser up overnight, and answered it the next day thinking that it hadn't had a response.  Didn't mean to come in behind you that way.


Kent
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39944513
Kent; no problem. You provide much more than I do anyway :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now