一个非常非常奇怪的SQL问题，请高手指教

显示全部楼层 · 2006-4-17 13:46:34

SQL> desc subclubinfo
名称
是否为空? 类型
----------------------------------------- -------- ----------------------------
SUBCLUBNO
NOT NULL NUMBER
... ...
SQL> desc appluclub
名称
是否为空? 类型
----------------------------------------- -------- ----------------------------
SERVICEID
NOT NULL VARCHAR2(20)
... ...
SQL> select a.subclubno from(select subclubno from subclubinfo where to_char(subclubno) = '89') a
2,
3(select to_number(substr(serviceid,8)) SUBCLUBNO from JOINCLUB where serviceid like 'VVCLUB_%' group by substr(serviceid,8)) e
4where a.subclubno=e.subclubno
5/
SUBCLUBNO

----------

89

按理说subclubno本身是number型，直接写subclubno=89就可以了，但我这样写却报错
SQL> select a.subclubno from(select subclubno from subclubinfo where subclubno = 89) a
2,
3(select to_number(substr(serviceid,8)) SUBCLUBNO from JOINCLUB where serviceid like 'VVCLUB_%' group by substr(serviceid,8)) e
4* where a.subclubno=e.subclubno

*
ERROR 位于第 3 行:
ORA-01722: invalid number
请高手指教，谢谢

千问 · 2006-4-17 13:46:34

改成这样就可以了：
[php]
select a.subclubno from (select subclubno from subclubinfo where subclubno = 89) a,
select e.subclubno from ((select to_number(substr(serviceid,8)) SUBCLUBNO from JOINCLUB

where serviceid like 'VVCLUB_%' group by substr(serviceid,8)) e ) e
where a.subclubno=e.subclubno
[/php]

千问 · 2006-4-17 13:46:34

最初由 itpub.com.cn 发布
[B]改成这样就可以了：
[php]
select a.subclubno from (select subclubno from subclubinfo where subclubno = 89) a,
select e.subclubno from ((select to_number(substr(serviceid,8)) SUBCLUBNO from JOINCLUB

where serviceid like 'VVCLUB_%' group by substr(serviceid,8)) e ) e
where a.subclubno=e.subclubno
[/php] [/B]
这样不对吧？而且我觉得并没解决我疑惑的问题，呵呵。能再说说吗

千问 · 2006-4-17 13:46:34

问题很简单，对比你第一句怎么写的，你就明白了

千问 · 2006-4-17 13:46:34

问题找到了，是oracle的执行顺序问题。
select to_number(substr(serviceid,8)) SUBCLUBNO from JOINCLUB where serviceid like 'VVCLUB_%' group by substr(serviceid,8)
我原本以为这句SQL会先执行where，过滤掉serviceidnot like 'VVCLUB_%'的数据，在进行to_number(substr(serviceid,8)) 。但实际上，他是先进行了to_number，而表里的数据，有一个serviceid='CLUBPACK'的，因此报错。
PS. 感觉，以后涉及类型转换的还是谨慎的用to_char 好些，毕竟什么都能转换成char，呵呵

千问 · 2006-4-17 13:46:34

明白了一个道理，类型转换时不能有不兼容的数据存在