Creating Custom SQL in RPD with "With clause" - Error

 In Oracle Analytics (OBI / OAS /OAC) we can define Model sources in RPD as tables/views, select statements or stored procedures. Customer wanted to use select statement as data source, and in that select to use a "With" clause. For example:

From the Model tool we can select "View Data" and see data perfectly. When creating analysis or projects on top of this we get the error:

 [nQSError: 17001] Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause at OCI call OCIStmtExecute. 

 or, if using OAC RDG (Remote Data Gateway)

[nQSError: 77031] Error occurs while calling remote service DatasourceService. Details: [JDSError : 115] Invalid Database Name - Cause: ORA-32034: unsupported use of WITH clause Action: Please refer to logs for more Details (HY000)

 

The full error text in my case is:

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 32034, message: ORA-32034: unsupported use of WITH clause at OCI call OCIStmtExecute. (HY000)

State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)

SQL Issued: SELECT 0 s_0, "test"."select_with"."Channel" s_1, "test"."select_with"."Month" s_2, "test"."select_with"."Prod_category" s_3, "test"."select_with"."Amount_sold" s_4, "test"."select_with"."Quantity_sold" s_5 FROM "test" FETCH FIRST 500001 ROWS ONLY

Checking the sessions its clear why this happens. Oracle Analytics tends to add "With" clause  in to the SQL it generates. The Database does allow a With clause within With clause and returns ORA-32034.

To control the generated SQL we have few interesting options in the RPD Model physical features.

• PERF_PREFER_INTERNAL_STITCH_JOIN
• PERF_PREFER_MINIMAL_WITH_USAGE
• FULL_OUTER_JOIN_SUPPORTED
• WITH_CLAUSE_SUPPORTED
  

See here for more. 

In our case we only care about PERF_PREFER_MINIMAL_WITH_USAGE.

Now Publish / Sign In the RPD and we are done.

The analysis is created without additional With clause.

Kurt Wolf had similar issue few years back that required a bit more changes: https://www.kpipartners.com/blog/bid/82442/Using-WITH-in-Physical-Tables-Opaque-Views