0

CiviCRM ver. 5.18.2 (or 5.14.2) on Wordpress.

Hi all.

When Balance Due is selected in Pledge Report > Details we get a syntax error. Here is the error log:

 [error] $Fatal Error Details = Array
(
[callback] => Array
    (
        [0] => CRM_Core_Error
        [1] => handle
    )
[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, pledge_civireport.id as civicrm_pledge_id, pledge_civireport.contact_id as civicrm_pledge_contact_id, pledge_civireport.amount as civicrm_pledge_amount, pledge_civireport.currency as civicrm_pledge_currency, pledge_civireport.status_id as civicrm_pledge_status_id, COALESCE(sum(pledge_payment_civireport.actual_amount), 0) as civicrm_pledge_payment_total_paid, IF(pledge_civireport.status_id IN(3, ), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civireport.actual_amount),0)) as civicrm_pledge_payment_balance_due, (address_civireport.street_number % 2) as civicrm_address_address_odd_street_number  
        FROM civicrm_pledge pledge_civireport
             LEFT JOIN civicrm_contact contact_civireport
                  ON (contact_civireport.id =
                      pledge_civireport.contact_id )

    LEFT JOIN civicrm_pledge_payment pledge_payment_civireport ON
      pledge_civireport.id = pledge_payment_civireport.pledge_id
      AND pledge_payment_civireport.status_id = 1

             LEFT JOIN civicrm_address address_civireport
                       ON (contact_civireport.id =
                           address_civireport.contact_id)  AND
                           address_civireport.is_primary = 1
WHERE (pledge_civireport.is_test=0 )   GROUP BY pledge_civireport.id, pledge_civireport.currency  ORDER BY contact_civireport.sort_name, contact_civireport.id  LIMIT 0, 50 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civire' at line 1]
[type] => DB_Error
[user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, pledge_civireport.id as civicrm_pledge_id, pledge_civireport.contact_id as civicrm_pledge_contact_id, pledge_civireport.amount as civicrm_pledge_amount, pledge_civireport.currency as civicrm_pledge_currency, pledge_civireport.status_id as civicrm_pledge_status_id, COALESCE(sum(pledge_payment_civireport.actual_amount), 0) as civicrm_pledge_payment_total_paid, IF(pledge_civireport.status_id IN(3, ), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civireport.actual_amount),0)) as civicrm_pledge_payment_balance_due, (address_civireport.street_number % 2) as civicrm_address_address_odd_street_number  
        FROM civicrm_pledge pledge_civireport
             LEFT JOIN civicrm_contact contact_civireport
                  ON (contact_civireport.id =
                      pledge_civireport.contact_id )

    LEFT JOIN civicrm_pledge_payment pledge_payment_civireport ON
      pledge_civireport.id = pledge_payment_civireport.pledge_id
      AND pledge_payment_civireport.status_id = 1

             LEFT JOIN civicrm_address address_civireport
                       ON (contact_civireport.id =
                           address_civireport.contact_id)  AND
                           address_civireport.is_primary = 1
WHERE (pledge_civireport.is_test=0 )   GROUP BY pledge_civireport.id, pledge_civireport.currency  ORDER BY contact_civireport.sort_name, contact_civireport.id  LIMIT 0, 50 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), 0, COALESCE(pledge_civireport.amount, 0) - COALESCE(sum(pledge_payment_civire' at line 1]"]

Is this a bug? or is it the result of errors in setting the fields?

Thx&Bye.

  • You posted the backtrace, but not the error itself. Could you please post the error? And per Stack Exchange guidelines, could you please edit your question to post the backtrace here instead of on Pastebin? Otherwise it's harder for others to find this question if they have the same problem. Thanks! – Jon G - Megaphone Tech Oct 18 '19 at 13:32
  • @JonG-MegaphoneTech - Thanks for your directions, I changed everything. – Alessandro Letizia Oct 18 '19 at 18:16
2

This is a bug. It only happens when you have modified the name of the pledge status "Completed" or "Cancelled". In your case, "Completed" has been modified.

While you could change that label back, the report shouldn't crash regardless. It was easy to fix, so I reported the issue then made the fix.

You literally need to change one word in one file, so if you feel comfortable changing the contents of a file in your CiviCRM directory, you can look at the fix I made and make the change on your site. If you do, please report back on Github that it works - that will speed up the process of getting this fixed merged into CiviCRM for everyone.

| improve this answer | |
  • We do not feel confident about changing the code. Meanwhile we restored "Completed" instead of "Completato" (translate by I10n) in "pledge_status" options and everything works. Thank you and we hope that the bug resolution request will be implemented. – Alessandro Letizia Oct 19 '19 at 7:17
  • 1
    @AlessandroLetizia The fix has been accepted, it will be in CiviCRM 5.20 and above. – Jon G - Megaphone Tech Oct 19 '19 at 17: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.