Skip to main content

Error - 'ORA-01722: invalid number' when running a report

Insight report will run correctly for one company. An error displays when running for another: ORA-01722: invalid number.

J
Written by Joshua Shooter
Updated over a month ago

This error message usually appears because an existing DECODE calculation based on Person Status treated all data as a number. As Person Status can be a number of characters (e.g. 1, 2, A, Z etc.) a mismatch of datatypes existed.
​
There are two options to resolve this issue:

Option 1

Add single quotes to the DECODE calculation so all data is treated as characters and not numbers.

E.g. Update this calculation:

DECODE(Person Type, 1, Balance, 2, Balance, 'A', Balance, 0) to

DECODE(Person Type, '1', Balance, '2', Balance, 'A', Balance, 0)

Option 2

Systematically remove the columns and see which column is causing the issue. If it is a large report, do it in batches to narrow it down.

Did this answer your question?