Re: Dazed and confused

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Mon, 13 Oct 2003 17:15:59 GMT
Message-ID: <j5Bib.18864$0I6.14706@nwrdny03.gnilink.net>

"mar" <mar_at_notexist.com> wrote in message news:bmekoi$lscn2$1_at_ID-174077.news.uni-berlin.de...
> "Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote
>
> > You missed the point!
> > If you don't explicitly define the conversion, oracle defines it for
you.
> >
> > So in essence its trying to do the following:
> >
> > SELECT COUNT(*) FROM MYTABLE
> > WHERE to_number(XCODE)=1234567890123 AND XFLAG=0;
> > ^^^^^^^^^
> >
> > so if it uses the filter criteria xflag=0 first, it will try using the
> > filter criteria
> > to_number(xcode) = 1234567890123 next.
> >
> > So it errors out when it encounters the following conversion
> > to_number('abcdefghij') = 1234567890123
> > ^^^^^^^^^^^^^^^^^^
>
>
> Well, somebody misses a point here, that's obvious.
> It's probably me, I'm pretty dense today.
>
> However, I wanted to say that there is NO such value
> as 'abcdefghij' in your example above. I repeat: all strings
> are composed of digits only! What gives?
>
>
>
> > You need to prevent this implicit conversion by stating
> > XCODE = '1234567890123' !!!
> >
> > I don't understand why you are hellbent on not using the correct method.
>
>
> I'm not hellbent, actually the example/puzzle comes from my
> coworker. I warned him to use apostrophes.
>
>

If its not digits .. it might be tabs or something else. Look at this:

SQL> select to_number('1234 ') from dual; select to_number('1234 ') from dual

                  *

ERROR at line 1:
ORA-01722: invalid number

.. thats a tab after 1234. So anything not number can trigger this. You must be having one of these cases.
I cannot believe you got the error just for no reason.

Anurag Received on Mon Oct 13 2003 - 12:15:59 CDT