5

This error is killing me :(

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

(or)

Semi join sub-selects are not allowed with the 'OR' operator

Here is my code. Can someone help?

Note :- I can't convert the select query that I have written in my where clause to a seperate query (Where Id in (select.... ) and get the data in an id set and substitute in the below query, because my id set has about 35000 records and when I try to query with that set it throws a Time limit exceeded error as it is unable to handle even more than a 1000 or 2000 records in a set if it is in an SOQL IN clause.

And I have just given a sample code here by getting the count, I need to get the count as well as the actual data..



Id CompanyId = '001Z000000CF2PtIAL';
 String SearchText = '%test%';
 Set<Id> SearchgroupassetidSet = new Set<ID>();
 SearchgroupassetidSet.add('a7aZ00000004CHhIAM');
 SearchgroupassetidSet.add('a7aZ00000004CHiIAM');
 SearchgroupassetidSet.add('a7aZ00000004CHjIAM');
 SearchgroupassetidSet.add('a7aZ00000004CHkIAM');
 Integer RecCount=0;
  List<AggregateResult> lstaggResult = [Select count(id) RecCount  FROM Asset__c where  Id IN (Select Asset__c from Customer_Asset__c where isDeleted =false and Company__c =: CompanyId and (Asset__r.Title__c like : SearchText or Asset__r.Name like : SearchText)) 
 or (Group__c IN : SearchgroupassetidSet and ((Title__c like : SearchText) or (Name like : SearchText)))];

  if (lstaggResult.size() > 0)
    {
        RecCount = (Integer)lstaggResult[0].get('RecCount');                        
    }
    System.debug('Record Count : ' + RecCount);


4

As you state, normally you need to do the subquery separately and store the IDs in a set you can then use in the main query:

set<Id> AssetSet = new set<Id>();

for(Customer_Asset__c asset : [Select Asset__c from Customer_Asset__c where isDeleted = false and Company__c =: CompanyId and (Asset__r.Title__c like : SearchText or Asset__r.Name like : SearchText)])
{
  AssetSet.add(asset.Asset__c);
} 

List<AggregateResult> lstaggResult = [Select count(id) RecCount  FROM Asset__c where  Id IN 
: AssetSet or (Group__c IN : SearchgroupassetidSet and ((Title__c like : SearchText) or (Name like : SearchText)))];

However, if you're sure you can't do this then it would suggest that you may need to use batch apex to do the first (sub) query, leveraging database.Stateful to help with managing the data.

Alternatively look for a way to leverage other fields to reduce the scope of the query.

Important note

In your code you've got a lot of hard coded IDs, e.g. Id CompanyId = '001Z000000CF2PtIAL'; and SearchgroupassetidSet.add('a7aZ00000004CHhIAM');

You should be aware that this is considered bad practice, because once you try to move this code to a different org, say a production environment, these IDs will no longer be valid and as such your code will fail to run.

  • @Matt Thanks for your answer. I know that I have hard coded :) I have already mentioned that in my question itself.. this is just a test code.. I used another query to populate that set.. The problem is that i need to pull the data out from this query in to a list and add it to a List<Wrapperclass> type and bind it to a jqgrid in VF Page. so unfortunately i cant use batch apex here :( Any other suggestions? – Sathya Sep 6 '12 at 9:51
  • I'm not sure of what your main goal is, but it seems to be generating some kind of analysis view of the data in question. I would use a batch to roll up my numbers into a custom object and then just display the latest info on the visualforce page. You could schedule the batch to keep it reasonably up to date, and even provide a button for users to kick it off manually if need be. – Matt Lacey Sep 6 '12 at 13:17
  • Actually I have to show some asset data that is stored in object and whatever has been assigned for each customer i need to show that in a jqgrid in visualforce page in sites – Sathya Sep 6 '12 at 19:09
0
  1. apex:page's readOnly="true" probably won't help you.
  2. All the LIKE look as if they are the reason of timeouts, not only the size of Set of Ids.. A SOSL query might be better in your case and you can still use WHERE clauses in it to further filter down the results. The question is whether you're fine with displaying "over 200 results" on your Site or does it have to be 100% accurate?
  3. Maybe some kind of Analytic Snapshot can save you. Either a real one (based on a scheduled report) or code solution. Batch job or trigger (even with @future) to recalculate some total and insert aggregated helper data somewhere?
  4. Bit more hardcore - try to apply small limit on it (say LIMIT 10) and if it works - consider using a standardSetController, queryMore(), maybe even paginated solution?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.