0

I have knowledge on SQL but new to Salesforce and SOQL. I am facing issue with the below scenario in writing the SOQL query to fetch the details.

Scenario:

I have a Lead Table like below from where want to retrieve only the lead details which doesn't have any non-converted duplicate entries. (eg; only the yellow highlighted rows) Importantly I don't want to return the row even if it is converted because it has a duplicate non- converted entry at row 4.

Equivalent SQL query: SELECT FirstName, LastName, Email, Phone, MobilePhone from Lead WHERE FirstName NOT IN (SELECT FirstName FROM Lead WHERE IsConverted = false)

SOQL Error: MALFORMED_QUERY: NOT IN (SELECT FirstName FROM Lead WHERE IsConverted = false) ^ ERROR at Row:1:Column:111 The inner and outer selects should not be on the same object type

So What are the right/possible ways in the SOQL query?

enter image description here

1

SOQL does not allow such query, so you need to separate it into 2.

1) Gather leads and store their first names into Set.

List<Lead> leads = [SELECT FirstName FROM Lead WHERE IsConverted = false];
Set<String> leadsNames = new Set<String>();
for (Lead lead_i : leads) {
    leadsNames.add(lead_i.FirstName);
}

2) Now you can query:

SELECT FirstName, LastName, Email, Phone, MobilePhone from Lead WHERE FirstName NOT IN :leadsNames

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.