Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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.

share|improve this answer
    
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 at 9:55
1  
How many XYZ__c records are there in production versus the environment you are deploying from? –  Daniel Ballinger Apr 18 at 9:58
    
Sandbox - 6994 records Production - 7054 records –  snehakem Apr 18 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 at 10:21
3  
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." –  Peter Knolle Apr 18 at 10:32
up vote 0 down vote accepted

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

share|improve this answer
1  
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. –  Daniel Ballinger Apr 19 at 9:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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