如题,在执行以下查询时出现:ora-00980:同义词转换不再有效 错误提示! 希望大家帮忙解决下!在此先谢谢了!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
(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
解决方案 »
- sql双层分组 怎么写sql
- java.sql.SQLException: ORA-01704: 字符串文字太长
- 为什么count得不出正确的值
- 在windows中和linux系统在oracle中使用datediff的问题
- 请教如何同时UPDATE单张表中的两个字段
- for loop 循环判断问题
- 解决分数全给。用delete语句清理数据,由于数据量太大,会滚段(UNDOTBS)从200M增长到3G,把磁盘空间占满了,现在连数据部分的表空间都
- 各位大侠,请问如何修改HTTP Server的默认端口?
- Error in Open method. 未在本地计算机上注册“OraOLEDB.Oracle”提供程序?
- 如何添加一条记录 在sql plus 中 比较菜鸟的问题 大家见笑了!
- 关于ora-00913错误
- 怎么获取数据库没提交的所有sql
你看看那个synonyms没效,再改正下synonyms啊!
呵呵!不是synonyms的问题!
问题我自己解决了!
自己不是足够的仔细!