Multi statement queries in Civireport

Question

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 .....

Answer 1

@Dino62 - I dont think you can execute multiple SQL at the same time

But still you can split the SQL in to different task and create a temp table and input all the values which you want to display in the report

Use the below to run the SQL against Database separately

CRM_Core_DAO::executeQuery( $query, $sqlParams );

If the above is not helping then try to use

function alterRow( &$row ) {
    $row['sort_name'] .= ' ( altered )';
}

where you can handle each row individually

I guess this helps !!!