Oracle Analytics Idea Lab

Home› Oracle Analytics and AI› Oracle Analytics and AI Idea Labs› Oracle Analytics Idea Lab

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Enable CTE Query in OAC Dataset

Needs Votes
32
Views
2
Comments
Rakesh Joshi-Oracle
Rakesh Joshi-Oracle Rank 2 - Community Beginner
in Oracle Analytics Idea Lab

Currently OAC Dataset which connect to Oracle SQL Database does not CTE queries. Requesting this feature which will allow user to write and break down complex queries into smaller, more manageable parts, significantly improving readability, maintainability, and the ability to reuse calculated results within a single query, making it easier to understand and debug complex data manipulation tasks

Tagged:
6
Up
6 votes

Needs Votes · Last Updated

Comments

  • Michal Zima
    Michal Zima Rank 7 - Analytics & AI Coach

    @Rakesh Joshi-Oracle I understand you requirement (we are "fighting" with this limitation as well), but sorry to say, this is more limitation of Oracle DB , since it does not support nested CTE queries (queries with multiple WITH clauses). BI Server (when generating physical SQL queries passing it to DB) always "wraps" query using WITH clause, so you if you create data set (more precisely table in data sets) out of your custom SQL having WITH clause, Oracle DB will produce error , complaining about usage of nested WITH clause (ORA-32034: unsupported use of WITH clause). Changing behavior of BI Server (core component of OAS/OAC) not to wrap generated queries in WITH clause would mean quite substantial effort with big implications I guess…. So not easy to handle this requirement.

  • User_ERL72
    User_ERL72 Rank 1 - Community Starter

    Bumping this back up as it was one of the major benefits of other BI tools like Tableau and PowerBI