AnsweredAssumed Answered

Sugar 6.5 Creating Invalid Queries

Question asked by Pat Pawlowski on Aug 15, 2017
Latest reply on Aug 15, 2017 by Pat Pawlowski

The following API call generates an error and when checking the Sugar log the SQL that it attempted to run is invalid. 

 

I have tried setting the selected_fields to a single field like id but it still attempts to query all the data with the same query as below. 

 

curl
-X POST https://crm.worldwidesupply.net/service/v4_1/rest.php
--header "Accept: application/json"
-F method=get_entry_list
-F input_type=JSON
-F response_type=JSON
-F rest_data='{"session":"gmedi9fjmk3k96nfg6602vqcb6", "module_name":"Leads", "query":" leads.date_modified > \"1970-01-01 00:00:00\"","order_by":"date_modified","offset":0,"selected_fields":[],"link_name_to_fields_array":[],"max_results":200,"deleted":0,"favorites":false}'

 

Raw CURL response:
Database failure. Please refer to sugarcrm.log for details.

 

sugarcrm.log entry. I modified this slightly. I included all 3 fields that are duplicated instead of just the first one.
--Thu Aug 3 14:12:16 2017 [13354][1][FATAL] The column 'campaign_id', 'contact_id_c', and 'user_id_c' were specified multiple times for 'a'.: 
SELECT TOP 41 * FROM
(
SELECT leads.* ,
leads_cstm.public_contact_url_c,
leads_cstm.last_email_click_date_c,
leads_cstm.haspuchused_c,
leads_cstm.emails_clicked_c,
leads_cstm.portaluser_c,
leads_cstm.recent_conversion_c,
leads_cstm.mrkto2_industry_c,
leads_cstm.last_email_open_date_c,
leads_cstm.behavioral_score_c,
leads_cstm.contact_id_c,
leads_cstm.demographic_score_c,
leads_cstm.import_filter_c,
leads_cstm.interest_c,
leads_cstm.mrkto2_annualrevenue_c,
leads_cstm.mrkto2_anonymousip_c,
leads_cstm.mrkto2_birthdate_c,
leads_cstm.mrkto2_do_not_call_reason_c,
leads_cstm.mrkto2_inferred_company_c,
leads_cstm.mrkto2_inferred_country_c,
leads_cstm.mrkto2_lead_id_c,
leads_cstm.mrkto2_lead_score_c,
leads_cstm.mrkto2_main_phone_c,
leads_cstm.mrkto2_middle_name_c,
leads_cstm.mrkto2_number_of_employees_c,
leads_cstm.mrkto2_rating_c,
leads_cstm.mrkto2_role_c,
leads_cstm.mrkto2_sic_code_c,
leads_cstm.mrkto2_site_c,
leads_cstm.mrkto2_sync_c,
leads_cstm.mrkto2_unsubscribed_c,
leads_cstm.mrkto2_unsubscribed_reason_c,
leads_cstm.mrkto2_website_c,
leads_cstm.part_numbers_c,
leads_cstm.rfq_form_comments_c,
leads_cstm.user_id_c,
LTRIM(RTRIM(ISNULL(leads.first_name,
'')+N' '+ISNULL(leads.last_name,
''))) as name ,
LTRIM(RTRIM(ISNULL(jt0.first_name,
'')+N' '+ISNULL(jt0.last_name,
''))) modified_by_name ,
jt0.created_by modified_by_name_owner ,
N'Users' modified_by_name_mod ,
LTRIM(RTRIM(ISNULL(jt1.first_name,
'')+N' '+ISNULL(jt1.last_name,
''))) created_by_name ,
jt1.created_by created_by_name_owner ,
N'Users' created_by_name_mod ,
LTRIM(RTRIM(ISNULL(jt2.first_name,
'')+N' '+ISNULL(jt2.last_name,
''))) assigned_user_name ,
jt2.created_by assigned_user_name_owner ,
N'Users' assigned_user_name_mod ,
ts1.team_count team_count ,
ts1.created_by team_count_owner ,
N'Teams' team_count_mod ,
LTRIM(RTRIM(ISNULL(tj.name,
'')+N' '+ISNULL(tj.name_2,
''))) team_name ,
tj.created_by team_name_owner ,
N'Teams' team_name_mod,
LTRIM(RTRIM(ISNULL(leads.first_name,
'')+N' '+ISNULL(leads.last_name,
''))) as full_name ,
jt5.name campaign_name ,
jt5.id campaign_id ,
jt5.assigned_user_id campaign_name_owner ,
N'Campaigns' campaign_name_mod,
N' ' c_accept_status_fields ,
N' ' call_id ,
N' ' m_accept_status_fields ,
N' ' meeting_id ,
leads_cstm.user_id_c,
LTRIM(RTRIM(ISNULL(jt8.first_name,
'')+N' '+ISNULL(jt8.last_name,
''))) assigned_scrubber_c ,
leads_cstm.contact_id_c,
LTRIM(RTRIM(ISNULL(jt9.first_name,
'')+N' '+ISNULL(jt9.last_name,
''))) trackreferral_c ,
LTRIM(RTRIM(ISNULL(jt10.first_name,
'')+N' '+ISNULL(jt10.last_name,
''))) modified_user_name ,
jt10.created_by modified_user_name_owner ,
N'Users' modified_user_name_mod ,
ROW_NUMBER()
OVER (ORDER BY leads.date_modified) AS row_number
FROM leads LEFT JOIN leads_cstm ON leads.id = leads_cstm.id_c LEFT JOIN users jt0 ON leads.modified_user_id=jt0.id AND jt0.deleted=0

 

AND jt0.deleted=0 LEFT JOIN users jt1 ON leads.created_by=jt1.id AND jt1.deleted=0

 

AND jt1.deleted=0 LEFT JOIN users jt2 ON leads.assigned_user_id=jt2.id AND jt2.deleted=0

 

AND jt2.deleted=0 LEFT JOIN team_sets ts1 ON leads.team_set_id=ts1.id AND ts1.deleted=0

 

AND ts1.deleted=0 LEFT JOIN teams tj ON leads.team_id=tj.id AND tj.deleted=0

 

AND tj.deleted=0 LEFT JOIN campaigns jt5 ON leads.campaign_id=jt5.id AND jt5.deleted=0

 

AND jt5.deleted=0 LEFT JOIN users jt8 ON leads_cstm.user_id_c = jt8.id AND jt8.deleted=0 LEFT JOIN contacts jt9 ON leads_cstm.contact_id_c = jt9.id AND jt9.deleted=0 LEFT JOIN users jt10 ON leads.modified_user_id=jt10.id AND jt10.deleted=0

 

AND jt10.deleted=0 where ( leads.date_modified > "1970-01-01 00:00:00" ) AND leads.deleted=0 
) AS a
WHERE row_number > 0

Outcomes