IBM Support

XQE-DAT-0001 Data source adapter error: ORA-01722: invalid number

Troubleshooting


Problem

When changing from CQM to DQM, existing reports get error : XQE-DAT-0001 Data source adapter error: java.sql. SQLSyntaxErrorException: ORA-01722: invalid number

Symptom

During DQM Migration,
Existing reports with Oracle datasource start failing with error
XQE-DAT-0001 Data source adapter error: java.sql.
SQLSyntaxErrorException: ORA-01722: invalid number

Cause

in CQM, parameters were internally converted as number which is not the case in DQM.

Environment

Cognos BI 10.2.2
Oracle
DQM

Diagnosing The Problem

If there is some calculation based on the parameter which requires typecasting, this issue occurs. In DQM, parameter needs to be converted to number before using it.

Resolving The Problem

Instead of parameter ?parameter1? use the expression to_number(?parameter1?) in order to get rid of this issue.

Eg:

to_char({sysdate} + ?DaysUntillExpire?,'YYYY-MM-DD')

To

to_char({sysdate} + to_number(?DaysUntillExpire?),'YYYY-MM-DD')

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"10.2;10.2.1;10.2.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21964350

Page Feedback