有如下SQL语句:
SELECT a.group_id,
COUNT(distinct(case
when SRV_STATE <> 'R' AND month = 201008 AND
PID in
(select item_id
from temp_2
where id = 8
and substr(node, 1, 4) in
('0001', '0002', '0003', '0004')) then
serv_id
end)) cnt
FROM temp_1 a
WHERE a.state = 'A'
AND nvl(a.flag, 0) < 2
GROUP BY a.group_id
执行报错:ORA-01722 invalid number
将子查询改为
(select item_id
from temp_2
where id = '8'
and substr(node, 1, 4) in
('0001', '0002', '0003', '0004')) 后,整段SQL可出来结果
不明白,为什么改成id = '8'后,就可出正确结果?
有验证过,select item_id
from temp_2
where id = 8
and substr(node, 1, 4) in
('0001', '0002', '0003', '0004') 与
select item_id
from temp_2
where id = '8'
and substr(node, 1, 4) in
('0001', '0002', '0003', '0004')均可出结果
就怀疑是不是哪的隐式转换出问题了呢?
附上表结构:
SQL> desc temp_1
Name Type Nullable Default Comments
---------------- ------------- -------- ------- --------
MONTH NUMBER(12)
SERV_ID NUMBER(12)
FLAG NUMBER(1)
PID NUMBER(9)
STATE VARCHAR2(3)
SRV_STATE VARCHAR2(3)
GROUP_ID VARCHAR2(20)
SQL> desc temp_2
Name Type Nullable Default Comments
--------- ------------- -------- ------- --------
ID VARCHAR2(30)
NODE VARCHAR2(30)
ITEM_ID VARCHAR2(30)
写得有点长,希望有把问题说清楚,呵呵