ora-01722,invalid number 百思不解，敬请各位大师解决

wqliu

我刚在9i  9.2.0.1试过没有问题

wqliu

难道是版本的问题?

wqliu

不是版本的问题.
刚回到公司,
1....create table AAA as  select   *from G_PRINT_PALLET_MORE ;
2.....
select T1.* from (
  select to_number(substr(parame_name,4)) as parame_name1 , a.* from SAJET.AAA a
  where substr(parame_name,0,3) = 'SN_' and parame_name != 'SN_COUNT' and parame_name != 'SN_END'
  )T1
  where to_number(T1.parame_name1) between 400 and 420;

是没有任何问题的.
或者, 把导出来的数据 插入到AAA,也一样没有问题.


问题到底在哪儿呢

wqliu

可以结贴了.

用oracle 的正则表达式可解决.


     select T1.parame_name1  from (
  select substr(parame_name,4) as parame_name1 , a.* from SAJET.a_test2 a
  where substr(parame_name,0,3) = 'SN_' and parame_name != 'SN_COUNT' and parame_name != 'SN_END'
  )T1
  where  regexp_substr(parame_name1,'[0-9]+')  between 400 and 420;

wqliu

sql plan

sqysl

这个贴虽然楼主结贴，但问题依然没有解决，或者没解释清楚，那么，我来解释下：
1、之前楼主的SQL语句之所以报错，是因为SQL生成执行计划阶段，对子查询进行了合并，并将最外面报错的where条件和子查询中的where条件进行了合并，这点从楼主提供的执行计划就可以看出来，这样，合并进来的报错的where条件先进行过滤时，就会发生报错，因为这时之前子查询中的where条件可能还没发生作用；
2、楼主用正则函数避免了报错的问题，是因为这个正则函数和to_number不是等价的，它只是找出了包含一位数字的数据行，所以不报错；
3、根据上面的分析，楼主也可以禁止子查询展开试试，那样就应该不报错了。

wqliu

谢谢您的精彩分析。确实是full scan做了合并，导致子查询没起到作用，那如何做到禁止子查询展开呢

sqysl

wqliu 发表于 2020-5-6 23:56
谢谢您的精彩分析。确实是full scan做了合并，导致子查询没起到作用，那如何做到禁止子查询展开呢

你可以在子查询中用group分组或rownum之类的，也可以用no_merge等hint试试看。

sqysl

例如：
select /*+ no_merge(T1)*/  T1.* from (
  select to_number(substr(parame_name,4)) as parame_name1 , a.* from SAJET.G_PRINT_PALLET_MORE a
  where substr(parame_name,0,3) = 'SN_' and parame_name != 'SN_COUNT' and parame_name != 'SN_END'
  )T1
  where to_number(T1.parame_name1) between 400 and 420;

sxq129601

说到底还是数据问题