Answers.Body: <p>@Dino62 - I dont think you can execute multiple SQL at the same time </p>
  
  <p>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 </p>
  
  <p>Use the below to run the SQL against Database separately </p>
  
  <pre><code>CRM_Core_DAO::executeQuery( $query, $sqlParams );
  </code></pre>
  
  <p>If the above is not helping then try to use </p>
  
  <pre><code>function alterRow( &amp;$row ) {
      $row['sort_name'] .= ' ( altered )';
  }
  </code></pre>
  
  <p>where you can handle each row individually </p>
  
  <p>I guess this helps !!! </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-07-21T12:51:00.907Z
Answers.Text: Thank you! Using executeQuery to create the temp tables and then using buildRows for the final query worked perfectly.
Answers.UserDisplayName: Dino62
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-07-21T12:01:23.773Z
Answers.LastActivityDate: 2016-07-21T12:01:23.773Z
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Body: <p>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.  </p>
  
  <p>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?</p>
  
  <p>If it helps, the rough outline of the SQL I need to handle is as follows:</p>
  
  <p>CREATE TEMPORARY TABLE all_grades AS (SELECT ......</p>
  
  <p>CREATE TEMPORARY TABLE all_grades_copy SELECT * FROM all_grades;</p>
  
  <p>CREATE TEMPORARY TABLE most_recent_sequence SELECT .....</p>
  
  <p>SELECT.....FROM all_grades, INNER JOIN most_recent_sequence ON ....JOIN all_grades_copy ON .... GROUP BY .....</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-07-21T08:42:58.100Z
Comments.Text: Please refer: 
  https://wiki.civicrm.org/confluence/display/CRMDOC42/Create+a+Custom-Search+Extension
Comments.UserDisplayName: Ramesh - ARTECH Consultancy
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-07-21T11:45:29.947Z
Comments.Text: @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.)
Comments.UserDisplayName: Dino62
Tags: <civireport>
Title: Multi statement queries in Civireport
ThreadText: # Multi statement queries in Civireport
  
  (Asked by: username_0 on 2016-07-21)
  
  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 .....
  
  **Tags:** civireport
  
  > **Comments:**
  > **username_1**: Please refer:  https://wiki.civicrm.org/confluence/display/CRMDOC42/Create+a+Custom-Search+Extension  
  > **username_0**: @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.)  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-07-21. Score: 0 | Accepted answer
  
  @username_0 - 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 !!!
  
  > **Comments:**
  > **username_0**: Thank you! Using executeQuery to create the temp tables and then using buildRows for the final query worked perfectly.  
  
  ---
  
  