Troubleshooting
Problem
a. to_char
b. chr
c. nvl
d. instr
Cause
Environment
Diagnosing The Problem
Resolving The Problem
The report was using Oracle specific function like "to_char" in the join expression, and since the join cannot be pushed to the database the to_char function is failing.
Example SQL for DQM:
QueryA as (Select ColumnA from DatabaseA )
QueryB as (Select ColumnB from DatabaseB)
Select QueryA.ColumnA , QueryB.ColumnB
from QueryA, QueryB
where to_char(QueryA.ColumnA ) = QueryB.ColumnB <--"This will be proceed locally by Cognos."
CQM handles the query differently, it does not try and handle the to_char function in the join expression, it pushes it down in the Select Statement.
Example SQL for CQM
QueryA as (Select to_char(ColumnA) from DatabaseA )
QueryB as (Select ColumnB from DatabaseB)
Select QueryA.ColumnA , QueryB.ColumnB
from QueryA, QueryB
where QueryA.ColumnA = QueryB.ColumnB
DQM is stricter in terms of it's SQL rules. To workaround this, you should move the to_char like function to the data item expression and not in the join expression or you can use the Cognos Cast function.
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
29 July 2019
UID
ibm10961300