Re: to_number

From: r_hao <r_hao_at_hotmail.com>
Date: Sun, 8 Oct 2000 21:48:36 -0700
Message-ID: <8rr88a$dp3$1_at_slb3.atl.mindspring.net>

[Quoted] Thank you Matt;

My query is

select emp_id
from employee
where emp_id in
(select emp_id
from job_title
where to_number(job_level) > 10);

I can run 'select emp_id from job_title where to_number(job_level) > 10'. However, I did not check all the rows. Maybe there is a row contains char. I'll check it.

Thanks - Randy

"Matt B." <mcb_at_fightspam.sd.znet.com> wrote in message
news:stqnjn6jrk853d_at_corp.supernews.com...

> "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)
>
> 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
> 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
> the value in "TEST", it internally does a TO_NUMBER on the rows to see
 what
> matches:
>
> 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
> number without single quotes in your where clause and your column or
 variable
> that you are comparing it to is a character? If that's not the problem,
 post
> it so we can all see what you're doing.
>
> -Matt
>
>
Received on Mon Oct 09 2000 - 06:48:36 CEST