I recently created a target list and added a bunch of names to the list, now I am getting an error:
01/19/17 11:17:05 [15304][1][FATAL] Error retrieving ProspectList list: Query Failed:SELECT TOP 10 * FROM
(
(SELECT contacts.id , contacts.first_name , contacts.last_name , contacts.salutation , LTRIM(RTRIM(ISNULL(contacts.first_name,’’)+N’ ‘+ISNULL(contacts.last_name,’’))) as name, N’ ’ account_name , N’ ’ account_id , contacts.phone_work , contacts.assigned_user_id , N’contacts’ panel_name,
(SELECT email_addresses.email_address
, ROW_NUMBER()
OVER (ORDER BY (select email_addresses.email_address
from email_addr_bean_rel
join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
where
email_addr_bean_rel.primary_address = 1 and
email_addr_bean_rel.deleted = 0 and
email_addr_bean_rel.bean_id = contacts.id and
email_addr_bean_rel.bean_module = N’contacts’) desc) AS row_number
FROM email_addr_bean_rel
JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id
WHERE
email_addr_bean_rel.primary_address = 1 AND
email_addr_bean_rel.deleted = 0 AND
email_addr_bean_rel.bean_id = contacts.id AND
email_addr_bean_rel.bean_module = N’Contacts’) as email1 FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c INNER JOIN prospect_lists_prospects ON contacts.id=prospect_lists_prospects.related_id AND prospect_lists_prospects.prospect_list_id=N’b8e9fb8c-68b1-29bb-6ba8-5880c40740ad’ AND prospect_lists_prospects.deleted=0
AND prospect_lists_prospects.related_type = N’Contacts’
where contacts.deleted=0)
) AS a
WHERE row_number > 0::: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name ‘row_number’.
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
01/19/17 11:17:05 [15304][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
I am now unable to add additional names to a target list.
• Did a quick repair
• Updated to the newest version
• Did another quick repair
• Yes, the permissions are correct.
The ID “b8e9fb8c-68b1-29bb-6ba8-5880c40740ad” is the ID of the target list that I am adding names to.
I am getting a similar error when I am trying to view the freshly created target list with new assigned targets:
Tue Dec 6 08:44:32 2022 [105627][91e48ff1-4c61-e0db-efd5-638ed8391c46][FATAL] Error retrieving ProspectList list: Query Failed: (SELECT prospects.id , prospects.first_name , prospects.last_name , LTRIM(RTRIM(CONCAT(IFNULL(prospects.first_name,''),' ',IFNULL(prospects.last_name,'')))) as full_name, prospects.title , prospects.phone_work , prospects.assigned_user_id , 'prospects' panel_name FROM prospects LEFT JOIN prospects_cstm ON prospects.id = prospects_cstm.id_c INNER JOIN prospect_lists_prospects ON prospects.id=prospect_lists_prospects.related_id AND prospect_lists_prospects.prospect_list_id='ab243a02-7e3c-7d70-b535-638ef0a56a7f' AND prospect_lists_prospects.deleted=0
AND prospect_lists_prospects.related_type = 'Prospects'
where prospects.deleted=0) ORDER BY prospects.last_name asc LIMIT 0,10: MySQL error 1250: Table 'prospects' from one of the SELECTs cannot be used in global ORDER clause
Tue Dec 6 08:44:32 2022 [105627][91e48ff1-4c61-e0db-efd5-638ed8391c46][FATAL] [ListView::processUnionBeans] . Datenbank Fehler. Bitte sehen Sie in der Datei suitecrm.log nach.
Tue Dec 6 08:45:01 2022 [111280][1][FATAL] Job 7ca26818-2ecc-6ecd-6f92-638ef2a68623 (Google Calendar Sync) failed in CRON run
The error is here: “where prospects.deleted=0)” the closing brace needs to be removed.
I can fix this error, but do not know where it is generated.
Can anybody point out the code part which generates this query?
Thank you for your help. I am just a newbie in SuiteCRM…
Thank you very much for your input.
I am aware that I am replying to an old thread. I think it makes sense to continue an existing thread with more up-to-date data than to open a new one.
Furthermore, I checked my version and it’s really 7.12.8
The fix was merged into the main branch on October the 27th, so this fix should be included in the 7.12.8 version with build date November 17th. But it is not. Strange thing!
I just commented the two lines myself and got it working now!
Thanks again for your help. It is much appreciated.
I did not submit the original pull request correctly.
I submitted a pull request between a sub branch and my own master branch not between my master branch and the main branch.
As a result the change was not merged with the main branch prior to the release of 7.12.8.
A corrected pull request has been submitted. Once reviewed and approved this issue should be resolved with the next release. Until then users will need to manually comment the lines.
