CREATE OR REPLACE PROCEDURE DW.DW_XYW_DDL(rq in varchar2) ASBEGIN delete from dw.dw_ghlx where state_date = rq; commit; insert into dw.dw_ghlx (state_date, count, exch_id, main_prod_id, main_spec_id, prod_id) select state_date, sum(a.count), exch_id, main_prod_id, main_spec_id, prod_id from dw.dw_wxz_xyw_ddl a where state_date = rq group by state_date, exch_id, main_prod_id, main_spec_id, prod_id; commit; end; 这段代码,在SQL里把RQ换成'YYYYMMDD'可以正常运行,写存储过程里就报错,我改了一下,现在报这个错误: Error: ORA-00978: nested group function without GROUP BY ORA-06512: at line 1, Batch 1 Line 1 Col 1; 麻烦高手帮忙看看谢谢~
不好意思 中间那段代码粘错了,应该是这段; delete from dw.dw_wxz_zyyh_ddl where state_date=rq; commit; insert into dw.dw_wxz_zyyh_ddl(state_date,count,exch_id,prod_id,COMM_SERV_SPEC_ID) select to_char(stat_date,'yyyymmdd'),sum(amount),exch_id,prod_id,COMM_SERV_SPEC_ID from JLCNCNEW97.rept_yy_zyyh@new97 where local_net_id=431 and to_char(stat_date,'yyyymmdd')=rq GROUP BY to_char(stat_date,'yyyymmdd'),exch_id,prod_id,COMM_SERV_SPEC_ID;
应该是你把sql赋给一个变量,但sql的返回不知一条所致出错。
delete from dw.dw_ghlx where state_date = rq;
commit;
insert into dw.dw_ghlx
(state_date, count, exch_id, main_prod_id, main_spec_id, prod_id)
select state_date,
sum(a.count),
exch_id,
main_prod_id,
main_spec_id,
prod_id
from dw.dw_wxz_xyw_ddl a
where state_date = rq
group by state_date, exch_id, main_prod_id, main_spec_id, prod_id;
commit;
end;
这段代码,在SQL里把RQ换成'YYYYMMDD'可以正常运行,写存储过程里就报错,我改了一下,现在报这个错误:
Error: ORA-00978: nested group function without GROUP BY
ORA-06512: at line 1, Batch 1 Line 1 Col 1;
麻烦高手帮忙看看谢谢~
delete from dw.dw_wxz_zyyh_ddl
where state_date=rq;
commit;
insert into dw.dw_wxz_zyyh_ddl(state_date,count,exch_id,prod_id,COMM_SERV_SPEC_ID)
select to_char(stat_date,'yyyymmdd'),sum(amount),exch_id,prod_id,COMM_SERV_SPEC_ID
from JLCNCNEW97.rept_yy_zyyh@new97
where local_net_id=431
and to_char(stat_date,'yyyymmdd')=rq
GROUP BY to_char(stat_date,'yyyymmdd'),exch_id,prod_id,COMM_SERV_SPEC_ID;
commit;