| Want to know Data Entered ?Pl.Help [message #63928] |
Fri, 26 November 2004 01:15  |
BhavinShah
Messages: 105
Registered: February 2004
|
Senior Member |
|
|
Dear frends,
I wanted to know a table which has a data like column ID2. means It's column datatype is varchar2 but in that column "no" and "varchar" field both are added. Here in example "2" and "b".
SQL> select * from demo_dh;
ID1 ID2
---------- ----------
1 2
3 B
SQL> select count(*) from demo_dh
2 where id2=2;
where id2=2
*
ERROR at line 2:
ORA-01722: invalid number
SQL> desc demo_dh
Name Null? Type
----------------- -------- ------------
ID1 VARCHAR2(10)
ID2 VARCHAR2(10)
SQL>
|
|
|
|
| Re: Want to know Data Entered ?Pl.Help [message #63929 is a reply to message #63928] |
Fri, 26 November 2004 01:36  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
Try the following:
SQL> select count(*) from demo_dh
2 where id2='2';
Oracle needs to convert ID2 to a number before it can do the comparison. When it tries to convert the column containing the 'B', it fails with ORA-01722: invalid number.
Best regards.
Frank
|
|
|
|
|
|
|
|
| Re: But I want to find the data of like this.:pl help [message #63932 is a reply to message #63931] |
Fri, 26 November 2004 03:38 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The table does not contain incosistent data. The datatype is varchar2. Some rows contain digits (= character), some rows contain letters (= character).
In terms of datatype, there is nothing wrong. The error is purely at a functional level: you know id should contain numbers only.
One more reason to choose your datatypes right at designtime.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
