Can't use common table expressions with ActiveSQL data layer?

Bruce Campbell

• May 18, 2022 17:13

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.

E.g. 

with x (x) as (select x from x)

select <col list>

from x join <table>

where ...

 

Thanks!

0

• 

  Johnny Stevens

  • Logi Info Ninja
  • Logi Forum Guru
  • May 18, 2022 18:37

  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.

  Regards,
  Johnny

  0
• 

  Bruce Campbell

  • May 18, 2022 18:42

  Thanks for the info Johnny!

  0

Please sign in to leave a comment.