Re: to_number fails?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 05 Aug 1999 20:12:53 +0800
Message-ID: <37A97FC5.955@yahoo.com>

stevie_deja_at_my-deja.com wrote:
>
> Hi,
> I've been racking my brain with this one for a while and
> can't come up with a good solution. Can anyone tell me
> why the following fails?
>
> SQL> select REQUEST, STATUS, QTYNEEDED from csr where
> 10 = to_number(to_char(QTYNEEDED));
>
> 10 =
> to_number(to_char(QTYNEEDED))
> *
> ERROR at line 2:
> ORA-01722: invalid number
>
> Table fields are all VARCHAR2s,
> and the only data in the QTYNEEDED fields are number 2, 4 & 10,
> nothing too difficult to convert. I've tried it without the
> to_char and it fails in just the same order.
>
> Thanks for any help,
> Steve K.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

You have a quantity that ain't a number...

try

select *
from table
where rpad(translate(qty,'0123456789','XXXXXXXXXX'),10,'X') != 'XXXXXXXXX' ie map all the numerics to X, pad out length of 10 and see what remains...

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Aug 05 1999 - 07:12:53 CDT