日期:2014-05-17  浏览次数:21997 次

查询时出现ora-00980:同义词转换不再有效
如题,在执行以下查询时出现:ora-00980:同义词转换不再有效 错误提示! 希望大家帮忙解决下!在此先谢谢了!
SQL code

with tmp_a as
(select distinct nvl(m.rdate, n.rdate),
        max(case when m.rdate=n.rdate and m.userlevel = n.userlevel then m.rcnt_2+n.rcnt_2 else m.rcnt_2 end) "总数高级",
        max(case when m.rdate=n.rdate and m.userlevel = n.userlevel then m.rcnt_1+n.rcnt_1 else m.rcnt_1 end) "总数初级"
   from(select to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') rdate,
               userlevel,
               count(decode(userlevel,10,1)) rcnt_1,
               count(decode(userlevel,11,1)) rcnt_2
          from ta 
         where userlevel in(10,11)
         group by userlevel, to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')
       )m,
      (select to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') rdate,
              userlevel,
              count(decode(userlevel,10,1)) rcnt_1,
              count(decode(userlevel,11,1)) rcnt_2
         from tc n
         where userlevel in(10,11)
        group by userlevel, to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')
       )n
  group by nvl(m.rdate, n.rdate)
),
tmp_b as(
select to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') rdate,
       count(decode(userlevel,10,1)) rcnt_1,
       count(decode(userlevel,11,1)) rcnt_2
  from tb
  where userlevel in(10,11)
  group by to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')
 )
select nvl(a.rdate,b.rdate) "时间",
       nvl(a.rcnt_2,0) "开户高级",
       nvl(a.rcnt_1,0) "开户初级",
       nvl(b.rcnt_2,0) "销户高级",
       nvl(b.rcnt_2,0) "销户初级",
       sum(a.rcnt_2)over(order by nvl(a.rdate,b.rdate))-nvl(sum(b.rcnt_2)over(order by nvl(a.rdate,b.rdate)),0) "总数高级",
       sum(a.rcnt_1)over(order by nvl(a.rdate,b.rdate))-nvl(sum(b.rcnt_1)over(order by nvl(a.rdate,b.rdate)),0) "总数初级"
  from tmp_a a full join tmp_b b
    on a.rdate=b.rdate
 order by nvl(a.rdate,b.rdate);


  若需要ta.tb.tc的建表代码的话,
  进入: http://topic.csdn.net/u/20100513/13/d4b8cc61-d5fb-4bb0-aba7-e6785ec45445.html

------解决方案--------------------
同义词转换不再有效
你看看那个synonyms没效,再改正下synonyms啊!