今天在中间库执行两个远程数据库之间的数据传输时遇到ORA-02070: 数据库 DB 不支持此上下文中的 subqueries错误；
pl/sql语句如下:
declare
v_datenow varchar2(10) :=to_char(sysdate,'yyyy-mm');
begin
INSERT INTO TE@WH.NET
SELECT V_DATENOW as nowdate,(SELECT MD FROM ML@dblink3.net,MF@dblink3.NET WHERE MD=MCD AND ME=SE) AS LCD
,SE
,(SELECT ME FROM ML@dblink3.NET WHERE ME=SE) AS ME
,SUM(NVL(ST,0)) AS ST
FROM SL@dblink3.NET
WHERE SC=0
AND EXISTS (SELECT 1 FROM ML@dblink3.NET WHERE MG='A' AND ME=SE)
GROUP BY SE
UNION ALL
SELECT V_DATENOW,(SELECT MD FROM ML@dblink3.NET,MF@dblink3.NET WHERE MD=MCD AND ME=SE) AS LCD
,SE
,(SELECT ME FROM ML@dblink3.NET WHERE ME=SE) AS ME
,SUM(NVL(ST,0)) AS ST
FROM SL@dblink3.NET
WHERE SC is null
AND EXISTS (SELECT 1 FROM ML@dblink3.NET WHERE MG='A' AND ME=SE)
GROUP BY SE;
commit;
end;
修改成如下代码后解决
declare
v_datenow varchar2(10) :=to_char(sysdate,'yyyy-mm');
begin
INSERT INTO TE@WH.NET
with T as (SELECT V_DATENOW,(SELECT MD FROM ML@dblink3.NET,MF@dblink3.NET WHERE MD=MCD AND ME=SE) AS LCD
,SUBS_MCODE
,(SELECT ME FROM ML@dblink3.NET WHERE ME=SE) AS ME
,SUM(NVL(ST,0)) AS ST
FROM SL@dblink3.NET
WHERE SC=0
AND EXISTS (SELECT 1 FROM ML@dblink3.NET WHERE MG='A' AND ME=SE)
GROUP BY SE
UNION ALL
SELECT V_DATENOW,(SELECT MD FROM ML@dblink3.NET,MF@dblink3.NET WHERE MD=MCD AND ME=SE) AS LCD
,SUBS_MCODE
,(SELECT ME FROM ML@dblink3.NET WHERE ME=SE) AS ME
,SUM(NVL(ST,0)) AS ST
FROM SL@dblink3.NET
WHERE SC is null
AND EXISTS (SELECT 1 FROM ML@dblink3.NET WHERE MG='A' AND ME=SE)
GROUP BY SE)
select * from T;
commit;
end;
区别就是用with T as 将查询语句转换成一个临时内存表
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/187805/viewspace-759934/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
