Troubleshooting
Problem
A report references a query that contains a UNION set operation between queries.
One query references a DB2 user-defined function with an input using a DB2 olap style aggregate function, like count (distinct column for report).
One query references a DB2 user-defined function with an input using a DB2 olap style aggregate function, like count (distinct column for report).
Db2 will throw an exception, which is displayed by Dynamic Query.
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: The operands of a set operator or a VALUES clause do not have the same number of columns.
Cause
Due to a DB2 defect in version 11.1, reference number wsbbu01459432/APAR is wsdbu01459433/IT35380.
An example of the kind of SQL where the DB2 error is returned is:
SELECT 1 as NUM,
'A' as TXT from sysibm.dual
'A' as TXT from sysibm.dual
union
SELECT DISTINCT
"TEST"."NUM" AS "Vermögenswert_Objekt_ID",
dbcert.finvchar( count(1) over () ) AS "Bewertungsbetrag"
FROM ( Select 1 as NUM, 'A' as TXT, '1' as TXT_NUM from sysibm.dual) "TEST"
In the above SQL, dbcert.finvchar is the user-defined function, and count(1) over () is the olap style aggregate.
Document Location
Worldwide
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m50000000Cl5uAAC","label":"Reporting->Dynamic Query Mode and XQE"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
Modified date:
05 March 2021
UID
ibm16404680