Can't use common table expressions with ActiveSQL data layer?
Using LOGI 14; Displaying charts and tables, we have some Oracle queries that take too long to run with a DataLayer.SQL element. The SQL uses common table expressions (CTE); Using ActiveSQL data layers is much faster, but if the query has a CTE - we get ORA-00972: identifier is too long message. There is NO identifier that is too long in the CTE or anywhere else in the query;
The query runs fine in an IDE/sqlplus.
We have many queries with CTEs in another analytics tool, and we don't want to rewrite them when (if) we port them to LOGI.
with x (x) as (select x from x)
select <col list>
from x join <table>
Hey Bruce Campbell
Sorry to be the bearer of bad news, but ActiveSQL doesn't allow CTEs. I've had to find some creative SQL methods to bypass this, but none of them are as straightforward or as efficient as a CTE. Perhaps others in the community have some experience or some options for you.
Thanks for the info Johnny!0
Please sign in to leave a comment.