from cwgl c left outer join (select hxdnum,sum(fmone) as aa from cwgl0 group by hxdnum) a on c.hxdnum=a.hxdnum left outer join (select hxdnum,sum(fmone) as ddkx from cwgl2 group by hxdnum) b on c.hxdnum=b.hxdnum left outer join (select hxdnum,rate,tsrate from bgd) d on c.hxdnum=d.hxdnum left outer join (select hxdnum,sum(totalmone) as totalmone from zzsfp1 group by hxdnum) e on c.hxdnum=e.hxdnum where c.id=formid'; 修改为: from cwgl c,(select hxdnum,sum(fmone) as aa from cwgl0 group by hxdnum) a,(select hxdnum,sum(fmone) as ddkx from cwgl2 group by hxdnum) b,(select hxdnum,rate,tsrate from bgd) d,(select hxdnum,sum(totalmone) as totalmone from zzsfp1 group by hxdnum) e where c.id=formid' and c.hxdnum=a.hxdnum(+) and c.hxdnum=b.hxdnum(+) and c.hxdnum=d.hxdnum(+) and c.hxdnum=e.hxdnum(+) ;
beckhambobo! 你好! 更改了,还是出现问题
你的oracle是什么版本?9i用支持join连接
create or replace procedure cwgl_tmp(formid in out NUMBER) is cursor t_sor is select c.id,c.fobmone,c.hbf,c.yswhe,c.mll,rate,tsrate,a.aa as sswhe,(a.aa-c.yswhe) as whcy,(a.aa-c.hbf) as whje,(a.aa-c.hbf)*8.265 as ckfpsr,b.ddkx,(a.aa-c.yswhe)*8.265 as srcy,round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2) as cwll,e.totalmone as cwsj,e.totalmone-round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2) as cbcy,round((e.totalmone-round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2))/(1+rate/100)*(1+rate/100-tsrate/100),2) as cbtz,e.totalmone-round((e.totalmone-round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2))/(1+rate/100)*(1+rate/100-tsrate/100),2) as syfks,(a.aa-c.hbf)*mll as jylr from cwgl c left outer join (select hxdnum,sum(fmone) as aa from cwgl0 group by hxdnum) a on c.hxdnum=a.hxdnum left outer join (select hxdnum,sum(fmone) as ddkx from cwgl2 group by hxdnum) b on c.hxdnum=b.hxdnum left outer join (select hxdnum,rate,tsrate from bgd) d on c.hxdnum=d.hxdnum left outer join (select hxdnum,sum(totalmone) as totalmone from zzsfp1 group by hxdnum) e on c.hxdnum=e.hxdnum where c.id=formid; begin for v_sor in t_sor loop insert into cwgl_temp(id,fobmone,hbf,yswhe,mll,rate,tsrate,sswhe,whcy,whje,ckfpsr,ddkx,srcy,cwll,cwsj,cbcy,cbtz,syfks,jylr) values(v_sor.id,v_sor.fobmone,v_sor.hbf,v_sor.yswhe,v_sor.mll,v_sor.rate,v_sor.tsrate,v_sor.sswhe,v_sor.whcy,v_sor.whje,v_sor.ckfpsr,v_sor.ddkx,v_sor.srcy,v_sor.cwll,v_sor.cwsj,v_sor.cbcy,v_sor.cbtz,v_sor.syfks,v_sor.jylr); END LOOP; end cwgl_tmp; /
修改为:
from cwgl c,(select hxdnum,sum(fmone) as aa from cwgl0 group by hxdnum) a,(select hxdnum,sum(fmone) as ddkx from cwgl2 group by hxdnum) b,(select hxdnum,rate,tsrate from bgd) d,(select hxdnum,sum(totalmone) as totalmone from zzsfp1 group by hxdnum) e
where c.id=formid' and c.hxdnum=a.hxdnum(+) and c.hxdnum=b.hxdnum(+) and c.hxdnum=d.hxdnum(+) and c.hxdnum=e.hxdnum(+) ;
更改了,还是出现问题
is
cursor t_sor is
select c.id,c.fobmone,c.hbf,c.yswhe,c.mll,rate,tsrate,a.aa as sswhe,(a.aa-c.yswhe) as whcy,(a.aa-c.hbf) as whje,(a.aa-c.hbf)*8.265 as ckfpsr,b.ddkx,(a.aa-c.yswhe)*8.265 as srcy,round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2) as cwll,e.totalmone as cwsj,e.totalmone-round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2) as cbcy,round((e.totalmone-round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2))/(1+rate/100)*(1+rate/100-tsrate/100),2) as cbtz,e.totalmone-round((e.totalmone-round(((a.aa-c.hbf)*8.265-c.fobmone*c.mll-ddkx)/(1+rate/100-tsrate/100)*(1+rate/100),2))/(1+rate/100)*(1+rate/100-tsrate/100),2) as syfks,(a.aa-c.hbf)*mll as jylr from cwgl c left outer join (select hxdnum,sum(fmone) as aa from cwgl0 group by hxdnum) a on c.hxdnum=a.hxdnum left outer join (select hxdnum,sum(fmone) as ddkx from cwgl2 group by hxdnum) b on c.hxdnum=b.hxdnum left outer join (select hxdnum,rate,tsrate from bgd) d on c.hxdnum=d.hxdnum left outer join (select hxdnum,sum(totalmone) as totalmone from zzsfp1 group by hxdnum) e on c.hxdnum=e.hxdnum where c.id=formid; begin
for v_sor in t_sor loop
insert into cwgl_temp(id,fobmone,hbf,yswhe,mll,rate,tsrate,sswhe,whcy,whje,ckfpsr,ddkx,srcy,cwll,cwsj,cbcy,cbtz,syfks,jylr) values(v_sor.id,v_sor.fobmone,v_sor.hbf,v_sor.yswhe,v_sor.mll,v_sor.rate,v_sor.tsrate,v_sor.sswhe,v_sor.whcy,v_sor.whje,v_sor.ckfpsr,v_sor.ddkx,v_sor.srcy,v_sor.cwll,v_sor.cwsj,v_sor.cbcy,v_sor.cbtz,v_sor.syfks,v_sor.jylr);
END LOOP;
end cwgl_tmp;
/