Unable to evaluate a sql cohort query that contains multiple statements via reporting module

I created a SQL cohort query that contains two statements/queries where the second query is the one that returns the resultset I’m interested in but it seems like the reporting module is unable to evaluate it, @mseaton is this not supported? Below is an example of what the query can look like

SET @LAST_RUN = ":lastExecutionTime";

SELECT person_id
FROM person
WHERE
  (date_created > @LAST_RUN OR date_changed > @LAST_RUN)
  AND not voided;

Hi @wyclif,

That’s correct, we execute SQL-based definition queries using “statement.executeQuery();” rather than “execute” or “executeUpdate”. See the SqlQueryBuilder class. This is intentional in order to protect the database against accidental changes, accidental or not.

There is an open ticket about supporting stored procedures.

I’m happy to have a discussion about changing this For our Malawi implementation, I wrote an alternative MysqlCommandSqlDataSetDefinition implementation that bypasses JDBC and executes a SQL script natively in order to get around some of this (this may not be best practice). You can see what I did for that here:

Mike

That was helpful, thanks Mike!