set term!!
create procedure inimanager7returns(mtid int, iamount numeric(9,2), iprice numeric(9,2),oamount numeric(9,2), idate date,desid int)
as
declare VARIABLE imtid int;
declare VARIABLE inamount numeric(9,2);
declare VARIABLE inprice numeric(9,2);
declare VARIABLE indate date;
declare VARIABLE idesid int;
declare VARIABLE omtid int;
declare VARIABLE outmount numeric(9,2);
declare VARIABLE TheoryCount numeric(9,2);
declare VARIABLE outdate date;
declare VARIABLE odesid int;
begin
for select id from matrec into:mid do
begin
for select id from storeroom into:roomid do
begin
for select did.mtid imtid, sum(did.Amount) inamount,sum(did.price)inprice,d.createtime indate,d.desid idesid from document d join DocInDetail did on d.id =did.DcID and d.DcTId <> 1
where createtime>'2009-1-1' and createtime<'2009-2-2' and mtid=:mid and desid=:roomid group by d.createtime,did.Mtid,d.DesID
into :imtid,:inamount, :inprice, :indate, :idesid do for select dod.mtid omtid, sum(dod.Amount) outamount, sum(dod.TheoryOut) TheoryCount,d.createtime outdate,d.desid odesid from document d join DocOutDetail dod on d.id = dod.DcID
and d.DcTId <> 1 where createtime>'2009-1-1' and createtime<'2009-2-2' and mtid=:mid and desid=:roomid group by d.createtime,dod.mtid,d.desid
into :omtid, :outmount, :TheoryCount, :outdate, :odesid do if ((imtid is not null) and (omtid is null)) then
begin
matid=:mid;
iamount=:inamount;
iprice=:inprice;
oamount=0;
idate=:indate;
desid=:roomid;
end
if((imtid is null) and (omtid is not null)) then
begin
matid=:mid;
inamount=0;
inprice=0;
if (outmount is null) then
outmount=0;
if (TheoryCount is null) then
TheoryCount=0;
oamount=:outmount+ThoryCount;
idate=:outdate;
desid=:roomid;
end
if((imtid is not null) and (omtid is not null)) then
begin
matid=:mid;
iamount=:inamount;
iprice=:inprice;
if (outmount is null) then
outmount=0;
if (TheoryCount is null) then
TheoryCount=0;
oamount=:outmount+ThoryCount;
idate=:indate;
desid=:roomid;
end
suspend;
end
end exit;
end!!
set term;提示的错误是Column unknown,在存储过程中我不想吧空置返回所以没有处理,不知道对不对,大家帮我看看哪里有错误,谢谢大家!
create procedure inimanager7returns(mtid int, iamount numeric(9,2), iprice numeric(9,2),oamount numeric(9,2), idate date,desid int)
as
declare VARIABLE imtid int;
declare VARIABLE inamount numeric(9,2);
declare VARIABLE inprice numeric(9,2);
declare VARIABLE indate date;
declare VARIABLE idesid int;
declare VARIABLE omtid int;
declare VARIABLE outmount numeric(9,2);
declare VARIABLE TheoryCount numeric(9,2);
declare VARIABLE outdate date;
declare VARIABLE odesid int;
begin
for select id from matrec into:mid do
begin
for select id from storeroom into:roomid do
begin
for select did.mtid imtid, sum(did.Amount) inamount,sum(did.price)inprice,d.createtime indate,d.desid idesid from document d join DocInDetail did on d.id =did.DcID and d.DcTId <> 1
where createtime>'2009-1-1' and createtime<'2009-2-2' and mtid=:mid and desid=:roomid group by d.createtime,did.Mtid,d.DesID
into :imtid,:inamount, :inprice, :indate, :idesid do for select dod.mtid omtid, sum(dod.Amount) outamount, sum(dod.TheoryOut) TheoryCount,d.createtime outdate,d.desid odesid from document d join DocOutDetail dod on d.id = dod.DcID
and d.DcTId <> 1 where createtime>'2009-1-1' and createtime<'2009-2-2' and mtid=:mid and desid=:roomid group by d.createtime,dod.mtid,d.desid
into :omtid, :outmount, :TheoryCount, :outdate, :odesid do if ((imtid is not null) and (omtid is null)) then
begin
matid=:mid;
iamount=:inamount;
iprice=:inprice;
oamount=0;
idate=:indate;
desid=:roomid;
end
if((imtid is null) and (omtid is not null)) then
begin
matid=:mid;
inamount=0;
inprice=0;
if (outmount is null) then
outmount=0;
if (TheoryCount is null) then
TheoryCount=0;
oamount=:outmount+ThoryCount;
idate=:outdate;
desid=:roomid;
end
if((imtid is not null) and (omtid is not null)) then
begin
matid=:mid;
iamount=:inamount;
iprice=:inprice;
if (outmount is null) then
outmount=0;
if (TheoryCount is null) then
TheoryCount=0;
oamount=:outmount+ThoryCount;
idate=:indate;
desid=:roomid;
end
suspend;
end
end exit;
end!!
set term;提示的错误是Column unknown,在存储过程中我不想吧空置返回所以没有处理,不知道对不对,大家帮我看看哪里有错误,谢谢大家!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货