"Bind Variables only allowed in Apex code" when comparing fields from two different tables, how to resolve this error?

640    Asked by Aashishchaursiya in Salesforce , Asked on May 11, 2023

I'm trying to show all the leads that have the same DUNS number like the record i'm currently on (account)

Select l.Name, l.Email, l.CompanyDunsNumber,l.Status From Lead l WHERE CompanyDunsNumber = (Select a.DunsNumber From Account a Where Id='0015E00000CsMZXQA3')

and i receive the error: Bind Variables only allowed in Apex Code

Answered by Clare Matthews

You are seeing this error - Bind Variables only allowed in Apex Code due to the field to field comparison in WHERE clause of SOQL.

I am not sure how you are obtaining the current Account Id but I am providing an example with this Id hardcoded (you should move away from this approach within your design) for you to move forward with. Let's start with creating a collection of Ids to store our found "Matching" Leads (based on DunsNumer). You will need to iterate over the Account(s) (plural once you have redesigned to pass in a List of Account Ids to work with rather than the single hardocded Id) and then for each Account we can query the Leads that match on DunsNumber.

  List leadsWithMatchingDUNS = new List(); for (Account acc : [select DunsNumber from Account where Id ='0012400000Max8P']) { Lead l = [select id from Lead where CompanyDunsNumber = :acc.DunsNumber]; if (l != null) { leadsWithMatchingDUNS.add(l.Id); } } System.debug(leadsWithMatchingDUNS);

You can follow on with this List of Lead Ids however you choose to do so. I am simply debugging for POC.


Your Answer

Interviews

Parent Categories