Complex Dynamic SQL Syntax Issue in HelicalInsight Metadata View

#1

Hello Helical Support Team,

I have encountered an issue when using dynamic SQL in Helical Insight.

I would like to ask how to properly write complex dynamic SQL in a metadata view.

For example, when using keywords such as DECLARE or WITH, the dynamic SQL execution fails with the following error: Error: [com.helicalinsight.efw.exceptions.EfwdServiceException: Couldn’t query the database, Couldn’t query the database, Incorrect syntax near ‘WITH’.]

Could you please advise on how to correctly implement complex dynamic SQL within a metadata view in Helical Insight?

Thank you in advance for your support.

#2

Hi Ruose,

Thank you for your question.

In Helical Insight, a dynamic metadata view is designed to execute a single SQL statement. Because of this, SQL constructs such as DECLARE, variable assignments, temporary procedural blocks, or other multi-statement scripts are not supported within a metadata view. These constructs typically require the database engine to execute multiple statements sequentially, whereas the BI engine expects a single executable SQL query. In fact almost all BI tools generally do not support this.

Regarding the WITH clause (Common Table Expression), it is generally supported by many databases and JDBC drivers (such as PostgreSQL) when used as part of a single SQL statement. However, support and syntax can vary depending on the database, JDBC driver, and the exact query structure.

Could you please let us know which database you are using (for example, PostgreSQL, SQL Server, MySQL, Oracle, etc.)? It would also be helpful if you could share the SQL query you are trying to execute. With that information, we can verify whether the syntax is supported and suggest the appropriate approach or an alternative implementation.

We look forward to your response.

Thank you