21

I am trying to deploy around 80 Components which include Apex Class,Trigger and Visualforce Pages. But it is failing due to below exception,

MyTrigger: execution of BeforeInsert caused by: System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch Trigg...

All test classes are run as a part of deployement process hence few of the test classes are making this trigger fire causing error. As its a change set deployment error, could not get the complete error statement. Also my trigger includes below code.

if(trigger.isBefore & trigger.isInsert) {
        for(AggregateResult ar : [select ABC__c, Name, max(Revision__c) maxRev from XYZ__c 
                                    where ABC__c in :oppMap.keySet()
                                    group by ABC__c, Name order by ABC__c, Name]) {
            decimal maxRev = Integer.valueOf(ar.get('maxRev'));
            string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
            lrMap.put(oppAppKey, maxRev);
        }
    }

Let me know how to fix this. Should i try using limit or is there any other solution?

4 Answers 4

10

I updated my code as below and it worked for me.

 Integer recordCount = [select count() from XYZ__c where ABC__c in :oppMap.keySet()];
            Integer sizeBlock = recordCount/2000 + (math.mod(recordCount,2000)!=0?1:0) ;
            Set<Id> setAppIds = new Set<Id>(); 

            //Iterate aggregate result with blocks of 2000 to avoid exception
            for(Integer i=0;i<sizeBlock;i++){            
                for(AggregateResult ar : [select id,ABC__c, Name, max(Revision__c) maxRev from XYZ__c
                                            where ABC__c in :oppMap.keySet() and id not in :setAppIds 
                                            group by id,ABC__c, Name order by ABC__c, Name limit 2000]) {
                    decimal maxRev = Integer.valueOf(ar.get('maxRev'));
                    string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
                    lrMap.put(oppAppKey, maxRev);
                    setAppIds.add(Id.valueOf(String.valueOf(ar.get('id'))));
                }
            }

courtesy :- @Peter Knolle

2
  • 2
    I'm glad this worked for you. Just to be safe, you might want to double check that the usage of limit 2000 here isn't causing you to miss expected rows. I guess it will work since you are grouping by and subsequently excluding by Application__c.Id. Apr 19, 2014 at 9:14
  • @DanielBallinger Ya it does work with the help of grouping by & excluding id using set. Thanks 4 ua help.
    – snehakem
    Jul 21, 2015 at 6:39
2

All my searches for that error message indicated it was occurring during batch Apex. Is it possible this trigger if firing during batch processing?

If so, a short term fix might be to exclude the query in batch context using System.isBatch().


In the Nonprofit Stater Pack the same error message was recorded as an issue. The corresponding checkin/revision 483 showed the SOQL query changing from:

for (sobject r : Database.query(soqlStatement)){
    // ...
}

To (edited to show just the important parts):

//generic object list for database query results
//resolves aggregrate querymore issue
list<sobject> objectList = database.query(soqlStatement);
        
for (sobject r : objectList ) {
    // ...
}

So you might try:

List<sObject> results = [select ABC__c, Name, max(Revision__c) maxRev from XYZ__c 
                                where ABC__c in :oppMap.keySet()
                                group by ABC__c, Name order by ABC__c, Name];
for(sObject ar : results ) {
        decimal maxRev = Integer.valueOf(ar.get('maxRev'));
        string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
        lrMap.put(oppAppKey, maxRev);
}

As per comment from @peter-knolle to the official documentation:

Note that queries including an aggregate function don't support queryMore. A runtime exception occurs if you use a query containing an aggregate function that returns more than 2000 rows in a for loop. Source: SOQL For Loops Versus Standard SOQL Queries

So it is the combination of the SOQL for loop syntax and more than 2000 aggregate results that is causing the error.

9
  • hey @Daniel, i just tried to query in developer console query editor. It gave me the same error asking to put a limit. Does that mean the query is wrong. how am i supposed to overcome that.
    – snehakem
    Apr 18, 2014 at 9:55
  • 1
    How many XYZ__c records are there in production versus the environment you are deploying from? Apr 18, 2014 at 9:58
  • Sandbox - 6994 records Production - 7054 records
    – snehakem
    Apr 18, 2014 at 10:00
  • Strange part is that trigger code is getting covered by test class. It mean that code/query works. But it gives error while deployment.
    – snehakem
    Apr 18, 2014 at 10:21
  • 5
    Over the 2000 limit for Aggregate queries: "Note that queries including an aggregate function don't support queryMore. A runtime exception occurs if you use a query containing an aggregate function that returns more than 2000 rows in a for loop." Apr 18, 2014 at 10:32
1

Since you are already using in your SOQL statement a GROUP BY and ORDER BY clause, an alternative approach is to add LIMIT and OFFSET:

Integer batchSize = 2000;
Integer nrOfRecordsProcessed = 0;

Integer rowsReturned = batchSize;
while (rowsReturned == batchSize) {
  rowsReturned = 0;
  for(AggregateResult ar : [select ABC__c, Name, max(Revision__c) maxRev from XYZ__c 
                            where ABC__c in :oppMap.keySet()
                            group by ABC__c, Name order by ABC__c, Name 
                            LIMIT :batchSize OFFSET :nrOfRecordsProcessed]) {
    decimal maxRev = Integer.valueOf(ar.get('maxRev'));
    string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
    lrMap.put(oppAppKey, maxRev);
    rowsReturned++;
  }
  nrOfRecordsProcessed = nrOfRecordsProcessed + rowsReturned;
}

This way you don't need to modify the query with a different grouping criteria and an additional condition. Also, you don't need the additional Set and neither the first SOQL to get the COUNT().

0

Implementing Iterable interface instead of Query Locator in batch class.

global class AggregateResultIterator Implements Iterator<AggregateResult>{ 

AggregateResult [] results {get;set;} 
Integer index {get;set;} 

global AggregateResultIterator(){ 
String query = 'select Id, Max(ModifiedDate) from Campaign GROUP BY Id '; 
results = Database.query(query); 
} 

global boolean hasNext(){ 
return results !=null && !results.isEmpty() && index < results.size(); 
} 

global AggregateResult next(){ 
return results[index++]; 
} 
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .