Print this page

Aggregate queries and Batch Apex

Knowledge Article Number 000192834
Description
Aggregate queries do not work in Batch Apex. They run into the error "Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch"
Resolution
There are two ways  to run the Aggregate Query 
 
1. Run the "Aggregate Query" in a schedule class by using @Read annotation. 
 
EX: 
 
Schedule Class: 
----------------------- 
global class MySchedulableClass implements Schedulable{ 
 
@ReadOnly 
global void execute (SchedulableContext ctx){ 
List<AggregateResult> query = [Select MIN(CreatedDate) CD from Account]; 
 
DateTime dt; 
 
for(AggregateResult ar : query){ 
dt = (DateTime)ar.get('CD'); 
 
RunQuery rq = new RunQuery(dt); 
Database.executeBatch(rq); 
 
Batch Class: 
------------------ 
 
global class RunQuery implements Database.Batchable<sObject> { 
 
global DateTime dt; 
 
global RunQuery(DateTime dt){ 
this.dt = dt; 
 
global Database.QueryLocator start(Database.BatchableContext BC){ 
System.debug('const' + dt); 
 
Set<DateTime> dtime = new Set<DateTime>(); 
dtime.add(dt); 
 
DateTime createdDate; 
String query; 
query = 'select Id from Account where CreatedDate =: dt'; 
return Database.getQueryLocator(query); 
 
global void execute(Database.BatchableContext BC, List<sObject> scope){ 
for(sObject s : scope){ 
System.debug('----------' + s); 
 
global void finish(Database.BatchableContext BC){ 
AsyncApexJob a = 
[Select Id, Status, NumberOfErrors, JobItemsProcessed,TotalJobItems,CreatedBy.Email FROM AsyncApexJob WHERE Id = :BC.getJobId()]; 
System.debug('********: ' + a.Id); 
 
 
2. Implementing Iterable interface instead of Query Locator in batch class 
 
Iterable class: 
-------------------- 
global class AggregateResultIterable implements Iterable<AggregateResult>{ 
global Iterator<AggregateResult> Iterator(){ 
return new AggregateResultIterator(); 
 
Iterator Class: 
------------------------ 
global class AggregateResultIterator Implements Iterator<AggregateResult>{ 
 
AggregateResult [] results {get;set;} 
Integer index {get;set;} 
 
global AggregateResultIterator(){ 
String query = 'select Id, MIN(createdDate) from Account GROUP BY Id LIMIT 1'; 
results = Database.query(query); 
 
global boolean hasNext(){ 
return results !=null && !results.isEmpty() && index < results.size(); 
 
global AggregateResult next(){ 
return results[index++]; 
 
Batch Class: 
------------------- 
global class RunQuery implements Database.Batchable<AggregateResult> { 
 
global Iterable<AggregateResult> start(Database.BatchableContext BC){ 
return new AggregateResultIterable(); 
 
global void execute(Database.BatchableContext BC, List<sObject> scope){ 
for(sObject s : scope){ 
System.debug('----------' + s); 
 
global void finish(Database.BatchableContext BC){ 
AsyncApexJob a = 
[Select Id, Status, NumberOfErrors, JobItemsProcessed,TotalJobItems,CreatedBy.Email FROM AsyncApexJob WHERE Id = :BC.getJobId()]; 
System.debug('********: ' + a.Id); 
 




promote demote