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
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'
...至于报错,楼主先要确定那段错了,一句一句删除试试就知道了