Fatal Error Creating Smart Group

[sibro]

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']"] )

[Donald Lobo]

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

[sibro]

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.