bom表展开问题？

[homel]

create  function f_Bom(@ItemIDs  varchar(8000),
                       @num int )---要生产的数量
returns @t table(itemid  bigint,childid bigint,nums int,need_qty int,levels int)
as 
begin
  declare @level int
  declare @need_qty int
  set @level=1
  insert @t select a.p_id,a.childID,round(@num*(1-a.waste_rate),0),round(amount*(1-a.waste_rate),0),@level
  from item_bom a,item b
  where a.p_id=b.[id]
  AND CHARINDEX(','+RTRIM(a.p_id)+',',','+@ItemIDs+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
begin 
 set @level=@level+1
 insert @t select a.itemid,b.childid,round(@num*(1-b.waste_rate),0),round(a.need_qty*@num,0),@level
 from @t a,item_bom b,item c
 where a.itemid=b.p_id
 and  b.p_id=c.[id]
 and a.levels=@level-1
END 
RETURN 
END 

调用函数：

select a.itemid,item_id=b.id,
       a.childid,--child_id=c.id,
       a.nums,a.need_qty,a.levels
from f_bom('75',10) a,item b,item c
where  a.itemid=b.id
and a.childid=c.id
order by a.itemid,a.levels,a.childid

总是提醒：Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
***查了相关资料，说是因为函数中返回的@@identity引起的，请高手们给指点一下，谢谢啦

[华夏小卒]

好像是什么数据类型的问题

[--小F--]

?哪里的数据类型出问题了?

[dawugui]

最好给出完整的表结构,测试数据,计算方法和正确结果.

http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[Herb2]

Arithmetic overflow error converting expression to data type int.


将表达式转换成INT类型时产生溢出错误。
也就是你的计算过程有数据超出了INT类型的范围。

你是不是应该定义的你的变量为：LONG
或者检查你的程序，有那么大的数据吗？？？

[homel]

不好意思，谢谢大家：
一下为表结构：

table name:item
id    unit   
75     1
76     2
77     1
78     1
** id为自增长型

table name：item_bom 
p_id  childid    amount waste_rate
75      76        1        0
75      77        1        0
76      78        100      0

**p_id 关联item表中的id,childid也关联item表中的id
希望得到如下结果(nums为要生产的数量）：
p_id   childid  nums   need_qty 
75      76       10      10
75      77       10      10
75      78       10      1000

[dawugui]

[Quote=引用 5 楼 homel 的回复:]
不好意思，谢谢大家：
一下为表结构：
SQL codetable name:item
id unit751762771781** id为自增长型table name：item_bom
p_id childid amount waste_rate75761075771076781000**p_id 关联item表中的id,childid也关联item表中的id
希望得到如下结果(nums为要生产的数量）：
p_id childid nums need_qty75761010757710107578101000
[/Quote]

参考:

--BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION  ALL  SELECT 2,N'B产品',0.02
UNION  ALL  SELECT 3,N'C产品',0.10
UNION  ALL  SELECT 4,N'D配件',0.15
UNION  ALL  SELECT 5,N'E物料',0.03
UNION  ALL  SELECT 6,N'F物料',0.01
UNION  ALL  SELECT 7,N'G配件',0.02

CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION  ALL SELECT 1,7   --A产品由D配件和G配件组成
UNION  ALL SELECT 2,1
UNION  ALL SELECT 2,6
UNION  ALL SELECT 2,7   --B产品由F物料及G配件组成
UNION  ALL SELECT 4,5
UNION  ALL SELECT 4,6    --D配件由F物料组成
UNION  ALL SELECT 3,2
UNION  ALL SELECT 3,1    --C产品由A产品和B产品组成
GO

CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num   int          --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
    FROM Bom a,Item b
    WHERE a.ChildId=b.ID
        AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
    WHILE @@ROWCOUNT>0 and @Level<140
    BEGIN
        SET @Level=@Level+1
        INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
        FROM @t a,Bom b,Item c
        WHERE a.ChildId=b.ItemID
            AND b.ChildId=c.ID
            AND a.Level=@Level-1
    END
    RETURN
END
GO

--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
    a.ChildId,ChildName=c.Name,
    a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
    AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId

/*
ItemID      ItemName   ChildId     ChildName  Nums        Level
----------- ---------- ----------- ---------- ----------- -----------
1           A产品        4           D配件        12          1
1           A产品        7           G配件        10          1
1           A产品        5           E物料        12          2
1           A产品        6           F物料        12          2
2           B产品        1           A产品        10          1
2           B产品        6           F物料        10          1
2           B产品        7           G配件        10          1
2           B产品        4           D配件        12          2
2           B产品        7           G配件        10          2
2           B产品        5           E物料        12          3
2           B产品        6           F物料        12          3
3           C产品        1           A产品        10          1
3           C产品        2           B产品        10          1
3           C产品        1           A产品        10          2
3           C产品        4           D配件        12          2
3           C产品        6           F物料        10          2
3           C产品        7           G配件        10          2
3           C产品        7           G配件        10          2
3           C产品        4           D配件        12          3
3           C产品        5           E物料        12          3
3           C产品        6           F物料        12          3
3           C产品        7           G配件        10          3
3           C产品        5           E物料        12          4
3           C产品        6           F物料        12          4

(24 row(s) affected)


*/
drop table item
drop table bom
drop function f_Bom

[homel]

[Quote=引用 6 楼 dawugui 的回复:]
引用 5 楼 homel 的回复:
不好意思，谢谢大家：
一下为表结构：
SQL codetable name:item
id    unit751762771781** id为自增长型table name：item_bom
p_id  childid    amount waste_rate75761075771076781000**p_id 关联item表中的id,childid也关联item表中的id
希望得到如下结果(nums为要生产的数量）：
p_id  childid  nums  need_qty75761010757710107578101000


参考:

SQL code--BOM算法
--产品配件清单查询示例(邹建)CREATETABLE Item(IDint,Namevarchar(10),Wastdecimal(2,2))INSERT ItemSELECT1,N'A产品',0.01UNIONALLSELECT2,N'B产品',0.02UNIONALLSELECT3,N'C产品',0.10UNIONALLSELECT4,N'D配件',0.15UNIONALLSELECT5,N'E物料',0.03UNIONALLSELECT6,N'F物料',0.01UNIONALLSELECT7,N'G配件',0.02CREATETABLE Bom(ItemIDint,ChildIdint)INSERT BomSELECT1,4UNIONALLSELECT1,7--A产品由D配件和G配件组成UNIONALLSELECT2,1UNIONALLSELECT2,6UNIONALLSELECT2,7--B产品由F物料及G配件组成UNIONALLSELECT4,5UNIONALLSELECT4,6--D配件由F物料组成UNIONALLSELECT3,2UNIONALLSELECT3,1--C产品由A产品和B产品组成GOCREATEFUNCTION f_Bom(@ItemIDsvarchar(1000),--要查询物料清单及生产量的产品编号列表(逗号分隔)@Numint--要生产的数量)RETURNS@tTABLE(ItemIDint,ChildIdint,Numsint,Levelint)ASBEGINDECLARE@LevelintSET@Level=1INSERT@tSELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@LevelFROM Bom a,Item bWHERE a.ChildId=b.IDANDCHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0WHILE@@ROWCOUNT>0and@Level<140BEGINSET@Level=@Level+1INSERT@tSELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@LevelFROM@t a,Bom b,Item cWHERE a.ChildId=b.ItemIDAND b.ChildId=c.IDAND a.Level=@Level-1ENDRETURNENDGO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.LevelFROM f_Bom('1,2,3',10) a,Item b,Item cWHERE a.ItemID=b.IDAND a.ChildId=c.IDORDERBY a.ItemID,a.Level,a.ChildId/*
ItemID ItemName ChildId ChildName Nums Level
----------- ---------- ----------- ---------- ----------- -----------
1 A产品 4 D配件 12 1
1 A产品 7 G配件 10 1
1 A产品 5 E物料 12 2
1 A产品 6 F物料 12 2
2 B产品 1 A产品 10 1
2 B产品 6 F物料 10 1
2 B产品 7 G配件 10 1
2 B产品 4 D配件 12 2
2 B产品 7 G配件 10 2
2 B产品 5 E物料 12 3
2 B产品 6 F物料 12 3
3 C产品 1 A产品 10 1
3 C产品 2 B产品 10 1
3 C产品 1 A产品 10 2
3 C产品 4 D配件 12 2
3 C产品 6 F物料 10 2
3 C产品 7 G配件 10 2
3 C产品 7 G配件 10 2
3 C产品 4 D配件 12 3
3 C产品 5 E物料 12 3
3 C产品 6 F物料 12 3
3 C产品 7 G配件 10 3
3 C产品 5 E物料 12 4
3 C产品 6 F物料 12 4

(24 row(s) affected)*/droptable itemdroptable bomdropfunction f_Bom
[/Quote]


谢谢ls的，我就是参考这个来做的，但是总是会提醒截断问题。。
我再找找错误。。。