Data conversion issue in Oracle database

Question

I am trying to create a view in Oracle EBS database from different tables, and the view has few columns that are calculated type particularly there are two columns in which the base tables are off combination of varchar type and number type and the resulting view has the type as Number type for this two columns I have tried to avoid errors while data conversion from type two number type by using

TO_NUMBER(revenue, DEFAULT 0 ON CONVERSION ERROR)

And if this view is queried, I’m getting the results in oracle as expected, but when trying to query from DVS like Denodo I am seeing an error that there is a non-numeric type in place where a numeric type is expected for these two row. if I remove these two columns, I am able to get the results perfectly on the Denodo side but when I include these two columns, I am getting the same error. I’ve tried various options, but no luck, but when the view is converted as a table on the Oracle side, and queried from DENODO it is working fine. I don’t understand where the issue is.

The calculated columns in the view are :

DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1,    DECODE (
                           attribute7,
                           NULL, DECODE (trans_source,
                                         'Payroll', quantity,
                                         'Eff_Report', quantity,
                                         'Shops', quantity,
                                         0),
                           TO_NUMBER (attribute7 DEFAULT 0 ON CONVERSION ERROR)),
                NVL (quantity, 0))

and

DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1, NVL (quantity, 0),
                DECODE (
                    attribute7,
                    NULL, DECODE (trans_source,
                                  'Payroll', quantity,
                                  'Eff_Report, quantity,
                                  'Shops', quantity,
                                  0),
                   TO_NUMBER(attribute7 DEFAULT 0 ON CONVERSION ERROR)))

FYI attribute7 is of varchar type and quantity is of Number type.

When the view is queried from Denodo, I am getting

[JDBC ROUTE] [ERROR] Received exception with message 'ORA-01858: a non-numeric character was found where a numeric was expected'

Answer 1

That error is not coming from the TO_NUMBER() calls, it's coming from the TO_DATE() calls.

The ORA-01858 error is described as:

Cause
The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.

Action
Fix the input data or the date format model to ensure that the elements match the number and type. Retry the operation.

So the issue is the date format model, which you have not provided, so it's using an implicit format model, not the number conversion part.

Doing TO_DATE ('20-JUN-2010') relies on your sessions' NLS settings, and those are different in the two clients/applications where you are testing this. That applies to the date format model, causing this error, and (as you are using a month abbreviation) the date language.

You could do a more explicit conversion:

SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH'))

but with a fixed value it would be simpler to use a date literal:

SIGN (expenditure_item_date - DATE '2010-06-20'),

fiddle

Answer 2

Too long for a comment but you can simplify your query using CASE rather than nested DECODEs and inverting some of the conditions:

CASE
WHEN expenditure_item_date <= DATE '2010-06-20' OR expenditure_item_date IS NULL
THEN COALESCE(quantity, 0)
WHEN attribute7 IS NOT NULL
THEN TO_NUMBER (attribute7 DEFAULT 0 ON CONVERSION ERROR))
WHEN trans_source IN ('Payroll', 'Eff_Report', 'Shops')
THEN quantity
ELSE 0
END