to_number()后比较大小出现无效数字问题分析解决
前言
这是个oracle 同表查询总嵌套子查询的执行计划问题。
问题原因
环境描述
一张表 xxx (文中执行计划部分打码), 有个yyy varchar2的字段,里边是数字,含有特殊的admin
需求,将yyy转为数字型的,去除admin,然后 过滤 yyy>30000 的结果集。
错误语句
select * from (
select to_number(yyy) as userID from xxx where xxx.aaa <>'admin'
) a where a.userID >= 30000
1
2
3
执行后报出 无效数字的错误 (单独执行子查询是没有问题的)
问题解决
初步问题分析
出现无效数字的问题,首先确定的是 数据类型不匹配
分析上述语句 , 就是对同一张表做的嵌套查询, 但是单独执行子查询是没有问题的。也就是说 to_number(yyy) 里边的字符是可以正常转成数字的, 又加了外层的查询后出的问题。
出现这种情况开始我一度怀疑是 to_number() 函数的问题,查看了官方文档以后,也没看出个所以然。
这其中我忽略了一个解决sql问题的很重要的步骤 就是查看 sql 执行计划 ,浪费了些许时间在网上所查结果,半天也一无所获,甚至被误导方向。
方法二 (正则替换):
select * from (
select to_number(REGEXP_REPLACE(yyy,'[^0-9]','')) as userID from xxx where xxx.aaa <>'admin'
) a where a.userID >= 30000