Select A.StartTime as 时间分组,A.Counts 呼入总次数,nvl(B.Counts,0) 成功总次数,nvl(C.Counts,0) 失败总次数,
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%) --未找到from语句
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
endfrom
(select count(*) from *** )A left outer join
(select count(*) from *** )B left outer join
(select count(*) from *** )C left outer join
(select count(*) from *** )D left outer join
(select count(*) from *** )E left outer join
在线等待
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%) --未找到from语句
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
endfrom
(select count(*) from *** )A left outer join
(select count(*) from *** )B left outer join
(select count(*) from *** )C left outer join
(select count(*) from *** )D left outer join
(select count(*) from *** )E left outer join
在线等待
解决方案 »
- oracle 字符转为时间的问题
- oracle自动增加数据文件?
- 如何通过互联网访问ORACLE数据库?
- 情人节...加班中...散分!!!
- 老生常谈:数据库的导入导出问题
- 排序问题!!参与有分(刚才问错了,重新问)
- 还是备份的老问题。高手请进。我想要一个简短确切的答案。谢谢 ,回答完整者1000分,分10次支付!
- 请问如何对某一计算出来的结果进行四舍五入呢?例如某两个时间相减之后的结果进行整数的四舍五入
- 请问select sum(kk) as mm from table where mm<'88'这句sql对吗??
- Oracle Migration Workbench 出错
- IMPDP命令执行中为什么会出现ORA-31684的错误。高手指点迷津
- 求解
when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%) --未找到from语句
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
end
写乱了,怎么会有2个ELSE?
整理一下吧
我是才接触oracle的
整理为:when nvl(D.Counts,0)=0 then as--缺失关键字 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
end
if nvl(D.Counts,0)=0就将这一列变为: 转人工接通率(100%)
else 就将这一列变为: 转人工失败次数
得到的结果要像这样的:
if nvl(D.Counts,0)=0
时间分组 转人工接通率(100%) 。。
年 88 。。
月 55 。。
日 44 。。
时 66 。。
else
时间分组 转人工失败次数 。
年 88 。
月 55 。
日 44
时 66
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then as--缺失关键字 转人工接通率(100%)(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
endfrom
(select count(*) from *** )A left outer join
(select count(*) from *** )B left outer join
(select count(*) from *** )C left outer join
(select count(*) from *** )D left outer join
(select count(*) from *** )E left outer join
错误为 在 case
when nvl(D.Counts,0)=0 then as--缺失关键字 转人工接通率(100%)(100%)
when nvl(D.Counts,0)=0 then as--缺失关键字 转人工接通率(100%)(100%)
then 要有内容的,你是对什么起的别名为 "转人工接通率(100%)(100%)"呢
这是一列动态数据从
(select count(*) from *** )D left outer join
(select count(*) from *** )E left outer join
这两个查询语句中得也就是((select count(*) from *** )D left outer join)-((select count(*) from *** )E left outer join) as 转人工接通率(100%)
when nvl(D.Counts,0)=0 then XXXX as 转人工接通率(100%)(100%) ;
你是不是打漏XXX了;
这个是语法错误,仔细看看啊, 不带这么玩的
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
end
from
(select count(*) as Counts from *** )A left outer join
(select count(*) as Counts from *** )B left outer join
(select count(*) as Counts from *** )C left outer join
(select count(*) as Counts from *** )D left outer join
(select count(*) as Counts from *** )E left outer join
错误是:
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
as 缺失关键字
想要显示两列的话 请用两个case when...
else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%)
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
当 nvl(D.Counts,0)=0 就显示为 nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
nvl(D.Counts,0)<>0 就显示为 (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
end
from(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(*) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and ServiceChainIndex = 0 --呼入总次数
) A left outer join (select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(*) as Counts
from SRRecord where StartTime>=to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss') and StartTime<=to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')
and (ServiceChainIndex = 1) AND (EndReason = '11') AND (ISO <> 'T')
) B on A.StartTime=B.StartTime left outer join --应答成功总次数(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(*) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and ((ServiceChainIndex = 0) And (EndReason<>'7') And (EndReason<>'515')) OR ((ServiceChainIndex = 1) AND (EndReason <> '11') AND (ISO <> 'T') And (SPType is null))
) C on A.StartTime=C.StartTime left outer join --应答失败总次数(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(distinct SRID) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and (EndReason = 20 and ISO<>'T' ) --IVR转座席次数,但不重复计算
) D on A.StartTime=D.StartTime left outer join (select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(distinct SRID) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and (EndReason = 21 and ISO<>'T' ) --IVR转座席成功,但不重复计算
) E on A.StartTime=E.StartTime
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
end
from上面所有列的数据都是从下面的select 语句中查出来的
我只是想在第五列做个判断
如果 nvl(D.Counts,0)=0 显示的结果为 nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
那么 显示的结果为 (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
(case when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end) as ,(nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数当 nvl(D.Counts,0)=0 nvl(E.Counts,0)*100/D.Counts(列) as 转人工接通率(100%)
那么 ,(nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
问题出在 (100%), 可能在oracle 中会认为 转人工接通率(100%)是个函数(个人认为)
去掉 (100%) 正常运行。。