Categories
OAC - Dataset WITH syntax in sql not supported

Hello,
Do you have any idea why WITH Sql syntax is not accepted in OAC Sql based Dataset?
There is a plan to make evolve this and accept it in the futures OAC versions ?
Thanks,
Daniel
Answers
-
Hi
Using WITH clause in SQL syntax for dataset is not supported. This is because OAC wraps the user's SQL query inside another query to limit the number of rows fetched from the DB, but the Oracle DB does not support WITH clauses nested inside of other select statements( It will give error "ORA-32034: unsupported use of WITH clause" )
To use queries containing a WITH clause, one option is to first create a view based on the WITH clause query and then using the view to create the data set in OAC.
For example,
create or replace mytable_v as
with d1 as (select * from mytable) select * from d1
Then the mytable_v view can be used to create a data set in OAC.
Thanks
Gayathri
6 -
0