CiviCRM Community Forums (archive)

Hi all,

Attempting to build a report using the Case Detail report template generates a "DB Error: unknown error" if a 'Start Date' or 'End Date' filter is used AND the 'Study Enrolment Role(s)' are included in the report criteria.

The problem seems to be that both the  civicrm_case and civicrm_relationship tables have a start_date and end_date column, and the query isn't identifying which one is to be queried to define the WHERE clause.

Looking at the sql generated by a more complex query, I can see that the table is being referenced when the filter for case type or indeed any other filter is being applied, and the 'order by' clause also correctly references "case_civireport.start_date" not just "start_date". But filtering by Start or End date results in an ambiguous query if the civicrm_relationship table is also being queried.

I can fix this in a mysql console by adding the table alias into the WHERE clause of the query, but can I modify the report template php file to ensure the table (or the table alias used by CiviReport) is specified in the query as well as the column name?

Ta

Nick

* * *

Database Error Code: Column 'start_date' in where clause is ambiguous, 1052

Additional Details:

[debug_info] => SELECT SQL_CALC_FOUND_ROWS case_civireport.id as civicrm_case_id, case_civireport.subject as civicrm_case_subject, case_civireport.case_type_id as civicrm_case_case_type_id, contact_civireport.sort_name as civicrm_contact_client_sort_name, contact_civireport.id as civicrm_contact_id, GROUP_CONCAT(DISTINCT(relationship_civireport.relationship_type_id) ORDER BY relationship_civireport.relationship_type_id) as civicrm_relationship_case_role 
             FROM civicrm_case case_civireport
 LEFT JOIN civicrm_case_contact civireport_case_contact on civireport_case_contact.case_id = case_civireport.id
 LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = civireport_case_contact.contact_id
 
             LEFT JOIN  civicrm_relationship relationship_civireport ON relationship_civireport.case_id = case_civireport.id

             LEFT JOIN civicrm_address address_civireport
                    ON contact_civireport.id = address_civireport.contact_id AND
                       address_civireport.is_primary = 1  WHERE ( start_date >= 20121001 ) AND ( start_date <= 20121231 )  GROUP BY case_civireport.id   ORDER BY case_civireport.start_date DESC   LIMIT 0, 50 [nativecode=1052 ** Column 'start_date' in where clause is ambiguous]

Yes, the same happens on the demo. I enabled CiviCase, then selected from the report templates Case Detail Report template.  Of the display columns I selected only 'Case Role(s)' and of filters I selected only 'Start Date' -> 'Previous Quarter'. The preview of the report fails with a DB Error.

Does the JIRA use the same login details as the forums? I haven't logged a civi bug before.