PM81158: NATIVE SQL FAILS WITH ORA-32039: RECURSIVE WITH CLAUSE MUST HAVE COLUMN ALIAS LIST.

APAR status

• Closed as Permanent restriction.

Error description

• Validating or testing Native SQL fails in Framework Manager if
  the Query Subject has the same name as the underlying table and
  the WITH clause is enabled in the project governors.??This query
  contains an error and can not be executed.??It is recommended
  that you view the query feedback on the "Query Information"
  tab.??RQP-DEF-0177 An error occurred while performing operation
  'sqlPrepareWithOptions' status='-9'.?UDA-SQL-0107 A general
  exception has occurred during the operation
  "prepare".?ORA-32039: recursive WITH clause must have column
  alias list
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Because the querysubject uses native sql, which we never     *
  * parse, the query subject name is used as the 'with clause'   *
  * query name. In this case the query subject name is the same  *
  * as the table name so oracle throws error.                    *
  * To workaround this error, user can either turn off 'with     *
  * clause' or use a different query subject name than the table *
  * name. Another solution could be to use 'cognos sql' instead  *
  * of 'native sql'.                                             *
  *                                                              *
  * In order to revert to the old behavior, customer can use the *
  * entry below to control whether treating a native SQL as a    *
  * pass-through, or to convert common table expression as a     *
  * derived table.                                               *
  *                                                              *
  * 1) The WITH clause is processed locally, the native SQL      *
  * becomes as a pass-through which is only SQL being pushed to  *
  * underlying database                                          *
  * Native_SQL_In_CTE="PT"                                       *
  *                                                              *
  * 2) The WITH clause is eliminated, and convert all common     *
  * table expression to be derived tables.                       *
  * Native_SQL_In_CTE="DT"                                       *
  *                                                              *
  * 3) This is default behavior where native SQL is part of WITH *
  * clause being pushed to unlerying database.                   *
  * Native_SQL_In_CTE="KEEP"                                     *
  ****************************************************************
  

Problem conclusion

• No Intent to Fix
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels