TO_NUMBER(SUBSTR(...)) 不能在where子句中使用吗？

cjf107 · 发表于 2006-12-7 18:00

linux as3.0  + oracle9.2.0.4

SQL> select count(*) from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions) ;

  COUNT(*)
----------
   920

SQL>  select count(*) from user_tab_partitions
  2  where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
            *
ERROR at line 2:
ORA-01722: invalid number

SQL>
请问这是为什么？

warehouse · 发表于 2006-12-7 18:09

select count(*) from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) col from user_tab_partitions) t;加上列别名和子查询的表别名看看！

dba_mx · 发表于 2006-12-7 19:24

[php]
SQL> select to_number('a') from dual;
select to_number('a') from dual
               *
ERROR 位于第 1 行:
ORA-01722: 无效数字

SQL> select count(*) from (select to_number('a') from dual);

  COUNT(*)
----------
      1

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0    Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

...
[/php]

dba_mx · 发表于 2006-12-7 19:25

dba_mx · 发表于 2006-12-7 19:27

[php]
SQL> select * from  (select to_number('a') from dual);
select * from  (select to_number('a') from dual)
                              *
ERROR 位于第 1 行:
ORA-01722: 无效数字

可以看出：如果外层查询只想或许一个内层查询的记录数的话，那么内层的表达式是不被计算的，直接统计内层表的记录数；
反之，如果希望获取内容，则表达式必然要求值，此时才发现错误；

..
[/php]

Oracle的语义分析功能蛮强啊。。。。

shunji · 发表于 2006-12-7 19:32

同意楼上兄弟的看法

yangtingkun · 发表于 2006-12-7 19:59

很显然，第一个TO_NUMBER根本没有执行，你可以改成TO_DATE试试

space6212 · 发表于 2006-12-8 08:53

以前一直没注意到这个细节

cjf107 · 发表于 2006-12-8 10:30

最初由 yangtingkun 发布
[B]很显然，第一个TO_NUMBER根本没有执行，你可以改成TO_DATE试试 [/B]

SQL>select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions;
......
                              494
                              495
                              495
                              496
                              496
                              497
                              497
                              498
                              498
                              499
                              499
                              500
                              500

920 rows selected.

SQL>
就是执行了也没有问题。我保证分区名都是由'PART_'||一个数字
组成的，可为什么TO_NUMBER(SUBSTR(PARTITION_NAME,6))放在where子句中就报错呢？
SQL> select count(*) from user_tab_partitions
2 where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182;
where TO_NUMBER(SUBSTR(PARTITION_NAME,6))<=182
*
ERROR at line 2:
ORA-01722: invalid number

SQL>

cjf107 · 发表于 2006-12-8 10:33

最初由 dba_mx 发布
[B][php]
SQL> select * from  (select to_number('a') from dual);
select * from  (select to_number('a') from dual)
                              *
ERROR 位于第 1 行:
ORA-01722: 无效数字

可以看出：如果外层查询只想或许一个内层查询的记录数的话，那么内层的表达式是不被计算的，直接统计内层表的记录数；
反之，如果希望获取内容，则表达式必然要求值，此时才发现错误；

..
[/php]
Oracle的语义分析功能蛮强啊。。。。 [/B]

SQL> select count(*) from (select *  from (select TO_NUMBER(SUBSTR(PARTITION_NAME,6)) from user_tab_partitions)) ;

  COUNT(*)
----------
   920

SQL>

TO_NUMBER(SUBSTR(...)) 不能在where子句中使用吗？

原因大抵如此吧：