Troubleshooting
Problem
A combination of cast and substring function fails with com.ibm.db2.jcc.am.SqlSyntaxErrorException on DB2.
This happens first after the migration from IBM Cognos Business Intelligence 10.2.2 to IBM Cognos Analytics 11.0 or 11.1, or after the migration from Compatible Query Mode to Dynamic Query Mode.
Example expression in a report:
cast(substring([Audit].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH]; 24; 1) || substring([Audit].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH]; 26; 4) as decimal(5))
Symptom
Report fails with error messages such as
XQE-DAT-0001 com.ibm.db2.jcc.am.SqlSyntaxErrorException: A value with data type "SYSIBM.LONG VARCHAR" cannot be CAST to type "SYSIBM.DECIMAL"
Document Location
Worldwide
[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}},{"Product":{"code":"SS6G84","label":"IBM Cognos Analytics on Cloud"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB76","label":"Data Platform"}},{"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB77","label":"Automation Platform"}}]
Log InLog in to view more of this document
This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.
Was this topic helpful?
Document Information
Modified date:
14 January 2026
UID
ibm11095916