+ Start a Discussion
Miranda L 2Miranda L 2 

getting error in Batch Too many query rows: 50001

Hello there,
Please help me to fix this error. My records not updating.

Apex script unhandled exception by user/organization: 0051X0000018hKG/00D1X0000008akn
Source organization: 00Db0000000HO6N (null)
Failed to process batch for class 'AllTimeRevenueAndYtDRevenueBatch' for job id '7071X00000FepEJ'

caused by: System.LimitException: Too many query rows: 50001

Class.AllTimeRevenueAndYtDRevenueBatch.execute: line 23, column 1
global class AllTimeRevenueAndYtDRevenueBatch implements Database.Batchable<sObject>,Database.Stateful,Schedulable {
global Decimal sum;
    public list<Account> oldInvoicesToUpdate = new list<Account>();
    public list<Account> invToUpdate = new list<Account>();
    public Set<ID> newEntitiesIds = new Set<ID>();
    global AllTimeRevenueAndYtDRevenueBatch(){}
        /* Batch class constructor */
    //Start method
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        //String query='SELECT id, Amount from Opportunity';
         String query='SELECT id, Normalized_Amount__c,Account__c from Invoice__c';
         return Database.getQueryLocator(query);
        }

    //Excute method
    
global void execute(Database.BatchableContext BC, List<Invoice__c> scope)
{
    //Excution logic
    //List<Opportunity> opp= new List<Opportunities>();
    //AggregateResult[] gr= [SELECT SUM(Amount) optyamt FROM Opportunity];
    AggregateResult[] gr= [SELECT SUM(Normalized_Amount__c) NormalizeAmount,Grouping(Account__c), Account__c
                                 FROM Invoice__c
                                 WHERE Category_Invoice__c != 'Monthly' AND Status__c != 'invoice_canceled' 
                                 GROUP BY Account__c];
    
    for(AggregateResult ag:gr){
          if((ID)ag.get('Account__c') != null){
                                         invToUpdate.add(new Account (
                                             ID = (ID)ag.get('Account__c'),
                                             //YtD_Revenue_for_Kantox__c = (Double)ag.get('NormalizeAmount'),
                                             AllTimeRevenueKantox__c = (Double)ag.get('NormalizeAmount')
                                             
                                         ));
                                     }
        
        //sum = (Decimal)ag.get('optyamt');
         
    }
    try{
            if(!invToUpdate.isEmpty()){
                update invToUpdate;
            }
        } catch(DmlException e) {
            System.debug('The following exception has occurred: ' + e.getMessage());
        }
    
}
      global void finish(Database.BatchableContext BC){
            // Finish logic
          system.debug(''+sum); 
       }
    public void execute(SchedulableContext Sc) {
        AllTimeRevenueAndYtDRevenueBatch batch = new AllTimeRevenueAndYtDRevenueBatch();
        Database.executeBatch(batch);    
    }
}

 
Deepali KulshresthaDeepali Kulshrestha
Hi Miranda,

​This issue occurs due to the setting of the SFDC governor limit, where only a maximum of 50000 records is allowed to be retrieved by SOQL Queries.
As more than 50000 records are returned in one data segment, this issue occurs.
Now I add limit in your SOQL query.
Try the following code, it may be helpful for you:
global class AllTimeRevenueAndYtDRevenueBatch implements Database.Batchable<sObject>,Database.Stateful,Schedulable {
global Decimal sum;
   
public list<Account> oldInvoicesToUpdate = new list<Account>();
    public list<Account> invToUpdate = new list<Account>();
    public Set<ID> newEntitiesIds = new Set<ID>();
    global AllTimeRevenueAndYtDRevenueBatch(){}
    
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
         String query='SELECT id, Normalized_Amount__c,Account__c from Invoice__c';
         return Database.getQueryLocator(query);
        }
    
global void execute(Database.BatchableContext BC, List<Invoice__c> scope)
{
    AggregateResult[] gr= [SELECT SUM(Normalized_Amount__c) NormalizeAmount,Grouping(Account__c), Account__c
                                 FROM Invoice__c
                                 WHERE Category_Invoice__c != 'Monthly' AND Status__c != 'invoice_canceled' 
                                 GROUP BY Account__c LIMIT 50000];
    
    for(AggregateResult ag:gr){
          if((ID)ag.get('Account__c') != null){
                                         invToUpdate.add(new Account (
                                             ID = (ID)ag.get('Account__c'),
                                             //YtD_Revenue_for_Kantox__c = (Double)ag.get('NormalizeAmount'),
                                             AllTimeRevenueKantox__c = (Double)ag.get('NormalizeAmount')
                                             
                                         ));
                                     }
        
    }
    try{
            if(!invToUpdate.isEmpty()){
                update invToUpdate;
            }
        } catch(DmlException e) {
            System.debug('The following exception has occurred: ' + e.getMessage());
        }
    
}
      global void finish(Database.BatchableContext BC){
            // Finish logic
          system.debug(''+sum); 
       }
    public void execute(SchedulableContext Sc) {
        AllTimeRevenueAndYtDRevenueBatch batch = new AllTimeRevenueAndYtDRevenueBatch();
        Database.executeBatch(batch);    
    }
}

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha
VamsiVamsi
Hi,

Please try the below 

AggregateResult[] gr= [SELECT SUM(Normalized_Amount__c) NormalizeAmount,Grouping(Account__c), Account__c FROM Invoice__c WHERE Category_Invoice__c != 'Monthly' AND Status__c != 'invoice_canceled' AND ID IN : scope GROUP BY Account__c];

In the above query I made use of scope to get only the appropriate invoices for batch execution.

Please mark as best answer if the above helps ..!!
Miranda L 2Miranda L 2
Thanks you @Deepali,
I am still getting the same error. First error: Too many query rows: 50001

Thanks
Miranda L 2Miranda L 2
Hi Vamsi,
I have tried your updated SOQL but I am now getting "First error: Duplicate id in list: 001b000000l8nV9AAI"
Could you please see my code and suggest where I am going wrong.
Thanks