One last question 2004-07-16 - By Mladen Gogala
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 (See ORA-01722.ora-code.com): 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
