function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Shrey TyagiShrey Tyagi 

SOQL - OR clause in sub query- Please Help!!!

Need help with following SOQL

Select Id,Name from Opportunity where
name != null
and
 (
    Id In (Select OpportunityId FROM OpportunityTeamMember where Name LIKE '%shrey%')
    OR
    (Proposal_Leader__r.Name='Shrey Tyagi')
 )
order by Name asc nulls first limit 50

Error : MALFORMED_QUERY: Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions.
Alain CabonAlain Cabon
1) You have noticed yet that without  OR (Proposal_Leader__r.Name='Shrey Tyagi')  
or without  Id In (Select OpportunityId FROM OpportunityTeamMember where Name LIKE '%shrey%')  OR
that works fine so the only solution without an aggregate query is two queries (the alternative OR is impossible here directly). 

2) An aggregate query uses OpportunityTeamMembers (plural) 
SELECT Id,Name, Proposal_Leader__r.Name, (SELECT OpportunityId FROM OpportunityTeamMembers where Name LIKE '%shrey%') 
FROM Opportunity 
WHERE name != null or Proposal_Leader__r.Name='Shrey Tyagi'
ORDER BY Name asc nulls first limit 50

Id In (Select OpportunityId FROM is implicit using an aggregate query.

Best regards

Alain
 
Shrey TyagiShrey Tyagi
Thanks a lot for your tima Alain . Please allow me to explain the scenatio here, it might be more helpful to you. I have a requirement where user is performing search of Opportunity records and he will give a text input e.g Shrey as used in soql . Now the query should take that input and search at 2 places. 

1. All related opportunity team member records: to see id any opportunity exists with such team member or not . That is why I used 
   
   Id In (Select OpportunityId FROM OpportunityTeamMember where Name LIKE '%shrey%')

2. A look up field ( looking up to the user ) exists on Opportunity record called Proposal _Leader__c . 

So the query should return all opportunity records where either existas a team member by name Shrey or the proposal leader's name is Shrey.

That is why I used  :   name != null and (condition 1 or condition 2)
Can you help me with this type of query ?
Alain CabonAlain Cabon
SOQL is not optimized for your need and you have written the best target query if SOQL could have processed it but it cannot.

The only solution is two queries.

Using an aggregate query, the filter is not optimized at all as you can see ( name != null​ means that nearly all the opportunities will be browsed ) but it is interesting to show it for the "theory" only (what could be the alternative for a single query ? there is no optimized one).

Alain
Shrey TyagiShrey Tyagi
Alain,
     Can you please help me split my query into 2  separate queries? name!=null was just for example.


Select Id,Name from Opportunity where
StageName != 'Closed Won'
AND
Sub_Stage__c='Awarded'
AND
 (
    Id In (Select OpportunityId FROM OpportunityTeamMember where Name LIKE '%shrey%')
    OR
    (Proposal_Leader__r.Name='Shrey Tyagi')
 )
order by Name asc nulls first limit 50

Thanks a lot for your help.

Regards
Shrey Tyagi
 
Alain CabonAlain Cabon
You have to unifiy the two result sets. That will be more complicated for your code in Apex than a single query.

1) First set: 
Select Id,Name from Opportunity where
StageName != 'Closed Won'
AND
Sub_Stage__c='Awarded'
AND
 (
    Id In (Select OpportunityId FROM OpportunityTeamMember where Name LIKE '%shrey%')
 )
order by Name asc nulls first limit 50

2) Second set: 
Select Id,Name from Opportunity where
StageName != 'Closed Won'
AND
Sub_Stage__c='Awarded'
AND
 (
    (Proposal_Leader__r.Name='Shrey Tyagi')
 )
order by Name asc nulls first limit 50

The problem is the OR with a join that SOQL cannot process.

Alain