0

Why is this SOQL query not working?

I'm trying to retrieve duplicate accounts, by I need the Id for each specific account that's why I didn't just use the subselect query which would give me a grouped result.

SELECT Id, ebMobile__ERPAccountNumber__c
FROM Account
WHERE Id IN (
    SELECT Id
    FROM Account
    WHERE ebMobile__ERPAccountNumber__c != null
    GROUP BY ebMobile__ERPAccountNumber__c
    HAVING COUNT(Id) > 1
)

Salesforce Inspector returns this error:

MALFORMED_QUERY:
GROUP BY ebMobile__ERPAccountNumber__c
^
ERROR at Row:7:Column:4
expecting a right parentheses, found 'GROUP'

Dev Console just says: Unknown error parsing query

1 Answer 1

0

Although not explicitly documented, the error is making it clear that you can't use GROUP BY in a semi-join. Likewise, you cannot use HAVING or COUNT() in a subquery (though these limitations are documented).

The documentation here is the Considerations for Semi-Join and Anti-Join queries section of the Comparison Operators page in the SOQL and SOSL reference.

I don't think there's a way for you to avoid using 2 separate queries here (one to get the duplicated ebMobile__ERPAccountNumber__c values, one to get the Account Ids that contain one of those values.

This will potentially be difficult to do if you're just working with the developer console. If I found myself with this task, I would:

  • Use Dataloader to export all Account records that have a non-null value for your target field (you'd only need the Id and your target field)
  • Open the resulting CSV in Excel
  • Create a "pivot table" (containing both the Id and your target field)
  • Add your target field and Id to the "rows" of the pivot table (in that order)
  • Add your target field to the "values" of the pivot table (and make it count the number of occurrences)
  • Click outside of the pivot table, then click "sort & filter" and select "filter" (this should give you the ability to filter on the "Count of [your field here]" column in the pivot table
  • Filter out the rows of the pivot table where the count = 1
1
  • Hi Derek, after some research I found out that you can't query and subquery the same object at the same time. I ended up solving this problem with an Apex Class. But thank you for your reply and for the link to the documentation. Commented Jun 2, 2023 at 19:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .