April 16, 2009 at 12:30 pm
Hi,
We have a BOM table containing material items grouped by the BOMs containing them. A wrinkle is that a BOM can also contain phantoms. A phantom is basically a list of materials. Phantoms enable the system to reduce the number of lines a BOM would have to contain to properly specify a production order. Each BOM line contains a field indicating whether or not it’s a phantom. When the production order is created, instead of containing the phantoms, it contains the items within them. This arrangement is complicated however by the fact that phantoms can contain other phantoms in tree-type fashion; in other words, these lists can contain other lists. A recursive CTE works well here except, it appears, when you want to multiply the quantities down. So, if a BOM contains a phantom with a quantity of 5 and all of the line quantities in the phantom are 5, the resulting production order should contain 25 (5X5) of each of the phantom's line items. If that phantom also contains another phantom with line quantities of 5, the production order should have 125 (25X5) of the items on the second level phantom. This should continue on down to the end of all of the tree’s branches.
I am able to use a recursive CTE for everything except getting these quantities to multiply down. Does anyone know how to do this or if this is even possible with recursive CTEs?
Thanks,
Mike
April 16, 2009 at 1:33 pm
Below is the error message I get when I try to do the multiplication:
Types don't match between the anchor and the recursive part in column "Qty" of recursive query "BOMCTE".
April 16, 2009 at 3:08 pm
Don't multiply the Order Quantity over the Phantoms, only apply that multiplication to the terminal line quantities.
As for your error, that's an entirely different matter. You will have to post your query code and the CREATE TABLE statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 4:48 pm
Hi RBarry,
Thanks for your reply. I tried what you are suggesting and the quantities at the end of the tree were too low. The tree can be 20+ deep and everything must multiply down. So a 5x5x5x10 tree condition must end up with 1250 at the end of that branch.
Thanks again.
Mike
April 16, 2009 at 5:00 pm
I think that I would have to see the query to understand what you were getting at. Is the error your only problem with it then?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 5:12 pm
That is my only error. I will try to simplify the query so that it is easier for you to see what I'm trying to achieve.
I am uploading a zip of a bitmap that illustrates the structure of the product tree and how the quantities should play out. Line items on the BOMs have icons. The ghost icon indicates a phantom. Let me know if I need to clarify any of it.
April 16, 2009 at 9:33 pm
RBarry,
I believe I solved the problem but I don’t know why it works. It appears that the order of calculations has an effect. In the anchor part of the query, I originally had the multiplication for the ParentQty occur instead for the ExtendedQty. While I was simplifying the query in order to post it, I tried moving the multiplication over the ParentQty field and then ran it. To my pleasant surprise the ExtendedQty field looked right in the record set for all rows except where the tree level was equal to 1. I applied a case to correct those rows and put the result in the PdOLineQty field; but this doesn’t quite feel right. I’m going to add another level to the tree to see what happens. The SQL is below. The PdOLineQty is the field I’m interested in. The resulting recordset is shown in the attached zipped bitmap.
Thanks,
Mike
-- Table Explanations:
-- OITT is the BOM header table
-- ITT1 is the BOM line table
-- OITM is the Item Master table; this is where the phantom indicator resides.
--
-- The BOMQty is stored in the header and it simply means that the when
-- a production order based on that BOM is completed, the BOMQty is the
-- number of items that have been produced. It is used as a divisor
-- when calculating phantom tree quantities.
WITH SimpleRecursive(Father, Code, ChildNum, BOMQty, U_NBS_MatlQty, Phantom,
TreeLevel, TreePath, ParentQty, ExtendedQty)
AS
(
SELECT Father, ITT1.Code, ChildNum, OITT.Qauntity AS BOMQty, U_NBS_MatlQty, Phantom, 1 AS TreeLevel,
CAST('0.' + RIGHT('00000' + CAST(ChildNum AS NVARCHAR(5)), 5) + '.' AS NVARCHAR(MAX)) AS TreePath,
U_NBS_MatlQty * OITT.Qauntity AS ParentQty, OITT.Qauntity AS ExtendedQty
FROM ITT1 JOIN OITM ON OITM.ItemCode = ITT1.Code
JOIN OITT ON OITT.Code = ITT1.Father
WHERE Father IN ('PH4')
UNION ALL
SELECT A.Father, A.Code, A.ChildNum, OITT.Qauntity AS BOMQty, A.U_NBS_MatlQty, OITM.Phantom, B.TreeLevel + 1 AS TreeLevel,
CAST(B.TreePath + RIGHT('00000' + CAST(A.ChildNum AS NVARCHAR(5)), 5) + '.' AS NVARCHAR(MAX)) AS TreePath,
A.U_NBS_MatlQty * B.U_NBS_MatlQty AS ParentQty, B.U_NBS_MatlQty AS ExtendedQty
FROM ITT1 A JOIN OITM ON OITM.ItemCode = A.Code
JOIN OITT ON OITT.Code = A.Father
JOIN SimpleRecursive B ON B.Code = A.Father
WHERE B.Phantom = 'Y'
)
SELECT SR.Father, SR.Code AS Child, SR.ChildNum, U_NBS_MatlQty AS Qty,
SR.ParentQty, SR.BOMQty * SR.U_NBS_MatlQty * ParentQty AS ExtendedQty,
SR.TreeLevel, SR.TreePath,
CASE TreeLevel
WHEN 1 THEN SR.U_NBS_MatlQty
ELSE (SR.U_NBS_MatlQty * ParentQty) / SR.BOMQty
END AS PdOLineQty
FROM SimpleRecursive SR
WHERE SR.Phantom = 'N'
ORDER BY SR.TreePath
April 16, 2009 at 9:53 pm
This query does not match you error message. I need them to match in order to debug it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 10:33 pm
Here it is:
-- Table Explanations:
-- OITT is the BOM header table
-- ITT1 is the BOM line table
-- OITM is the Item Master table; this is where the phantom indicator resides.
WITH SimpleRecursive(Father, Code, ChildNum, BOMQty, U_NBS_MatlQty, Phantom,
TreeLevel, TreePath, ParentQty, ExtendedQty)
AS
(
SELECT Father, ITT1.Code, ChildNum, OITT.Qauntity AS BOMQty, U_NBS_MatlQty, Phantom, 1 AS TreeLevel,
CAST('0.' + RIGHT('00000' + CAST(ChildNum AS NVARCHAR(5)), 5) + '.' AS NVARCHAR(MAX)) AS TreePath,
U_NBS_MatlQty * OITT.Qauntity AS ParentQty, OITT.Qauntity AS ExtendedQty
FROM ITT1 JOIN OITM ON OITM.ItemCode = ITT1.Code
JOIN OITT ON OITT.Code = ITT1.Father
WHERE Father IN ('PH4')
UNION ALL
SELECT A.Father, A.Code, A.ChildNum, OITT.Qauntity AS BOMQty, A.U_NBS_MatlQty, OITM.Phantom, B.TreeLevel + 1 AS TreeLevel,
CAST(B.TreePath + RIGHT('00000' + CAST(A.ChildNum AS NVARCHAR(5)), 5) + '.' AS NVARCHAR(MAX)) AS TreePath,
A.U_NBS_MatlQty * B.U_NBS_MatlQty AS ParentQty,
A.U_NBS_MatlQty * B.ParentQty AS ExtendedQty
FROM ITT1 A JOIN OITM ON OITM.ItemCode = A.Code
JOIN OITT ON OITT.Code = A.Father
JOIN SimpleRecursive B ON B.Code = A.Father
WHERE B.Phantom = 'Y'
)
SELECT SR.Father, SR.Code AS Child, SR.ChildNum, U_NBS_MatlQty AS Qty,
SR.ParentQty, SR.BOMQty * SR.U_NBS_MatlQty * ParentQty AS ExtendedQty,
SR.TreeLevel, SR.TreePath,
CASE TreeLevel
WHEN 1 THEN SR.U_NBS_MatlQty
ELSE (SR.U_NBS_MatlQty * ParentQty) / SR.BOMQty
END AS PdOLineQty, SR.Phantom
FROM SimpleRecursive SR
WHERE SR.Phantom = 'N'
ORDER BY SR.TreePath
--------------------------------------------------------------
Error:
Msg 240, Level 16, State 1, Line 6
Types don't match between the anchor and the recursive part in column "ExtendedQty" of recursive query "SimpleRecursive".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply