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
David Herrero 5David Herrero 5 

Using AggregateResult inside Execute method of a Batch Class

I have facing an error when I use an aggregateresult inside the execute method of a Batch Class.

I have being reviewing old post and I have read the solution provided here https://help.salesforce.com/apex/HTViewSolution?urlname=Aggregate-queries-and-Batch-Apex&language=en_US
but in that solution, the problem is in the start , not in the execute.

My batch class is like this :
global class BatchClass implements Database.Batchable<Sobject> {

global Database.QueryLocator start(Database.BatchableContext ctx) {
        String query = 'select a lot of records based in Ids and Dates';
        return Database.getQueryLocator(query);
    }
    global void execute(Database.BatchableContext ctx, List<Sobject> sObjects) {
    for(AggregateResult result : [select field1__c,field2__c, sum(Net_Revenue__c) r from Revenue__c where Type__c = 'C' where :condition group by Corporate_Account__c, Year_Month__c]){
            revenues.get((Id)result.get('c')).addRevenue((String)result.get('m'), (Decimal)result.get('r'));
        }

When I execute the class, I get this.

First error: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch

Any idea on how to resolve this?

Thanks
      
    }


}

Best Answer chosen by David Herrero 5
GarryPGarryP
I dont think taking QUERY outside for loop will solve this issue. Perhaps this is a recommended best practise to have a for loop with query.
Your query might be returning too many record set hence the error. as per my experience whenever any aggreate query results more than 2000 rows this error appears. You might have to use more selective query by adding indexed fields into where clause or additional filters which will still give you logical results you want to acheive.

also as susggested by UC innovation have a look at documentation around selective queries and optimize as per your need.

All Answers

UC InnovationUC Innovation
Hi David,

Ran into this issue myself once. Soql query for loops like that automatically use the querymore and query methods. I suggest moving the query above the for loop like this
  
AggregateResult[] results = [select field1__c
                                                               sum(Net_Revenue__c) r 
                                                   from Revenue__c 
                                                   where Type__c = 'C' 
                                                   ... 
                                                   group by Corporate_Account__c, 
                                                                    Year_Month__c]

for (AggregateResult result : results) {
// blah
}

This should resolve your issue. Another way might be to limit your results to just 1 as your error message suggests but doubt you want to do that since you put it in a loop for a reason.

My issue was slightly different but maybe the first part of this docu might give a bit more insight about whats going on when you use soql for loops like this.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

Hope this helps!

AM
GarryPGarryP
I dont think taking QUERY outside for loop will solve this issue. Perhaps this is a recommended best practise to have a for loop with query.
Your query might be returning too many record set hence the error. as per my experience whenever any aggreate query results more than 2000 rows this error appears. You might have to use more selective query by adding indexed fields into where clause or additional filters which will still give you logical results you want to acheive.

also as susggested by UC innovation have a look at documentation around selective queries and optimize as per your need.
This was selected as the best answer
David Herrero 5David Herrero 5
I will try to reduce the batchsize so the query will receive less records and I hope it won't fail.
Thank you for your responses.