BOM recursive query-CodePudding

Table 1=BomMaster
Fields: ID, itemcod
The itemcode here refers to the number of mother material;
Table 2=BomComponent
Fields: bommaster, itemcode
In this case, the itemcode child a materials issue;
BomMaster. ID and BomComponent. BomMaster association;
How to do a recursive query BOM structure

CodePudding user response:

Asked this question, the other people give me the code, do you want,

CodePudding user response:

 USE [kji] 
GO 
/* * * * * * Object: UserDefinedFunction [dbo] [f_cid] Script Date: 11/14/2013 16:52:13 * * * * * */
The SET ANSI_NULLS ON 
GO 
The SET QUOTED_IDENTIFIER ON 
GO 

ALTER the function [dbo] [f_cid] 
(
@ BOMNO varchar (100), 
@ liqty NUMERIC (7, 2) 
) 
Returns the @ t_level table 
(
P_CODE varchar (100), 
CODE varchar (100), 
CBDESC nvarchar (100), 
QTY_NEED numeric (7, 2), 
WASTAGE numeric (7, 2), 
The UNIT nvarchar (20), 
- SHS numeric (7, 2), 
LIQYT numeric (10, 3), 
Level int, level1 varchar (8000) 
) 
As 
The begin 
Declare @ level int 
Declare @ level1 int 
The set @ level=1 
The set @ level1='0' 
Insert into @ t_level select P_CODE, CODE, CBDESC, QTY_NEED, (100) WASTAGE/WASTAGE, UNIT, 
Between ((CAST (@ liqty AS BIGINT) + (@ liqty * (WASTAGE/100))) * QTY_NEED) LIQYT, @ level, @ level1 
The from BOMT where BOMT. BOMNO=@ BOMNO 
While @ @ ROWCOUNT & gt; 0 
The begin 
The set @ level=@ level + 1 
Insert into @ t_level select Amy polumbo _CODE, a.C ODE, a.C BDESC, a.Q TY_NEED, (aleem walji ASTAGE + b.W ASTAGE), a.U NIT, 
Between ((cast (b.L IQYT as bigint) + between (cast (b.L IQYT as bigint) * (aleem walji ASTAGE/100))) * a.Q TY_NEED) LIQYT, @ level, 
Case the when (select count (*) from BOMT c where Amy polumbo _CODE=c.P _CODE and Arthur c. ODE> A.C ODE and Arthur c. ODE<> @ BOMNO)=0 
Then b.l evel1 + '- 000 
The else b.l evel1 + '-' (' 00 '+ + right ltrim ((select count (*) from BOMT c where Amy polumbo _CODE=c.P _CODE and Arthur c. ODE> A.C ODE and Arthur c. ODE<> @ BOMNO)), 3) 
End 
The from BOMT a, @ t_level b 
Where Amy polumbo _CODE=biggest ODE and b.l evel=@ level 1 and a.C ODE<> @ BOMNO 
End 
Return 
End

CodePudding user response:

And this is a question of SQL best in SQL version

CodePudding user response:

Sql2005 above, can be used with the query tree

CodePudding user response:

Why the two tables, generally in a table