if would be nice if someone could help me.
I am just trying to get an explosive BOM. This means I want to see the levels of the subproducts within all Bill of materials.
I found this link:
which is saying that the following syntax should be used:
WITH cte_name (column1, column2, …)
cte_query_definition -- Anchor member
cte_query_definition -- Recursive member; references cte_name.
-- Statement using the CTE
In Infor our main table would be [dbo].[ttibom010]
But If try to do this in sql with our database I will not get any plausible result after level 2.
WITH cte_BOM (Mainitem,ProductLevel,Subitem, Sort )
AS (SELECT P.t_mitm,
CAST (P.t_mitm AS VARCHAR (100))
FROM [dbo].[ttibom010100] AS P where p.t_mitm
CAST (cte_BOM.Sort+ '\' + cte_bom.Item AS VARCHAR (100))
INNER JOIN [dbo].[ttibom010100] AS BOM
ON BOM.t_sitm = cte_BOM.Item
option (maxrecursion 0)
Does someone know, how I can get the correct structure out of our BOM?
Thank you in advance.