SYMPTOM:
When running a Freeform SQL report with multiple statements in the query body, the report fails with an error:
Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ORA-00933: SQL command not properly ended
Error in Process method of Component: QueryEngineServer, {Project Name}, Job xxx, Error Code= -2147212544. or
Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ORA-00911: invalid character
Error in Process method of Component: QueryEngineServer, {Project Name}, Job xxx, Error Code= -2147212544. This behaviour is seen for multipass SQL statements that combine statements that retrieve data and statements that do not retrieve data in Freeform SQL reports.
CAUSE:Multipass SQL statements in Freeform SQL are not supported in Strategy.
When a Freeform SQL report's query is submitted to the database, the entire query text is sent using a single ODBC SQLExecDirect call. Most databases expect only one statement per SQLExecDirect. Multiple queries combined in a single string are very likely to produce parsing errors.
There may be specific database and driver combinations that permit multiple statements in the same execution cycle. This handling occurs outside of Strategy and is not within Strategy's direct control. Therefore, it cannot be guaranteed that multipass Freeform SQL reports will be successful in every environment, even if they run to completion in certain environments.
WORKAROUND:The alternatives to creating multipass Freeform SQL reports are as follows:
- Rewrite the query to use Derived Table or Common Table Expression syntax.
- Use the Report Pre-statement VLDB property. Intermediate passes can be written one at a time or together separated by semi-colons in the Report Pre-statements. The limitation to this approach is that prompts cannot be used in the intermediate table expressions.
- Create Stored procedures. The limitation to this approach is that DBA intervention is required to create stored procedures.
