Re: Unexplainable SQL-error message

Even though you checked, and all the values _appear_ to be numbers, at least one is not. Three traps to check out:

1. A blank appended to the end (or beginning) of a string. (I predict this is the problem)
2. A letter O substituted for the number 0.
3. An uppercase letter I or lowercase letter l (L) substituted for the number 1.

As someone else said, if the values are always numbers, you should either change the field to a NUMBER data type, or at the very least use a CHECK constraint.

F.Th.G. Heijmans <no_F.Th.G.Heijmans_at_inter.nl.net_spam> wrote in message news:38257c6d.2982797_at_news.xs4all.nl...
> Hi,
>
> Can anyone tell me where this error-message comes from
>
> 1 select to_number(accountnr)
> 2 from invoces
> 3 where projectnr = 'R1099'
> 4 and to_number(accountnr) > 0
>
> TO_NUMBER(ACCOUNTNR)
> ---------------------
> 1040706
> 313102015
> 871540
> 1748160
> 446338508
> 337611602
> 485724
> 674154029
> 1166821
> 336036337
> 5015662
> 897975
> 1172139
> 811702634
> 572054114
> ERROR:
> ORA-01722: invalid number
>
> Accountnr is stored in the dabase as a varchar2(10). When I run the
> same query without the >0 there is no problem
>
> 1 select to_number(accountnr)
> 2 from invoces
> 3 where projectnr = 'R1099'
>
> This is not at all what I would expect. All the numbers are fine, I
> checked that. Appearantly ORA-01722 is not the real error.
>
> If I vary the size of the comparison (>0, >1000000) I get a different
> list, but it still stops after the 15th row with the same error.
>
> Is there anyone who can explain this to me?
>
> Regards,
>
> Frank
>
>
>
Received on Mon Nov 08 1999 - 08:50:52 CST