Re: Dazed and confused

"mar" <mar_at_notexist.com> wrote in message news:bmee66$lg2jn$1_at_ID-174077.news.uni-berlin.de...
>
> "Stephan Bressler" <sbresslerx_no_spam_at_arcor.de> wrote
>
> > Hi,
> > oracle accepts comparas of char columns to numeric values, for there is
> > a conversion possible (at least sometimes).
> > I assume you get the error with xflag=1 because all those records
> > contain numeric values for xcode, while at least one of the records with
> > xflag=0 contains a non-numeric value.
>
>
> Thanks to all of you, guys.
>
> Actually, what is puzzling is that with XFLAG=0 I get that error
> yet I can't see anything that would prevent corresponding
> XCODE (1234567890123) to be converted successfully
> into a number.
>
> Therefore I have to ask: why can't Oracle convert string
> '1234567890123' into a valid number?
>
> It's not only '1234567890123'. The same happens with
> any combination of 13 digits. Please notice the XCODE
> field is of type CHAR(15). That means that the last two chars
> are not taken. So it has got to do something with the last
> two char positions.
>
>

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
^^^^^^^^^^^^^^^^^^

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.

Anurag Received on Mon Oct 13 2003 - 10:56:01 CDT