CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • Report and Search UI Discussion (Moderators: CiviTeacher.com, TwoMice) »
  • Fatal Error Creating Smart Group
Pages: [1]

Author Topic: Fatal Error Creating Smart Group  (Read 815 times)

sibro

  • I post occasionally
  • **
  • Posts: 47
  • Karma: 2
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5
  • PHP version: 5.4
Fatal Error Creating Smart Group
July 26, 2014, 05:22:06 am
I created a custom search which runs fine. When I try to create a smart group the creation fails trying to create a temporary table. Claims iit can't find contact_a.id Using civi 4.4.5

Code: [Select]
$Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle ) [code] => -19 [message] => DB Error: no such field [mode] => 16 [debug_info] =>
 CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1956 (SELECT 1289 as group_id, club_id_7, cc.id ,
cc.addressee_display
 as club_name, club_title_40 as officer_position, ccb.last_name, ccb.addressee_display as officer_name , ccb.id as contact_id
FROM civicrm_contact cc,civicrm_relationship cr,civicrm_contact ccb, civicrm_value_extra_contact_info_1 ce,
 civicrm_value_club_position_5 rcv WHERE cc.contact_sub_type='club' and cc.id=ce.entity_id and fjmcaffil_19=1
and cr.id=rcv.entity_id
and cr.contact_id_b=cc.id and cr.relationship_type_id=62 and cr.end_date is NULL and cr.is_active=1
and cr.contact_id_a=ccb.id
AND club_title_40 IN ('Club President','Club Co-President','Club Treasurer')
ORDER BY club_id_7, ccb.last_name AND contact_a.id NOT IN ( SELECT contact_id FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Removed' AND civicrm_group_contact.group_id = 1289 ) );
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery'] [type] =>
DB_Error [user_info] => CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1956 (SELECT 1289 as group_id,

 club_id_7, cc.id , cc.addressee_display as club_name, club_title_40 as officer_position, ccb.last_name, ccb.addressee_display as
officer_name , ccb.id as contact_id FROM civicrm_contact cc,civicrm_relationship cr,civicrm_contact ccb, civicrm_value_extra_contact_info_1 ce, civicrm_value_club_position_5 rcv WHERE cc.contact_sub_type='club' and cc.id=ce.entity_id
and fjmcaffil_19=1 and cr.id=rcv.entity_id and cr.contact_id_b=cc.id and cr.relationship_type_id=62 and cr.end_date is NULL and cr.is_active=1 and cr.contact_id_a=ccb.id AND club_title_40 IN ('Club President','Club Co-President','Club Treasurer')
ORDER BY club_id_7, ccb.last_name AND contact_a.id NOT IN ( SELECT contact_id FROM civicrm_group_contact WHERE
civicrm_group_contact.status = 'Removed' AND civicrm_group_contact.group_id = 1289 ) );
[nativecode=1054 ** Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery'] [to_string] =>
[db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix=""
info="CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1956 (SELECT 1289 as group_id, club_id_7, cc.id ,
cc.addressee_display as club_name, club_title_40 as officer_position, ccb.last_name, ccb.addressee_display as officer_name ,

ccb.id as contact_id FROM civicrm_contact cc,civicrm_relationship cr,civicrm_contact ccb, civicrm_value_extra_contact_info_1 ce,
civicrm_value_club_position_5 rcv WHERE cc.contact_sub_type='club' and cc.id=ce.entity_id and fjmcaffil_19=1
and cr.id=rcv.entity_id and cr.contact_id_b=cc.id and cr.relationship_type_id=62 and cr.end_date is NULL and cr.is_active=1
and cr.contact_id_a=ccb.id AND club_title_40 IN ('Club President','Club Co-President','Club Treasurer')
ORDER BY club_id_7,
 ccb.last_name AND contact_a.id NOT IN ( SELECT contact_id FROM civicrm_group_contact WHERE civicrm_group_contact.status =
'Removed' AND civicrm_group_contact.group_id = 1289 ) );
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery']"] )
Bruce

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Fatal Error Creating Smart Group
July 26, 2014, 01:08:10 pm

can u change:

civicrm_contact cc

TO

civicrm_contact contact_a

(and other changes is your sql due to this) and see if it helps

thanx

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

sibro

  • I post occasionally
  • **
  • Posts: 47
  • Karma: 2
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5
  • PHP version: 5.4
Re: Fatal Error Creating Smart Group
July 27, 2014, 08:50:53 am
Lobo, I have not made the change you requested yet.

The smart group must have been created but did not show in the list. I believe they are automatically regenerated over night as the smart group appeared in the list along with all the contacts.  Do I still need to see if there is a issue with the query.
Bruce
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • Report and Search UI Discussion (Moderators: CiviTeacher.com, TwoMice) »
  • Fatal Error Creating Smart Group

This forum was archived on 2017-11-26.