Categories
Enable CTE Query in OAC Dataset
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
Comments
-
@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.
1
