September 29, 2019 at 4:01 pm
I'm trying to sort out someone's Bill of Materials process that's happening half in Access VBA and half in SQL Server, so if I sound confused, well, I am.
I'm trying to return a "shopping list" of all the components necessary to build everything in the work order. (If you use the AdventureWorks2017 database as a model, just think of the SalesOrderDetail table as a starting point … the salient columns are (SalesOrderID, ProductID, OrderQty)
The way I thought to solve it was to use the Bill of Materials CTE, but join that to this:
SELECT sod.ProductID, sod.OrderQty
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderID = @SalesOrderID
and end up with a "shopping list" of (SubComponentID, SubcomponentQty * sod.OrderQty) for the whole order.
So my question is - in the cteBOM query in the database, they're passing the AssemblyID (end Product ID) into the CTE... Is that what I want to do? And then join that CTE result to my "SalesOrderDetail" table (on ProductID)? The other part I find confusing about that CTE is which of the items returned are at the lowest level (have no subassemblies)?
Feel free to point me to any "Recursive CTEs for Dummies" articles. I won't be offended.
Thanks!
September 29, 2019 at 5:21 pm
Here's my code so far... How do I just return the lowest level components? I feel like I'm missing a join or something somewhere...
Here's my code so far... How do I just return the lowest level components? I feel like I'm missing a join or something somewhere...
DECLARE @SalesOrderID INT = 43660;
WITH cte_BOM (ProductID, Name, Color, Quantity, ProductLevel, ProductAssemblyID, Sort)
AS (SELECT P.ProductID,
CAST (P.Name AS VARCHAR (100)),
P.Color,
CAST (1 AS DECIMAL (8, 2)),
1,
NULL,
CAST (P.Name AS VARCHAR (100))
FROM Production.Product AS P
INNER JOIN
Production.BillOfMaterials AS BOM
ON BOM.ComponentID = P.ProductID
AND BOM.ProductAssemblyID IS NULL
AND (BOM.EndDate IS NULL
OR BOM.EndDate > GETDATE())
UNION ALL
SELECT P.ProductID,
CAST (REPLICATE('|---', cte_BOM.ProductLevel) + P.Name AS VARCHAR (100)),
P.Color,
BOM.PerAssemblyQty,
cte_BOM.ProductLevel + 1,
cte_BOM.ProductID,
CAST (cte_BOM.Sort + '\' + p.Name AS VARCHAR (100))
FROM cte_BOM
INNER JOIN Production.BillOfMaterials AS BOM
ON BOM.ProductAssemblyID = cte_BOM.ProductID
INNER JOIN Production.Product AS P
ON BOM.ComponentID = P.ProductID
AND (BOM.EndDate IS NULL
OR BOM.EndDate > GETDATE())
)
SELECT sod.ProductID,
Name,
Color,
Quantity,
ProductLevel,
ProductAssemblyID,
Sort
FROM cte_BOM INNER JOIN Sales.SalesOrderDetail sod
ON cte_BOM.ProductAssemblyID = sod.ProductID
WHERE ProductLevel > 1
AND sod.SalesOrderID = @SalesOrderID
ORDER BY Sort;
One last thing... if there are items in my SalesOrderDetail table that have some of the same components, would I not have a simple totals query at the end, where I group by ComponentID, ComponentDescription and return the total units required, like SUM(subcomponentQty)?
(I watched Joe Sack's video on Pluralsight, hoping he covered this topic, because he explains things really carefully, but no joy. DRAT!)
Thanks!
Pieter
September 30, 2019 at 5:53 am
FWIW, here's my current code... I *think* it works, but maybe it's just AdventureWorks playing tricks on me.
CREATE PROC PartsSheetForSalesOrder
@SalesOrderID INT
AS
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
SELECT ComponentID, Name, SUM(PerAssemblyQty * sod.OrderQty) AS TotalQty
FROM Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
INNER JOIN Sales.SalesOrderDetail sod
ON sod.ProductID = pr.ProductID
WHERE sod.SalesOrderID = @SalesOrderID
GROUP BY ComponentID, Name
ORDER BY ComponentID, Name;
I think I could leave the EndDate stuff out, but since I found the example here, I just left it in.
September 30, 2019 at 4:16 pm
The part I finally figured out that threw me at first is that you filter what the CTE returns by adding a WHERE clause outside the CTE (seems to painfully obvious now!). I was reading various examples, and some had filters inside the CTE... which was odd. I watched Steve Stedman's video on Common Table Expressions (on youtube). It's 45 minutes long, but really helpful. Highly recommended, because he breaks down how CTEs work, and what recursion is, and why you need to know. Made the stuff make a LOT more sense.
(yeah, this is my journal about learning how to write a CTE and use one. LOL)
September 30, 2019 at 4:16 pm
I would strongly recommend that you don't take this approach SQL is a declarative language in this kind of recursion is based on tree traversals and procedural code. After 30+ years of doing this sort of stuff, I found the nested set model is the best approach. You can Google that technique and find any number of articles on how to do it. If you need more help, I have a chapter in my book on treason hierarchies and SQL. Without explaining it this is what the query that gives you the leaf nodes of the tree structure in the nested set model looks like:
SELECT B.*
FROM Bill_of_Materials AS B
WHERE B.lft + 1 = B.rgt;
Doesn't that look a lot easier than your recursion? Don't you think it'll run an order of magnitude or more faster?
Please post DDL and follow ANSI/ISO standards when asking for help.
September 30, 2019 at 6:06 pm
Live and learn, I guess.
I might have to revisit this and rewrite it when I learn how to do it better. I think part of the challenge is that it's really hard to find an example of something when you don't know the name of that something. So I'll check it out.
I hope my solution is better than the person's who was asking for help - using Access and recordsets/cursors and shuffling data back and forth between SQL Server and Access. My code isn't great, but I hope it's a step in the right direction. =)
I guess nested sets is the next thing to learn?
October 3, 2019 at 12:59 pm
But that will give you all of the nodes and their position in the tree. The OP specifically asked for only the bottom nodes. Some of these will be at level 2, some might be at level 7 or 77
October 3, 2019 at 7:18 pm
Aaron,
is there an easy way to identify those?
Pieter
October 8, 2019 at 5:35 pm
not one that I would call easy 🙂
in the past, I have used STUFF()...FOR XML to build a hierarchical map string (not strictly necessary but helps with debugging) and then taken the last element in the string to get the list of bottom level elements.
I would be sorely tempted to do this with a DO..WHILE loop and build out a #temp_table (writing to disk is generally faster than in-memory processing and you can index it)
Take the top level components and build out a dataset of their sub-components that are not already in the temp table.
Insert the calculated record set into the temp table along with a level reference and their original component
run the query again and if you get more records back, insert them into the temp table. Repeat until the results set is empty.
Now take the results set and find the MAX(component_level) for each top level component and link it back to its level component record. You might find the same sub-components on multiple top level items as a different sub-level.
Now take these bottom level components and SUM(qty) to find your shopping list
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply