Re: One last question

On 07/16/2004 09:01:58 PM, Jonathan Gennick wrote:
> Wolfgang,
> 
> One last question, and then I'm done for the night. Here's
> that query again:
> 
> SQL> SELECT *
>   2  FROM ( SELECT flag, TO_NUMBER(num) num
>   3         FROM subtest
>   4         WHERE flag IN ('A', 'C') )
>   5  WHERE num > 0;
> ERROR:
> ORA-01722: invalid number
> 


Jonathan, you may simply have something invalid in that table. I created a
small table SUBTEST like this:

SQL> create table subtest as
  2  select substr(ename,1,1) flag, to_char(empno) num
  3  from emp;
 
Table created.
 
SQL> desc subtest;
 Name                                      Null?    Type
 ----------------------------------------- -------- 
---------------------------- FLAG                                               
VARCHAR2(1)
 NUM                                                VARCHAR2(40)
 
SQL> SELECT *
  2  FROM ( SELECT flag, TO_NUMBER(num) num
  3         FROM subtest
  4         WHERE flag IN ('A', 'C') )
  5  WHERE num > 0;
 
F        NUM
- ----------
C       7782
A       7499
A       7876
 
SQL>


As you can see, there is no error. You might really have something in your
data that cannot be converted. Of course, your "SUBTEST" table might have
not been create and populated the same way as mine.
-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: