表1=BomMaster
字段:ID,itemcod
这里的itemcode指母件的料号;
表2=BomComponent
字段:bommaster,itemcode
这里的itemcode指子件的料号;
BomMaster.ID与BomComponent.bommaster关联;
如何做递归BOM结构查询
字段:ID,itemcod
这里的itemcode指母件的料号;
表2=BomComponent
字段:bommaster,itemcode
这里的itemcode指子件的料号;
BomMaster.ID与BomComponent.bommaster关联;
如何做递归BOM结构查询
GO
/****** Object: UserDefinedFunction [dbo].[f_cid] Script Date: 11/14/2013 16:52:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER function [dbo].[f_cid]
(
@BOMNO varchar(100),
@liqty NUMERIC(7,2)
)
returns @t_level table
(
P_CODE varchar(100),
CODE varchar(100),
CBDESC nvarchar(100),
QTY_NEED numeric(7,2),
WASTAGE numeric(7,2),
UNIT nvarchar(20),
--SHS numeric(7,2),
LIQYT numeric(10,3),
level int,level1 varchar(8000)
)
as
begin
declare @level int
declare @level1 int
set @level = 1
set @level1 = '0'
insert into @t_level select P_CODE,CODE,CBDESC,QTY_NEED,(WASTAGE/100)WASTAGE,UNIT,
ceiling((CAST(@liqty AS BIGINT)+(@liqty*(WASTAGE/100)))*QTY_NEED)LIQYT,@level,@level1
from BOMT where BOMT.BOMNO=@BOMNO
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.P_CODE,a.CODE,a.CBDESC,a.QTY_NEED,(a.WASTAGE+b.WASTAGE),a.UNIT,
ceiling((cast(b.LIQYT as bigint) + ceiling(cast (b.LIQYT as bigint)*(a.WASTAGE/100)))*a.QTY_NEED)LIQYT,@level,
case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
then b.level1+'-000'
else b.level1+'-'+right('00'+ltrim((select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)),3)
end
from BOMT a , @t_level b
where a.P_CODE = b.CODE and b.level = @level - 1 and a.CODE<>@BOMNO
end
return
end