You need to sign in to do that
Don't have an account?
Nakata
SOQL - How to query with multiple sub-query ?
Good day,
I would like to query some account which are filter by multiple criterias, not sure how can i do it
SELECT ID FROM Account WHERE id not in (select ...) AND id not in (select ....) AND id not in (select ...) AND id not in (select .....)
and i got the error like Maximum 2 semi-join sub-select are allow , anyone can enlighten me is this a limitation of SOQL ?
Thank you !
Yes there is a limit of 2 on semi joins. check this.
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_comparisonoperators.htm
you could query the IDs first and store the ID is in a list (or set?) and then query against that list.
(Edit: had this all wrong the first time)
You might end up with more than one exclusion list, or do multiple queries and add the results all to the same exclusion list because you cannot have more than 2 IN statements per query:
"Because a great deal of processing work is required for semi-join and anti-join queries, salesforce.com imposes the following restrictions to maintain the best possible performance:
No more than two IN or NOT IN statements per WHERE clause."
Thanks , but the query suppose run in schema browser/ SOQL explorer instead of coding side
I wrote folllowing and ran it in Batchable context.
List<Id> archiveAccountList = new List<Id>();
List<RecordType> rtypes = [Select name, id FROM RecordType WHERE sObjectType = 'Account' AND IsPersonType = True];
for(Account acc:[Select Acc.ID, Acc.Type from ACCOUNT Acc where recordtypeid IN:rtypes AND
Acc.Id in (Select ORD.AccountId From ORDER ORD where ORD.Archive__c = True)
AND Acc.Id IN (Select CSE.AccountId From CASE CSE where CSE.Archive__c = True) ])
{
archiveAccountList.add(ACC.id);
}
List<Id> excludeArchiveAccountList = new List<Id>();
for(Account acc:[Select Acc.ID, Acc.Type from ACCOUNT Acc where acc.ID IN: archiveAccountList AND
Acc.Id in (Select Asst.AccountId From ASSET Asst Where Asst.UsageEndDate >: dateCheckTodayPlus1)])
{
excludeArchiveAccountList.add(ACC.Id);
}
for(Account acc:[Select Acc.ID, Acc.Type from ACCOUNT Acc where acc.ID IN: archiveAccountList AND
Acc.Id in (Select Asst.AccountId From ASSET Asst Where Asst.UsageEndDate <: dateCheckTodayPlus1)])
{
archiveAccountList.add(ACC.Id);
}
query = 'Select ACC.ID From ACCOUNT ACC where ACC.ID IN: archiveAccountList AND ACC.ID NOT IN: excludeArchiveAccountList';