function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
vishnu nvvishnu nv 

Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions

Hi, 
  I am trying to write SOQL for below case but facing  issue "Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions".

Use case is to retreive the opportunity where Amount > 1000 (OR) OpportunityFieldHistory where FIELD='StageName' AND CreatedDate  LAST_N_DAYS:14.

My Query:

SELECT Id, Name from opportunity where  Amount > 1000 OR Id IN(SELECT OpportunityId from OpportunityFieldHistory WHERE Field='StageName' AND CreatedDate=LAST_N_DAYS:14))

Can you please help, how this can be acheived.
Sai PraveenSai Praveen (Salesforce Developers) 
Hi Vishnu,

You may have to use two different queries and add those results to single list as below,
 
SELECT Id, Name from opportunity where   Id IN (SELECT OpportunityId from OpportunityFieldHistory WHERE Field='StageName' AND CreatedDate=LAST_N_DAYS:14)
 
SELECT Id, Name from opportunity where  Amount > 1000

Add the two list to single list.

Let me know if you face any issues.

If this solution helps, Please mark it as best answer.

Thanks,
Shri RajShri Raj
The error message you're receiving is because Salesforce only allows semi-join sub-selects in the top-level WHERE clause and not in nested WHERE clauses. In your case, the semi-join sub-select is being used in the nested WHERE clause.
To achieve your use case, you can try using a left outer join instead of a semi-join sub-select. Here's an example SOQL query that should work for you:
SELECT Id, Name
FROM Opportunity
LEFT OUTER JOIN OpportunityFieldHistory
    ON Opportunity.Id = OpportunityFieldHistory.OpportunityId
        AND OpportunityFieldHistory.Field = 'StageName'
        AND OpportunityFieldHistory.CreatedDate = LAST_N_DAYS:14
WHERE Opportunity.Amount > 1000
    OR OpportunityFieldHistory.Id != null

n this query, we're performing a left outer join between the Opportunity and OpportunityFieldHistory objects, filtering on the criteria you provided. Then we're using the WHERE clause to filter the results where either the opportunity amount is greater than 1000 or the OpportunityFieldHistory record exists.
vishnu nvvishnu nv
Hi Shri Raj, I am unable to run this SOQL query in Developer console -> Query Editor. I am getting this error "Unknown error parsing query"
Sai PraveenSai Praveen (Salesforce Developers) 
Hi Vishnu,

Did you try the solution which I suggested?

Thanks
 
vishnu nvvishnu nv
Hi Sai Praveen, I have used different solution, As I dont want to do call API  Call Twice