select a, b, (select sum(e.count) from ..where... +条件) as ontime,
(select sum(e.count) from .where.. +条件) ) as ahead,
(select sum(e.count) from..where.. +条件)) as delay
from c,d目前有这么一个类似的查询,出现的情况是某些条件下可以出结果,有些条件下就会报 ORA-01722: invalid number
请达人帮忙解答一下~谢谢原查询为:(条件为时间段)
select d.d_name,i.i_item_name,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='0' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as ontime,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as ahead,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='2' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31') as delay,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='0' and e.type='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sp_ontime,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='1' and e.type='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sp_ahead,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='2' and e.type='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sp_delay,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='0' and e.type='3' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sb_ontime,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='1' and e.type='3' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sb_ahead,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='2' and e.type='3' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sb_delay
from item i,department d where i.i_window_deptid=d.d_id order by d.d_order_num
(select sum(e.count) from .where.. +条件) ) as ahead,
(select sum(e.count) from..where.. +条件)) as delay
from c,d目前有这么一个类似的查询,出现的情况是某些条件下可以出结果,有些条件下就会报 ORA-01722: invalid number
请达人帮忙解答一下~谢谢原查询为:(条件为时间段)
select d.d_name,i.i_item_name,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='0' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as ontime,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as ahead,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='2' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31') as delay,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='0' and e.type='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sp_ontime,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='1' and e.type='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sp_ahead,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='2' and e.type='1' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sp_delay,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='0' and e.type='3' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sb_ontime,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='1' and e.type='3' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sb_ahead,
(select sum(e.count) from effective_table e where e.i_item_id=i.i_item_id and e.status='2' and e.type='3' and e.addtime >='2009-02-27' and e.addtime <='2009-03-31' ) as sb_delay
from item i,department d where i.i_window_deptid=d.d_id order by d.d_order_num
解决方案 »
- 创建Oracle数据库失败
- 关于Oracle全局(Globally)验证用户的一个问题
- oracle exp 远程导出数据报错 EXP-00056: 遇到 ORACLE 错误 12514
- 看看这个该怎么做?
- 世界级Oracle专家权威力作
- oracle 中sid与service_name
- 存储过程报告错误,帮忙看看,谢谢!
- 将EXCEL中的数据导入到ORACLE中怎样实现??急,急,急!!!!
- 我做了一个视图,只关联两个表,用管理工具打开浏览时需要十几分钟的时间,这是为什么?(两个表分别有11万条数据)
- oracle 触发器 如何修改另一条记录?
- 如何编写该update语句
- scott用户拥有哪些表?
sum(decode(e.status, '0', e.count, 0)) as on time,
...,
sum(decode(e.status||e.type, '01', e.count, 0)) as on sp_ontime,
...,
from ...
where e.addtime >='2009-02-27' and e.addtime <='2009-03-31'
...至于报错,楼主先要确定那段错了,一句一句删除试试就知道了