CODEWITHSUNDEEP
sqloracle-databaseviewcrystal-reports
dthunsterblich
dthunsterblich

Reputation: 1

ORA-01722 when retrieving Data from a View out of Crystal Reports

I built a view in SQLDeveloper on an Oracle database.

When I query the view it works without an error.

But when I query the view from Crystal Reports and try to retrieve data from a mixed calculation of varchar2 fields (containing only numeric entries) and numbers it throws the following Error: 22018

ORA-01722 Invalid Number

Table_A:

a Varchar2(20)
b Number

View:

SELECT a*b AS value_ab FROM TABLE A

Has anyone expirienced a similar issue? Changing the datatype is not an option. I also tried to convert the variable with "To_Number" but it didn't prevent the error from occuring.

Upvotes: 0

Views: 1474

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

You are storing numbers as strings (which is always a bad idea but you've already said you can't change that), and from comments they are float values with a period as the decimal separator. Your SQL Developer NLS settings make an implicit conversion valid, but your Crystal Reports NLS settings do not - although probably inherited from the PC's locale at some level, the relevant part here is that NLS_NUMERIC_CHARACTERS ends up as ',.' from there, vs. '.,' in SQL Developer.

You can probably change the NLS settings for your installation of Crystal Reports - I don't use it so can't tell you exactly how - but you'll still be relying on anyone else querying your view also having the 'right' NLS settings, which is not always going to be under your control.

If the strings are always in a fairly standard format, with a maximum scale and precision, then you can change the view definition to include the knowledge of the decimal separator used in the string values.

Essentially when you do a * b you're doing an implicit conversion, and you can see an explicit conversion also fails, with to_char(a) * b or cast(a as number) * b. Without changing the NLS settings both will get the ORA-01722.

You can either convert using a fixed mask with a period separator:

select to_number(a, '999999.99') * b as value_ab from table_a;

Or use the more generic separator symbol D and override the NLS settings, which is probably overkill in this case as the separator is fixed:

select to_number(a, '999999D99', 'NLS_NUMERIC_CHARACTERS=''.,''') * b
  as value_ab from table_a;

Either way you need to be able to specify the maximum number of digits you expect to see before and after the decimal separator, so you might need 9.99999 or 99999999.999 or some other variant - it depends entirely on your data.

Upvotes: 0

Related Questions