下面的SQL在Oracle11g下可以正常执行,但在Oracle9i 里面会报错
ORA-00979: 不是 GROUP BY 表达式
00979. 00000 - "not a GROUP BY expression"
SELECT
nvl(A.CallinagtNUMLackIvr,0) as CallinagtNUMLackIvr,
nvl(C.CalledagtNUM,0) as CalledagtNUM,
nvl(A.serviceNo,C.serviceno) as serviceno,
nvl(A.timePeriod,C.timePeriod) as timePeriod
FROM
(
SELECT
COUNT(distinct callid) AS CallinagtNUMLackIvr,
trunc(callend,'HH24') as timePeriod,
serviceNo,
(to_char(trunc(callend,'HH24'),'YYYYMMDDHH24MISS')+serviceno) as ids
FROM tbilllog1
WHERE (devicetype = 2 or (devicetype = 1 and callidnum = -1))
AND callend >= to_date('20130101000000','YYYYMMDDHH24MISS')
AND callend <to_date('20130102000000','YYYYMMDDHH24MISS')
AND calltype IN (0,5,13)
AND serviceno in (111,112,113,114,115)
group by
serviceNo,trunc(callend,'HH24')
)a,
(
select count(DISTINCT callid) AS CalledagtNUM,trunc(callend,'HH24') as timePeriod,serviceNo,(to_char(trunc(callend,'HH24'),'YYYYMMDDHH24MISS')+serviceno) as ids
from tbilllog1
where callend >= to_date('20130101000000','YYYYMMDDHH24MISS')
AND callend <to_date('20130102000000','YYYYMMDDHH24MISS')
and CALLTYPE in (0,5,13)
and Callend > Ackbegin
and DEVICETYPE = 2
and SERVICENO in (111,112,113,114,115)
group by
serviceNo,trunc(callend,'HH24')
)C where A.ids = C.ids
oraclesqloracle9ioracle11ggroup by
ORA-00979: 不是 GROUP BY 表达式
00979. 00000 - "not a GROUP BY expression"
SELECT
nvl(A.CallinagtNUMLackIvr,0) as CallinagtNUMLackIvr,
nvl(C.CalledagtNUM,0) as CalledagtNUM,
nvl(A.serviceNo,C.serviceno) as serviceno,
nvl(A.timePeriod,C.timePeriod) as timePeriod
FROM
(
SELECT
COUNT(distinct callid) AS CallinagtNUMLackIvr,
trunc(callend,'HH24') as timePeriod,
serviceNo,
(to_char(trunc(callend,'HH24'),'YYYYMMDDHH24MISS')+serviceno) as ids
FROM tbilllog1
WHERE (devicetype = 2 or (devicetype = 1 and callidnum = -1))
AND callend >= to_date('20130101000000','YYYYMMDDHH24MISS')
AND callend <to_date('20130102000000','YYYYMMDDHH24MISS')
AND calltype IN (0,5,13)
AND serviceno in (111,112,113,114,115)
group by
serviceNo,trunc(callend,'HH24')
)a,
(
select count(DISTINCT callid) AS CalledagtNUM,trunc(callend,'HH24') as timePeriod,serviceNo,(to_char(trunc(callend,'HH24'),'YYYYMMDDHH24MISS')+serviceno) as ids
from tbilllog1
where callend >= to_date('20130101000000','YYYYMMDDHH24MISS')
AND callend <to_date('20130102000000','YYYYMMDDHH24MISS')
and CALLTYPE in (0,5,13)
and Callend > Ackbegin
and DEVICETYPE = 2
and SERVICENO in (111,112,113,114,115)
group by
serviceNo,trunc(callend,'HH24')
)C where A.ids = C.ids
oraclesqloracle9ioracle11ggroup by
解决方案 »
- 编写一个小函数字符串拼接
- select * from table1 t?t是怎么来的
- 关于使用tera term工具访问Linux下的oracle数据库的问题!
- oracle11g的几个非常简单的问题
- 我自己开发的insert,update代码快速生成功能,大家来试试看(完全免费)
- (急)Oracle所在磁盘没有空间了,怎么办?
- 一个统计报表的写法,请大家指教
- oracle连接登录问题?(在线等待!)
- 求助:oracle连接错误ora_01034:oracle not available,如何解决!急!!!!!
- 数据复制可以复制大字段吗
- sql树查询
- SQL 错误: ORA-31000: 资源 'http://xmlbook.com/sample/contact.xsd' 不是 XDB 方案文档
这是是不是应该也加到group by 后面
应该用 ids