Recursive CTE Multiplying Quantities down the tree

  • 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

  • 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".

  • 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]

  • 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

  • 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]

  • 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.

  • 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

  • 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]

  • 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