Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Manufacturing & Supply Chain

User login

Frontpage Sponsor

Main

Google search


Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
18%
200 - 500 GB
26%
500 - 800 GB
3%
800 - 1200 GB
9%
1200 - 1500 GB
12%
1500 - 2000 GB
12%
> 2000 GB
21%
Total votes: 34

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 1st October 2018, 12:11
Thorsten1981 Thorsten1981 is offline
Newbie
 
Join Date: Aug 2013
Posts: 1
Thorsten1981 is on a distinguished road
Baan: Infor ERP LN 2.5 - DB: MS SQL Server - OS: Windows 7 64bit
Unhappy BOM explosion with SQL-CTE-Stament
Baan: ERP LN 6.1 FP8 (Infor ERP 10.2)

Hi there,

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:

https://www.essentialsql.com/recursive-ctes-explained/

which is saying that the following syntax should be used:

Quote:
WITH cte_name (column1, column2, …)
AS
(
cte_query_definition -- Anchor member
UNION ALL
cte_query_definition -- Recursive member; references cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
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.

Code:
	   WITH cte_BOM (Mainitem,ProductLevel,Subitem, Sort )
AS  (SELECT P.t_mitm,
			1,
			p.t_sitm,
			            CAST (P.t_mitm AS VARCHAR (100))
     FROM   [dbo].[ttibom010100]  AS P where p.t_mitm 
                        UNION ALL
     SELECT bom.t_mitm,
            cte_BOM.ProductLevel,
			cte_BOM.Item,
			CAST (cte_BOM.Sort+ '\' + cte_bom.Item AS VARCHAR (100))

     FROM   cte_BOM 
            INNER JOIN [dbo].[ttibom010100] AS BOM
            ON BOM.t_sitm = cte_BOM.Item
                        )
SELECT Mainitem,  
         Subitem,
         ProductLevel,
         Sort
FROM     cte_BOM  
option (maxrecursion 0)

Does someone know, how I can get the correct structure out of our BOM?

Thank you in advance.

Thorsten
Reply With Quote
Sponsored Links
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Standard BOM Explosion DLL (multi level) VishalMistry Tools Development 2 24th June 2016 20:42
What is the function to do a BOM explosion in V BaanDeveloper Tools Development 0 28th February 2013 18:36
SQL query for BOM batmush Tools Development 9 19th May 2009 14:31
Strange error while importing tables: Can not create index 9999 eppesuiG Tools Administration & Installation 8 17th March 2008 05:35
SQL Server 7.0 SP4 + BaanERP compatiblity Jabran Tools Administration & Installation 1 4th October 2004 12:49


All times are GMT +2. The time now is 01:55.


©2001-2018 - Baanboard.com - Baanforums.com