ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 解决ORA-02070: 数据库DB 不支持此上下文中的 subqueries

解决ORA-02070: 数据库DB 不支持此上下文中的 subqueries

原创 Linux操作系统 作者:wghxwl12 时间:2013-05-07 11:41:49 0 删除 编辑
今天在中间库执行两个远程数据库之间的数据传输时遇到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博客 ” ,链接:http://blog.itpub.net/187805/viewspace-759934/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-14

  • 博文量
    155
  • 访问量
    564790