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";
(date_created > @LAST_RUN OR date_changed > @LAST_RUN)
AND not voided;
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:
That was helpful, thanks Mike!