| Select query given ORA-01722 error message [message #603477] |
Mon, 16 December 2013 22:08  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Hi all,
a) SELECT * FROM TABLEA WHERE COL1 = 100;
Above query cannot run today given "ORA-01722: invalid number" error message but it's working fine long time before.
When I add "'" symbol in where condition, it's working fine like below.
b) SELECT * FROM TABLEA WHERE COL1 = '100';
"col1" field type was varchar2.
Can you explain it?
First query still working fine on my test server.
I saw only a difference for this column in table between PROD and TEST database.
When I see table colums by Toad, Historgam="Hieght Balanced" on PROD but Historgam="None" on TEST database for that COL1 of TABLEA.
|
|
|
|
|
|
|
|
| Re: Select query given ORA-01722 error message [message #603639 is a reply to message #603496] |
Wed, 18 December 2013 02:44  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Hi, I'll try to explain it a little bit further: When you compare two different data types, Oracle will implicitly cast one of the two sides of the equation. In your case, Oracle will try to convert COL1 to a number. If all values in COL1 can be converted to a number, you're fine but if one value can't be casted to a number, you'll hit the error you encountered. It is good practice to make sure that Oracle doesn't apply implicit casting.
Here's a small demonstration. Look at the difference in the data. The first version contains only numeric values.
-- This will work
With yourtable
AS
( Select cast('1' as varchar2(1)) char_column, 'One' description From dual Union All
Select cast('2' as varchar2(1)) char_column, 'Two' description From dual Union All
Select cast('3' as varchar2(1)) char_column, 'Three' description From dual Union All
Select cast('4' as varchar2(1)) char_column, 'Four' description From dual Union All
Select cast('5' as varchar2(1)) char_column, 'Five' description From dual Union All
Select cast('6' as varchar2(1)) char_column, 'Six' description From dual Union All
Select cast('7' as varchar2(1)) char_column, 'Seven' description From dual
)
Select *
From yourtable
Where char_column = 2
/
-- This won't work at all:
With yourtable
AS
( Select cast('A' as varchar2(1)) char_column, 'One' description From dual Union All
Select cast('2' as varchar2(1)) char_column, 'Two' description From dual Union All
Select cast('B' as varchar2(1)) char_column, 'Three' description From dual Union All
Select cast('4' as varchar2(1)) char_column, 'Four' description From dual Union All
Select cast('5' as varchar2(1)) char_column, 'Five' description From dual Union All
Select cast('6' as varchar2(1)) char_column, 'Six' description From dual Union All
Select cast('7' as varchar2(1)) char_column, 'Seven' description From dual
)
Select *
From yourtable
Where char_column = 2
/
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
