Resolving “semijoin subselect” errors

Salesforce’s SOQL implementation imposes some limitations on query structure, and this can cause unexpected errors. One example occurs when you try to use an “or” term in a filter, in conjunction with child objects. Here is a specific example:

In this example, the user wants all opportunities with certain specified stages, such that either the Opportunity’s Close Date is in a certain range, or there is at least one Payment (a child of Opportunity) whose Payment Date is in a certain range. So the filter logic is set up as 1 and (2 or 3). The filter is set up correctly, but causes Salesforce to throw an error:MALFORMED_QUERY: Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions.The reason for this error turns out to be the presence of the OR term nested inside a pair of parentheses. To work around this, we need to ensure that the OR term is not embedded within parentheses. So we recast the logical expression into an equivalent form, rewriting1 and (2 or 3)into the logically equivalent(1 and 2) or (1 and 3). This resolves the issue. See the corrected filter below.

Powered by BetterDocs