Re: to_number

[Quoted] "r_hao" <r_hao_at_hotmail.com> wrote in message news:8rjasm$53e$1_at_slb6.atl.mindspring.net...
> Hi, everyone;
>
> I found that 'to_number(column_name)' does not work under the sub-query. It
> give me ora -1722 error. How do I get around it?
>
> TIA
>
> Randy

Gee, sure would help if we saw your query.

But I'll take a guess at it anyway.

You probably have a table with a VARCHAR2 or CHAR column. We'll call this column "TEST". Length of the column is irrelevant - we'll use a length of 1 here:

TEST VARCHAR2(1) [Quoted] Now let's say you have these rows of data for TEST:

1
2
3
4
5
6
7
8
9

You write either of these two queries:

select 'got it!'
from my_table
where test = '5';

select 'got it!'
from my_table
where test = 5;

Both will work fine.

Now, you insert another row (TEST = 'X') into your table:

1
2
3
4
5
6
7
8
9
X

Now do your two queries again:

select 'got it!'
from my_table
where test = '5';

That one will work.

select 'got it!'
from my_table
where test = 5;

That one will bomb with the ORA-01722 Invalid Number error.

Wanna guess why?

Give up?

OK - I'll tell you. Well, this is just a guess but I'm pretty sure I'm right [Quoted] or at least very close.

The second query has:
where test = 5

The 5 has no quotes around it, so Oracle "knows" it's a number and not a character and treats it as number and not a character. In order to evaluate [Quoted] the value in "TEST", it internally does a TO_NUMBER on the rows to see what matches:

[Quoted] TO_NUMBER('1') = 5
TO_NUMBER('2') = 5
TO_NUMBER('3') = 5
TO_NUMBER('4') = 5
TO_NUMBER('5') = 5  <--- It will find a match on this
TO_NUMBER('6') = 5
TO_NUMBER('7') = 5
TO_NUMBER('8') = 5
TO_NUMBER('9') = 5
TO_NUMBER('X') = 5  <--- It will bomb on this

Uh-oh. That last one is an invalid number, eh?

Take a look at your query and see if it's something similar - are you using a [Quoted] number without single quotes in your where clause and your column or variable [Quoted] that you are comparing it to is a character? If that's not the problem, post [Quoted] it so we can all see what you're doing.

-Matt Received on Fri Oct 06 2000 - 07:13:46 CEST