I am building a custom report that, due to its complexity, requires multiple SQL statements including the creation of temporary tables. I have developed the queries in MySQL workbench and they work as intended.
However, when embedding these statements in the postProcess() function of a subclass of CRM_REPORT_FORM and passing the query to $this->buildRows the query fails with "DB Error: syntax error". Can anyone point me to an example of a report that uses multiple SQL statements or advise how to properly construct such a report?
If it helps, the rough outline of the SQL I need to handle is as follows:
CREATE TEMPORARY TABLE all_grades AS (SELECT ......
CREATE TEMPORARY TABLE all_grades_copy SELECT * FROM all_grades;
CREATE TEMPORARY TABLE most_recent_sequence SELECT .....
SELECT.....FROM all_grades, INNER JOIN most_recent_sequence ON ....JOIN all_grades_copy ON .... GROUP BY .....