數據表

Part_Nom ini_quantity Enter_quantity Exit_quantity
1 Null NULL NULL
G255822A0A NULL NULL NULL
P0025 NULL NULL NULL
G255822A0A 200.00 NULL NULL
P0025 15000.00 NULL NULL
P0025 NULL 25.00 NULL
G255822A0A 25.00 NULL NULL
G255822A0A NULL NULL 1.00

存儲過程﹕
create proc GETCurrent_Depot_Quantity
as
begin transaction
if exists(select * from dbo.sysobjects where id=object_id('[dbo].[CreateCurrent_Depot_Quantity]') and OBJECTPROPERTY(id,'IsUserTable')=1)
drop table CreateCurrent_Depot_Quantity
select Part_Nom,DP_Quantity=sum(ini_quantity),EDN_Quantity=sum(Enter_quantity),EDX_Quantity=sum(Exit_quantity),
-------------------------------------------------------當前庫存數-------------------------------------
case ( sum(ini_quantity))
when NULL
then
case(sum(Enter_quantity))
when NULL
then
NULL
else
sum(Enter_quantity)
end
else
case(sum(Enter_quantity))
when NULL
then
case(sum(Exit_quantity)) when NULL then sum(ini_quantity)
else sum(ini_quantity)-sum(Exit_quantity) end
else
case(sum(Exit_quantity)) when NULL then sum(ini_quantity)+sum(Enter_quantity)
else sum(ini_quantity)+sum(Enter_quantity)-sum(Exit_quantity) end
end
end as Current_quantity
----------------------------------------------------------------------------------------------------------
into CreateCurrent_Depot_Quantity
from DP_current_Quantity
group by Part_Nom
commit transaction

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

按要求的話﹕
Part_Nom='P0025'的Current_quantity應該是15025.00
但現在是null﹐錯在哪里

(其中 ini_quantity為初始數量 Enter_quantity為增加數量 Exit_quantity位減少數量
Current_quantity 位當前數量
Current_quantity= ini_quantity+ Enter_quantity-Exit_quantity)

[此贴子已经被作者于2006-9-14 11:02:43编辑过]