Re: Update using TO_NUMBER

<inlove22_at_hotmail.com> wrote in message
news:1137364114.760810.11320_at_g43g2000cwa.googlegroups.com...
> Hello,
>
> I have a problem with one of my update queries. Im trying to update
> table 1 column x_number which is datatype number() using the data in
> table 2 column x_char which is datatype char(). The following query
> does not work as i get the error value is not a number.
> Basically i wondering how i can convert the value of x_char as a number
> and stored it as a number into x_number.
>
> TIA!!!!
> *******************
> *******************
> UPDATE table1
> SET x_number = (SELECT TO_NUMBER(x_char)
> FROM table2 WHERE LENGTH(x_char) = 11
> AND row_id = table1.par_row_id
> AND x_char is not null
> WHERE EXISTS (SELECT x_char
> FROM table2 WHERE LENGTH(x_char) = 11
> AND row_id = table1.par_row_id
> AND x_char is not null
> );
>
> commit;
>
> EXIT;
> *******************
> *******************
>

The problem is that at least one of the values in the x_char is not a number but a character value.
Jim Received on Sun Jan 15 2006 - 21:10:32 CST