Error retrieving Contact list: Query Failed: SELECT

[jjgardner]

» Operating system type and version: Hosted
» Sugar version and edition 5.2.0.a
» apache Version:
» PHP version 5.2? don't know how to find it?
» MySQL server version 4.1.22


I get the following error after converting a Lead to a Contact, Account & Opportunity. After converting when I view the contact the screenit is blank and the following shows at the bottom of the contact page.

Error retrieving Contact list: Query Failed: SELECT
opportunities.id ,opportunities_cstm.fico_b_c,opportunities_cstm.lo an_appriased_value_c,opportunities_cstm.loan_purch ase_price_c,opportunities_cstm.credit_card_code_c, opportunities_cstm.birthdate_b_c,opportunities_cst m.birthdate_cb_c,opportunities_cstm.credit_card_na me_c,opportunities_cstm.credit_card_expiration_c,o pportunities_cstm.current_mtg_balance_1st_c,opport unities_cstm.current_mtg_impounds_included_c,oppor tunities_cstm.contact_id3_c,opportunities_cstm.cur rent_mtg_payment_2nd_c,opportunities_cstm.current_ mtg_payment_1st_c,opportunities_cstm.current_mtg_b alance_2nd_c,opportunities_cstm.current_mtg_rate_2 nd_c,opportunities_cstm.mailing_adress_city_c,oppo rtunities_cstm.mailing_adress_state_c,opportunitie s_cstm.mailing_adress_postalcode_c,opportunities_c stm.mailing_adress_country_c,opportunities_cstm.ma iling_adress_c,opportunities_cstm.current_home_add ress_city_c,opportunities_cstm.current_home_addres s_state_c,opportunities_cstm.current_home_address_ postalcode_c,opportunities_cstm.current_home_addre ss_country_c,opportunities_cstm.current_home_addre ss_c,opportunities_cstm.subject_property_address_c ity_c,opportunities_cstm.subject_property_address_ state_c,opportunities_cstm.subject_property_addres s_postalcode_c,opportunities_cstm.subject_property _address_country_c,opportunities_cstm.subject_prop erty_address_c,opportunities_cstm.rent_yes_type_c, opportunities_cstm.rent_amount_monthly_c,opportuni ties_cstm.legal_name_b_c,opportunities_cstm.legal_ name_cb_c,opportunities_cstm.ssn_b_c,opportunities _cstm.ssn_cb_c,opportunities_cstm.first_name_c,opp ortunities_cstm.last_name_c,opportunities_cstm.cur rent_mtg_year_purchased_c,opportunities_cstm.curre nt_mtg_purchased_price_c,opportunities_cstm.curren t_mtg_loan_program1st_c,opportunities_cstm.current _mtg_loan_program2nd_c,opportunities_cstm.story_bo ard_c,opportunities_cstm.loan_purpose_c,opportunit ies_cstm.loan_type_c,opportunities_cstm.occupancy_ c,opportunities_cstm.loan_program_c,opportunities_ cstm.loan_amount_1st_c,opportunities_cstm.loan_amo unt_2nd_c,opportunities_cstm.loan_program_2nd_c,op portunities_cstm.loan_servicer_1st_c,opportunities _cstm.loan_servicer_2nd_c,opportunities_cstm.loan_ payment_1st_c,opportunities_cstm.loan_payment_2nd_ c,opportunities_cstm.interest_rate_1st_c,opportuni ties_cstm.interest_rate_2nd_c,opportunities_cstm.r ate_margin_c,opportunities_cstm.rate_index_type_1s t_c,opportunities_cstm.index_type_2nd_c,opportunit ies_cstm.rate_cap_structure_c,opportunities_cstm.d ate_arm_reset_c,opportunities_cstm.date_io_reset_c ,opportunities_cstm.date_reset_balloon_c,opportuni ties_cstm.date_estimated_coe_c,opportunities_cstm. date_loan_closed_c,opportunities_cstm.date_rate_lo cked_c,opportunities_cstm.date_rate_lock_expires_c ,opportunities_cstm.credit_card_type_c,opportuniti es_cstm.loan_property_taxes_c,opportunities_cstm.l oan_ho_insurance_c,opportunities_cstm.loan_waive_e scrows_c,opportunities_cstm.loan_mi_insurance_c,op portunities_cstm.loan_servicer1st_loannumber_c,opp ortunities_cstm.loan_servicer2nd_loannumber_c,oppo rtunities_cstm.rate_margin_2nd_c,opportunities_cst m.date_first_payment_due_c,opportunities_cstm.cont act_id_c,opportunities_cstm.contact_id1_c,opportun ities_cstm.contact_id2_c,opportunities_cstm.curren t_mtg_rate_1st_c,opportunities_cstm.currentmtg_tax es_c,opportunities_cstm.currentmtg_mi_insurance_c, opportunities_cstm.currentmtg_ho_insurance_c,oppor tunities_cstm.ccbillingnumber_c,opportunities_cstm .subject_property_type_c,opportunities_cstm.testin g_decimal_c, opportunities.name , ' ' account_name , ' ' account_id , opportunities.sales_stage , opportunities.date_closed , opportunities.amount , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, opportunities.amount_usdollar , opportunities.assigned_user_id , 'opportunities' panel_name FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c LEFT JOIN users jt1 ON jt1.id= opportunities.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 INNER JOIN opportunities_contacts ON (opportunities.id=opportunities_contacts.opportuni ty_id AND opportunities_contacts.contact_id= '8bc92e04-2a6c-2670-2933-49b2b06b7778') where ( opportunities_contacts.deleted=0 AND opportunities.deleted=0) AND opportunities.deleted=0 ORDER BY opportunities.date_closed desc LIMIT 0,10::MySQL error 1054: Unknown column 'opportunities_cstm.testing_decimal_c' in 'field list'

[eggsurplus]

The error says at the bottom that testing_decimal_c does not exist in the opportunities_cstm table. I'd suggest running a repair/rebuild on the opportunities module and run the script that it comes back with. That should create the new column then.

[hdanielb]

I have a similar case, (Error retrieving Lead list: Query Failed) but the error at the final line says: "MySQL error 1222: The used SELECT statements have a different number of columns".
I have run all the possible rebuilds, and have similar errors, at the end of lead view, and account view. Here the whole error message:

Error retrieving Lead list: Query Failed SELECT meetings.id , meetings.name , 0 reply_to_status , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.parent_id , meetings.parent_type , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'meetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 INNER JOIN meetings_leads ON (meetings.id=meetings_leads.meeting_id AND meetings_leads.lead_id= '4b6e4403-3d28-8e4b-fc5d-4c72c0279773') where ( meetings_leads.deleted=0 AND meetings.deleted=0 AND (meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT meetings.id , meetings.name , 0 reply_to_status , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.parent_id , meetings.parent_type , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'oldmeetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( meetings.parent_id = '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' AND meetings.parent_type = 'Leads' AND meetings.id NOT IN ( SELECT meeting_id FROM meetings_leads ) AND (meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , 0 reply_to_status , tasks.status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name , tasks.contact_id , contacts.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, tasks.parent_id , tasks.parent_type , tasks.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' panel_name FROM tasks LEFT JOIN contacts contacts ON contacts.id= tasks.contact_id AND contacts.deleted=0 AND contacts.deleted=0 LEFT JOIN users jt1 ON jt1.id= tasks.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( tasks.parent_id= '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' AND tasks.parent_type='Leads' AND tasks.deleted=0 AND (tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , 0 reply_to_status , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.parent_id , calls.parent_type , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'calls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 INNER JOIN calls_leads ON (calls.id=calls_leads.call_id AND calls_leads.lead_id= '4b6e4403-3d28-8e4b-fc5d-4c72c0279773') where ( calls_leads.deleted=0 AND calls.deleted=0 AND (calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , 0 reply_to_status , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.parent_id , calls.parent_type , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'oldcalls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( calls.parent_id = '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' AND calls.parent_type = 'Leads' AND calls.id NOT IN ( SELECT call_id FROM calls_leads ) AND (calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT notes.id , notes.name , 0 reply_to_status , ' ' status , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) contact_name , notes.contact_id , contacts.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, notes.parent_id , notes.parent_type , notes.date_modified , ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename , notes.created_by , 'notes' panel_name FROM notes LEFT JOIN contacts contacts ON contacts.id= notes.contact_id AND contacts.deleted=0 AND contacts.deleted=0 where ( notes.parent_id= '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' AND notes.parent_type='Leads' AND notes.deleted=0) AND notes.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.reply_to_status , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'emails' panel_name FROM emails LEFT JOIN users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 INNER JOIN emails_beans ON (emails.id=emails_beans.email_id AND emails_beans.bean_id= '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' AND bean_module='Leads') where ( emails_beans.deleted=0 AND emails.deleted=0) AND emails.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name , emails.reply_to_status , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'linkedemails' panel_name FROM emails LEFT JOIN users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 JOIN (select distinct email_id from emails_email_addr_rel eear join email_addr_bean_rel eabr on eabr.bean_id ='4b6e4403-3d28-8e4b-fc5d-4c72c0279773' and eabr.bean_module = 'Leads' and eabr.email_address_id = eear.email_address_id and eabr.deleted=0 where eear.deleted=0 and eear.email_id not in (select eb.email_id from emails_beans eb where eb.bean_module ='Leads' and eb.bean_id = '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' and eb.deleted=0) ) derivedemails on derivedemails.email_id = emails.id where emails.deleted=0 ) UNION ALL ( SELECT sms_sms.id , sms_sms.name , sms_sms.status , ' ' contact_name , ' ' contact_name_owner , ' ' contact_name_mod , ' ' account_id , ' ' dummyColumn11 , ' ' dummyColumn121 , ' ' dummyColumn131 , sms_sms.date_entered as date_entered , sms_sms.to_user_id as assigned_user_name , sms_sms.created_by , 'leads_sms_sms' panel_name FROM sms_sms INNER JOIN sms_relations ON (sms_sms.id=sms_relations.sms_id AND sms_relations.relation_id= '4b6e4403-3d28-8e4b-fc5d-4c72c0279773' AND sms_relations.relation_type='leads') where ( sms_relations.deleted=0 AND sms_sms.deleted=0 AND sms_sms.status ='Sent') AND sms_sms.deleted=0 ) ORDER BY date_modified desc LIMIT 0,10::MySQL error 1222: The used SELECT statements have a different number of columns

[davidboris]

Hello,

I guess you would have tried to change the fields in Forhistory.php files to have customize the history subpanel.

Undo the changes and get the subpanel back. To get an extra field in history subapnel, you have to modify all included modules.