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

wqliu

select 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

常规思维：1，先做里面的子查询，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' ，
                2.再做外面的查询 select  * from T1 where to_number(T1.parame_name1) between 400 and 420是没有问题。

wqliu

如果这样写，也是有问题的。
select to_number(parame_name1) from (
select substr(parame_name,4) as parame_name1  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

只是甲

wqliu 发表于 2020-5-1 15:55
如果create table AAA as  select to_number(substr(parame_name,4)) as parame_name1 , a.* from SAJET.G_ ...

SQL> select count(*) from G_PRINT_PALLET_MORE;
  COUNT(*)
----------
      2100

SQL>
SQL> select T1.* from (
  2    select to_number(substr(parame_name,4)) as parame_name1 , a.* from G_PRINT_PALLET_MORE a
  3    where substr(parame_name,0,3) = 'SN_' and parame_name != 'SN_COUNT' and parame_name != 'SN_END'
  4    )T1
  5    where to_number(T1.parame_name1) between 400 and 420
  6  /
PARAME_NAME1 PARAME_NAME                                                                      PARAME_VALUE
------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         417 SN_417                                                                           R3K91DC111617
         418 SN_418                                                                           R3K91DC111618
         419 SN_419                                                                           R3K91DC111619
         420 SN_420                                                                           R3K91DC111620
         400 SN_400                                                                           R3K91DC111600
         401 SN_401                                                                           R3K91DC111601
         402 SN_402                                                                           R3K91DC111602
         403 SN_403                                                                           R3K91DC111603
         404 SN_404                                                                           R3K91DC111604
         405 SN_405                                                                           R3K91DC111605
         406 SN_406                                                                           R3K91DC111606
         407 SN_407                                                                           R3K91DC111607
         408 SN_408                                                                           R3K91DC111608
         409 SN_409                                                                           R3K91DC111609
         410 SN_410                                                                           R3K91DC111610
         411 SN_411                                                                           R3K91DC111611
         412 SN_412                                                                           R3K91DC111612
         413 SN_413                                                                           R3K91DC111613
         414 SN_414                                                                           R3K91DC111614
         415 SN_415                                                                           R3K91DC111615
         416 SN_416                                                                           R3K91DC111616
21 rows selected

只是甲

我手上没有10g的库

我把2000多条数据都给弄到表里面，执行查询是OK的，不知道是不是数据库版本的问题

wqliu

您的sql执行计划是什么,麻烦您贴一下

只是甲

wqliu 发表于 2020-5-1 16:58
您的sql执行计划是什么,麻烦您贴一下

****@****>select T1.* from (
  2    select to_number(substr(parame_name,4)) as parame_name1 , a.* from G_PRINT_PALLET_MORE a
  3    where substr(parame_name,0,3) = 'SN_' and parame_name != 'SN_COUNT' and parame_name != 'SN_END
  4    )T1
  5    where to_number(T1.parame_name1) between 400 and 420
  6  /

已选择21行。


执行计划
----------------------------------------------------------
Plan hash value: 3050350519

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |     1 |   204 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| G_PRINT_PALLET_MORE |     1 |   204 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PARAME_NAME"<>'SN_COUNT' AND "PARAME_NAME"<>'SN_END' AND
              SUBSTR("PARAME_NAME",0,3)='SN_' AND TO_NUMBER(TO_CHAR(TO_NUMBER(SUBSTR("PARAME_NA
              ME",4))))>=400 AND TO_NUMBER(TO_CHAR(TO_NUMBER(SUBSTR("PARAME_NAME",4))))<=420)

只是甲

TO_NUMBER(TO_CHAR(TO_NUMBER(SUBSTR"PARAME_NAME",4))))>=400

这个我也不明白了，为什么一次to_number不行

只是甲

T1.parame_name1 已经是number类型了，所以不用to_number了


*****@*****>SELECT T1.*
  2    FROM (SELECT to_number(substr(parame_name, 4)) AS parame_name1, a.*
  3            FROM G_PRINT_PALLET_MORE a
  4           WHERE substr(parame_name, 0, 3) = 'SN_'
  5             AND parame_name != 'SN_COUNT'
  6             AND parame_name != 'SN_END') T1
  7   WHERE T1.parame_name1 BETWEEN 400 AND 420;

已选择21行。


执行计划
----------------------------------------------------------
Plan hash value: 3050350519

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |     1 |   204 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| G_PRINT_PALLET_MORE |     1 |   204 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PARAME_NAME"<>'SN_COUNT' AND "PARAME_NAME"<>'SN_END' AND
              SUBSTR("PARAME_NAME",0,3)='SN_' AND TO_NUMBER(SUBSTR("PARAME_NAME",4))>=400 AND
              TO_NUMBER(SUBSTR("PARAME_NAME",4))<=420)

wqliu

T1.parame_name1 已经是number类型了，所以不用to_number了。。。。。是的，但我的确实报错

wqliu

而且执行计划是一样的