0

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

  • @Ramesh - the custom search capability appears to be similarly restrictive in that you define a single query. As noted above, I need to use multiple SQL statements, not just one. (Also, there seems to be a requirement that each row relates to a specific contact. The rows I eventually produce are an analytical summary of the data -- the rows do not contain any contact specific detail.) – Dino62 Jul 21 '16 at 11:45
0

@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 !!!

  • Thank you! Using executeQuery to create the temp tables and then using buildRows for the final query worked perfectly. – Dino62 Jul 21 '16 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.