Troubleshooting
Problem
Reports being upgraded to Cognos 10.2 that contain typed-in SQL (either in the report or in the FM model) can encounter this error if the typed-in SQL contains a With clause, and isn't flagged as "pass-through".
Symptom
ORA-32034: unsupported use of WITH clause
Cause
Cognos 10.2 may try to take typed-in SQL that is flagged as "Native" (the default), and use it within a derived table so that we can make the database perform the joins required for the report. But if the typed-in SQL use the SQL With clause already, this will cause an error (since you can't nest derived tables).
Environment
Cognos 10.2 using reports designed in previous versions of Cognos and running against an Oracle database. Similar errors are seen against other database that support derived tables, but have errors specific to those databases.
Resolving The Problem
Switch the SQL setting on the query subject or report query object from "native" to "pass-through"
The difference between "Native" and "Pass-through" relates to Cognos's expectation of being able to take the supplied SQL and embed it with our own generated SQL for better performance. If the typed in SQL contains query or processing hints, it should always be flagged as "Pass Through", even if the SQL appears to work using "Native", otherwise future changes to the Cognos query engine could cause the SQL to begin to fail.
Note that using typed-in SQL requires that the report and query objects be maintained by the developers; Cognos does not have the ability to analyze user-supplied SQL, and cannot guarantee the functionality or performance of the product when using these objects.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21632795