Support for WITH (CTE) Clauses in Manual SQL Queries in Oracle Analytics DV (OAS/OAC)

Dhaval Parikh Stantec

Current Limitation:
Currently, Oracle Analytics DV does not support the use of WITH (Common Table Expressions) clauses in manual SQL queries. We are are unable to define reusable subqueries at the beginning of their queries for easier readability and maintainability.

Proposed Feature:
Allow WITH (CTE) clauses in manual SQL queries within Oracle Analytics DV. This would enable users to define reusable subqueries before the main SELECT statement, making complex queries easier to write, understand, and maintain.

I think this will really help with improved query readability, better maintainability, alignment with SQL standards & enhanced performance optimization.

Bret Grinslade - Oracle Analytics-Oracle

Right now a work around might be to put this in a view.

sgeorge_otn

Upvote! Is this coming in the near future?

Michal Zima

Guys, this is more limitation of Oracle DB, which does not support nested WITH clauses (CTE) - when create DS table out of manual SQL, when executed by BI Server (execution component of OAS/OAC), this SQL is always wrapped (by BI Server) by WITH clause . And when your manual SQL already contains WITH clause, you hit this limitation of Oracle DB. I think, getting rid of this added WITH clause (by BI Server) would be very complicated change and thus more pressure should be made to DB team to add support of nested WITH clauses into SQL.