ORA_01722 ERROR.

[RycherX]

I'm getting the
ERROR:
ORA-01722: invalid number

How do I find the record in my view that causing the error?
Obviously there has to be some alpha letters somewhere.

 

[SantaMufasa]

Rycher,

Please post the SQL statement that generates the error. Then we can post some suggestions that will help you isolate your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dagon]

You tend to get this if you're trying to convert a character string to a number and the character string has some non-digit characters in it. You can look for non-digit characters in your field with something like:

select * from table
where translate(column, 'a0123456789', 'a') is not null;

 

[RycherX]

select to_number(wavr01) Parent, wadoco child, 'C' Type
from proddta.F4801
where
WASRST = '60'
and WADCTO = 'TF'
and WAMMCU = lpad('101',12,' ')
and wavr01 <> lpad(' ',25,' ')


I get the error.

 

[Dagon]

As I suggested, try:

select * from proddta.F4801
where translate(wavr01, 'a0123456789', 'a') is not null
and WASRST = '60'
and WADCTO = 'TF'
and WAMMCU = lpad('101',12,' ')
and wavr01 <> lpad(' ',25,' ')

 

[SantaMufasa]

Rycher,

First of all, the fact that you have code with a WHERE clause that says:

...and wavr01 <> lpad(' ',25,' ')

...tells me that you might have 25 blank spaces in a column that you wish to convert to numbers. This would certainly cause you an error if you had 25 blanks in a TO_NUMBER argument:

SQL> select to_number(lpad(' ',25,' ')) from dual;
select to_number(lpad(' ',25,' ')) from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

So, this is a red flag in my mind, concerning your code.

To identify the extent to which you have rows that would throw the error that you are encountering, some variant of Dagon's code would be useful:

select count(*) from proddta.F4801
where translate([b]wavr01[/b], 'a0123456789', 'a') is not null;

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RycherX]

I went ahead and ran the following
select wavr01
from proddta.F4801
where
WASRST = '60'
and WADCTO = 'TF'
and WAMMCU = lpad('101',12,' ')
and wavr01 <> lpad(' ',25,' ')

**********************
Only 40 records popped up and saw 2 records that were foul.
I corrected the bad data since it has to be corrected anyways. Now my reports run correctly.

 

[Thargy]

RycherX,

since you have found a couple of dud records, may I suggest that you get a DBA to put some constraints on those columns, otherwise, sooner or later, you'll be doing this all over again.

Regards

Tharg

Grinding away at things Oracular