CiviCRM Community Forums (archive)

I'm setting up CiviCase on production (after running it successfully in sandbox) and when I select Other>Case>Dashboard, I get this verbose error:

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY case_scheduled_activity_date ASC' at line 63, 1064
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT
civicrm_case.id as case_id,
civicrm_case.subject as case_subject,
civicrm_contact.id as contact_id,
civicrm_contact.sort_name as sort_name,
civicrm_phone.phone as phone,
civicrm_contact.contact_type as contact_type,
civicrm_contact.contact_sub_type as contact_sub_type,
t_act.activity_type_id,
cov_type.label as case_type,
cov_type.name as case_type_name,
cov_status.label as case_status,
cov_status.label as case_status_name,
t_act.status_id,
civicrm_case.start_date as case_start_date,
case_relation_type.label_b_a as case_role,
t_act.desired_date as case_scheduled_activity_date,
t_act.id as case_scheduled_activity_id,
t_act.act_type_name as case_scheduled_activity_type_name,
t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
SELECT act3.case_id, act3.minid AS id, act_details.activity_date_time AS desired_date, act_details.activity_type_id,
act_details.status_id, aov.name AS act_type_name, aov.label AS act_type
FROM civicrm_view_case_activity_upcoming act_details INNER JOIN
(
  SELECT t.case_id, MIN(act2.id) as minid FROM civicrm_view_case_activity_upcoming act2 INNER JOIN
    (SELECT vu.case_id, MIN(vu.activity_date_time) AS mindate FROM civicrm_view_case_activity_upcoming vu
     GROUP BY vu.case_id ORDER BY NULL
    ) AS t
  ON act2.activity_date_time = t.mindate
  GROUP BY t.case_id ORDER BY NULL
) AS act3
ON act3.minid = act_details.id
LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
LEFT JOIN civicrm_option_value aov ON (aov.option_group_id = aog.id AND aov.value = act_details.activity_type_id)
) AS t_act
        ON t_act.case_id = civicrm_case.id
LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 1
      AND case_relationship.case_id = civicrm_case.id )

LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )
       
LEFT JOIN civicrm_option_group cog_type
 ON cog_type.name = 'case_type'

LEFT JOIN civicrm_option_value cov_type
 ON ( civicrm_case.case_type_id = cov_type.value
      AND cog_type.id = cov_type.option_group_id )

LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1)  AND civicrm_case.is_deleted = 0  AND case_relationship.contact_id_b = 1
AND civicrm_case.status_id !=   ORDER BY case_scheduled_activity_date ASC  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY case_scheduled_activity_date ASC' at line 63]
    [type] => DB_Error
    [user_info] => SELECT
civicrm_case.id as case_id,
civicrm_case.subject as case_subject,
civicrm_contact.id as contact_id,
civicrm_contact.sort_name as sort_name,
civicrm_phone.phone as phone,
civicrm_contact.contact_type as contact_type,
civicrm_contact.contact_sub_type as contact_sub_type,
t_act.activity_type_id,
cov_type.label as case_type,
cov_type.name as case_type_name,
cov_status.label as case_status,
cov_status.label as case_status_name,
t_act.status_id,
civicrm_case.start_date as case_start_date,
case_relation_type.label_b_a as case_role,
t_act.desired_date as case_scheduled_activity_date,
t_act.id as case_scheduled_activity_id,
t_act.act_type_name as case_scheduled_activity_type_name,
t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
SELECT act3.case_id, act3.minid AS id, act_details.activity_date_time AS desired_date, act_details.activity_type_id,
act_details.status_id, aov.name AS act_type_name, aov.label AS act_type
FROM civicrm_view_case_activity_upcoming act_details INNER JOIN
(
  SELECT t.case_id, MIN(act2.id) as minid FROM civicrm_view_case_activity_upcoming act2 INNER JOIN
    (SELECT vu.case_id, MIN(vu.activity_date_time) AS mindate FROM civicrm_view_case_activity_upcoming vu
     GROUP BY vu.case_id ORDER BY NULL
    ) AS t
  ON act2.activity_date_time = t.mindate
  GROUP BY t.case_id ORDER BY NULL
) AS act3
ON act3.minid = act_details.id
LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
LEFT JOIN civicrm_option_value aov ON (aov.option_group_id = aog.id AND aov.value = act_details.activity_type_id)
) AS t_act
        ON t_act.case_id = civicrm_case.id
LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 1
      AND case_relationship.case_id = civicrm_case.id )

LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )
       
LEFT JOIN civicrm_option_group cog_type
 ON cog_type.name = 'case_type'

LEFT JOIN civicrm_option_value cov_type
 ON ( civicrm_case.case_type_id = cov_type.value
      AND cog_type.id = cov_type.option_group_id )

LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1)  AND civicrm_case.is_deleted = 0  AND case_relationship.contact_id_b = 1
AND civicrm_case.status_id !=   ORDER BY case_scheduled_activity_date ASC  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY case_scheduled_activity_date ASC' at line 63]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT
civicrm_case.id as case_id,
civicrm_case.subject as case_subject,
civicrm_contact.id as contact_id,
civicrm_contact.sort_name as sort_name,
civicrm_phone.phone as phone,
civicrm_contact.contact_type as contact_type,
civicrm_contact.contact_sub_type as contact_sub_type,
t_act.activity_type_id,
cov_type.label as case_type,
cov_type.name as case_type_name,
cov_status.label as case_status,
cov_status.label as case_status_name,
t_act.status_id,
civicrm_case.start_date as case_start_date,
case_relation_type.label_b_a as case_role,
t_act.desired_date as case_scheduled_activity_date,
t_act.id as case_scheduled_activity_id,
t_act.act_type_name as case_scheduled_activity_type_name,
t_act.act_type AS case_scheduled_activity_type  FROM civicrm_case
                  INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
                  INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id  INNER JOIN
(
SELECT act3.case_id, act3.minid AS id, act_details.activity_date_time AS desired_date, act_details.activity_type_id,
act_details.status_id, aov.name AS act_type_name, aov.label AS act_type
FROM civicrm_view_case_activity_upcoming act_details INNER JOIN
(
  SELECT t.case_id, MIN(act2.id) as minid FROM civicrm_view_case_activity_upcoming act2 INNER JOIN
    (SELECT vu.case_id, MIN(vu.activity_date_time) AS mindate FROM civicrm_view_case_activity_upcoming vu
     GROUP BY vu.case_id ORDER BY NULL
    ) AS t
  ON act2.activity_date_time = t.mindate
  GROUP BY t.case_id ORDER BY NULL
) AS act3
ON act3.minid = act_details.id
LEFT JOIN civicrm_option_group aog ON aog.name='activity_type'
LEFT JOIN civicrm_option_value aov ON (aov.option_group_id = aog.id AND aov.value = act_details.activity_type_id)
) AS t_act
        ON t_act.case_id = civicrm_case.id
LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
LEFT JOIN civicrm_relationship case_relationship
 ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 1
      AND case_relationship.case_id = civicrm_case.id )

LEFT JOIN civicrm_relationship_type case_relation_type
 ON ( case_relation_type.id = case_relationship.relationship_type_id
      AND case_relation_type.id = case_relationship.relationship_type_id )
       
LEFT JOIN civicrm_option_group cog_type
 ON cog_type.name = 'case_type'

LEFT JOIN civicrm_option_value cov_type
 ON ( civicrm_case.case_type_id = cov_type.value
      AND cog_type.id = cov_type.option_group_id )

LEFT JOIN civicrm_option_group cog_status
 ON cog_status.name = 'case_status'

LEFT JOIN civicrm_option_value cov_status
 ON ( civicrm_case.status_id = cov_status.value
      AND cog_status.id = cov_status.option_group_id )
 WHERE (1)  AND civicrm_case.is_deleted = 0  AND case_relationship.contact_id_b = 1
AND civicrm_case.status_id !=   ORDER BY case_scheduled_activity_date ASC  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY case_scheduled_activity_date ASC' at line 63]"]
)



I searched the forum and did not find a reference to this error.

Thanks Hershel - I had added one more case status to production. I removed it, but the error persists.  Should I try dropping the table contents and reinsert the default status types? I don't have any cases in the system yet.

My pleasure.

lhubbert, can you post your precise error message?

Can you check what option values are in the database for case status, e.g.
<site>/civicrm/admin/options/case_status?group=case_status&reset=1

and if it's not obvious from there what's missing then it will be helpful to see the name column directly from the database, e.g.

select ov.name, ov.label from civicrm_option_value ov inner join civicrm_option_group og on ov.option_group_id=og.id
WHERE og.name='case_status';

I know this is an old thread, but we're seeing exactly the same error message when we try to use the CiviCase dashboard. On examination, it seems that the code

    if ($type == 'upcoming') {
      $closedId = CRM_Core_OptionGroup::getValue('case_status', 'Closed', 'name');
      $condition .= "
AND civicrm_case.status_id != $closedId";
    }

 
... in /CRM/Case/BAO/Case.php is expecting there to be one (and one only) option_value for case_status with the name 'Closed'. And it must be set is_active = 1. If it isn't (as ours are not, because we created entirely new case_status entries and set all the pre-set ones to is_active = 0, then the code above no longer works.

We can deal with this by setting is_active back to 1, and just not using the case_status of 'Closed' but I'm wondering if that behaviour is not a bit too rigid. Now that case_status option values have the concept of a Status Class ("grouping" in the database table) wouldn't it be better if the code above used the 'grouping' column to differentiate between cases? Obviously it would mean the results set might be an array not a single id, but it would be more resilient and flexible. Anyone else think this is an appropriate change?

Oh, so *that's* what 'reserved' means...