You need to sign in to do that
Don't have an account?
vishnu 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.
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.
You may have to use two different queries and add those results to single list as below,
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,
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:
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.
Did you try the solution which I suggested?
Thanks