Re: One last question

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 16 Jul 2004 23:42:12 -0400
Message-ID: <20040717034212.GA11141@medo.noip.com>

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_at_freelists.org
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
-----------------------------------------------------------------

Received on Fri Jul 16 2004 - 22:38:54 CDT