Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Michele ToscanoMichele Toscano 

Aggregrate query has too many rows for assignment

How can I get around the aggregrate query error? When I tried taking out the For loop- the execution then failed. Is there a better way to do it?

global class Batch_ExpDate_PricIn implements Database.Batchable<sObject>,Database.Stateful
{
   global Database.QueryLocator start(Database.BatchableContext BC)
   {
        string manualExpStr = 'Manually Expired'; //Correct Status -11/2/16 MT
        string expiredStr = 'Expired'; 
        
        string query= 'select Id,RecordTypeId,RecordType.Name,Par_Status__c,Effective_date__c,Expiration_Date__c,(select Id,Expiration_Date_Change_To__c,Effective_date__c from Pricing_Inputs__r) from Price_Authorization_Request__c where Par_Status__c !=:manualExpStr  and Par_Status__c !=:expiredStr';
             return Database.getQueryLocator(query);
   }

   global void execute(Database.BatchableContext BC, List<Price_Authorization_Request__c> Parlist) {
            
       if(Parlist != null && !Parlist.isEmpty())
       {
              Map<String,string> maprecTypewithId = new Map<String,String>();
           List<Price_Authorization_Request__c> listPARToUpdate = new List<Price_Authorization_Request__c>();
           for(RecordType rec : [select id,Name from recordType where SObjectType = 'Price_Authorization_Request__c']) //-->system does not like this
           {
               maprecTypewithId.put(rec.Name,rec.id);
           }
           
           
           for(Price_Authorization_Request__c parObj : Parlist)
           {
            if(parObj.Pricing_Inputs__r != null && !parObj.Pricing_Inputs__r.isEmpty())
            {
                 Integer count = 0;
                 for(Pricing_Input__c PrcInputObj : parObj.Pricing_Inputs__r)
                 {
                    
                    if(PrcInputObj.Expiration_Date_Change_To__c != null && PrcInputObj.Expiration_Date_Change_To__c < system.today())
                    {
                        count = count + 1;
                    }
                 }
                 
                 if(count ==(parObj.Pricing_Inputs__r).size())  
                 {
                    parObj.Par_Status__c = 'Expired'; 
                  //  parObj.Expiration_Date__c=Date.valueOf(System.Today());
                    if(parObj.RecordType.Name == 'Standard PAR' && maprecTypewithId.get('ReadOnlyStandard PAR') != null)
                      parObj.RecordTypeId = maprecTypewithId.get('ReadOnlyStandard PAR');
                    else if(parObj.RecordType.Name == 'Formula PAR' && maprecTypewithId.get('ReadOnlyFormula PAR') != null)
                      parObj.RecordTypeId = maprecTypewithId.get('ReadOnlyFormula PAR');
                    listPARToUpdate.add(parObj);
                 }
            }
              
           }
           
           if(!listPARToUpdate.isEmpty())
               update listPARToUpdate;
       }
   }
    
    global void finish(Database.BatchableContext BC)
    {}
 }
salesforceMannsalesforceMann

Hi Michele, 

It would help to see your test class and debug log please.

Btw, what is the usecase?

Regards
Mann
Michele ToscanoMichele Toscano
I’ve attached the debug log. The use case is when a PAR record (which can have one or multiple pricing input lines each with its own expiration date) – system will check if the expiration date is past. If all the pricing inputs are past due- it updates the “header” status to Expired. If a PAR record is already in an Expired or Manually Expired status – it should not override the status on the header. If there are no product pricing inputs on the PAR- and the expiration date on the header is past due, as long as the status is not currently expired or in a manually expired status – then proceed to update the status to Expired. That’s essentially what it’s supposed to do. I’ve written a different batch class for the PAR records without pricing inputs- but it would be great if those could be included in one batch job. (But that’s another issue.) If I could just get through at least my For loop problem with this one- I can work to incorporate the other part. It seems to be exceeding the governor limits. Regards, Michele Toscano Senior Applications Specialist Cabot Corporation Office: (678) 297-1455
badibadi
Michele,

I just realised we cannot use sub queries in batch classes because we run into too many rows for assignment when there are large number of child records associated with parent.
Here is the modified code, again i have not tested this  
global class Batch_ExpDate_PricIn implements Database.Batchable<sObject>,Database.Stateful
{
   global Database.QueryLocator start(Database.BatchableContext BC)
   {
        string manualExpStr = 'Manually Expired'; //Correct Status -11/2/16 MT
        string expiredStr = 'Expired'; 
        
        string query= 'select Id,RecordTypeId,RecordType.Name,Par_Status__c,Effective_date__c,Expiration_Date__c from Price_Authorization_Request__c where Par_Status__c !=:manualExpStr  and Par_Status__c !=:expiredStr';
             return Database.getQueryLocator(query);
   }

   global void execute(Database.BatchableContext BC, List<Price_Authorization_Request__c> Parlist) {
            
       if(Parlist != null && !Parlist.isEmpty())
       {
              Map<String,string> maprecTypewithId = new Map<String,String>();
           List<Price_Authorization_Request__c> listPARToUpdate = new List<Price_Authorization_Request__c>();
           for(RecordType rec : [select id,Name from recordType where SObjectType = 'Price_Authorization_Request__c']) //-->system does not like this
           {
               maprecTypewithId.put(rec.Name,rec.id);
           }
		   
		   Map<Id, Integer> childRecCount= new Map<id, Integer>();
		   Map<Id, Integer> updatedCount= new Map<Id, Integer>();
		   Set<Id> prI= new Set<Id>();
		   for(Price_Authorization_Request__c parObj : Parlist){
			prI.add(parObj.Id);
			childRecCount.put(parObj.Id, 0);
			updatedCount.put(parObj.Id, 0);
		   }
		   List<Pricing_Input__c> pInput= [select Id,Expiration_Date_Change_To__c,Effective_date__c, Price_Authorization_Request__c from Pricing_Input__c WHERE Price_Authorization_Request__c IN :prI];  
		   
		   //get original count 
		   for(Pricing_Input__c pi :pInput){
				childRecCount.put(pi.Price_Authorization_Request__c, childRecCount.get(pi.Price_Authorization_Request__c)+1);
				updatedCount.put(pi.Price_Authorization_Request__c, updatedCount.get(pi.Price_Authorization_Request__c)+1);
		   }
		   
		    for(Price_Authorization_Request__c parObj : Parlist){
				if(childRecCount.get(parObj.Id) == updatedCount.get(parObj.Id)){
					parObj.Par_Status__c = 'Expired'; 
					if(parObj.RecordType.Name == 'Standard PAR' && maprecTypewithId.get('ReadOnlyStandard PAR') != null)
                      parObj.RecordTypeId = maprecTypewithId.get('ReadOnlyStandard PAR');
                    else if(parObj.RecordType.Name == 'Formula PAR' && maprecTypewithId.get('ReadOnlyFormula PAR') != null)
                      parObj.RecordTypeId = maprecTypewithId.get('ReadOnlyFormula PAR');
                    listPARToUpdate.add(parObj);
				}
		   }
           
           if(!listPARToUpdate.isEmpty())
               update listPARToUpdate;
       }
   }
    
    global void finish(Database.BatchableContext BC)
    {}
 }

 
Michele ToscanoMichele Toscano
When running the batch job- the results are as follows: First error: Update failed. First exception on row 91 with id a0cF000000KzJovIAF; first error: FIELD_FILTER_VALIDATION_EXCEPTION, Value does not exist or does not match filter criteria.: [Price_Data_Admin__c] (That was being caused because the person was Inactive) But the bigger issue is- when I looked at the record it was trying to expire – the pricing input expiration date is 12/31/2016 – so it shouldn’t be marking this one expired yet. Somehow the date consideration is wrong and it is marking records which have not yet expired – expired. Uh oh! Regards, Michele Toscano Senior Applications Specialist Cabot Corporation Office: (678) 297-1455
badibadi
FIELD_FILTER_VALIDATION_EXCEPTION-  must be related to lookup fields, since the code is trying to modify the record type I would check if there are any filters on recordtype or any other lookup fields 

Oh, Sorry I forgot to include your date condition (highligted in bold) so everything was getting expired. Following should fix that.  
global class Batch_ExpDate_PricIn implements Database.Batchable<sObject>,Database.Stateful
{
   global Database.QueryLocator start(Database.BatchableContext BC)
   {
        string manualExpStr = 'Manually Expired'; //Correct Status -11/2/16 MT
        string expiredStr = 'Expired'; 
        
        string query= 'select Id,RecordTypeId,RecordType.Name,Par_Status__c,Effective_date__c,Expiration_Date__c from Price_Authorization_Request__c where Par_Status__c !=:manualExpStr  and Par_Status__c !=:expiredStr';
             return Database.getQueryLocator(query);
   }

   global void execute(Database.BatchableContext BC, List<Price_Authorization_Request__c> Parlist) {
            
       if(Parlist != null && !Parlist.isEmpty())
       {
              Map<String,string> maprecTypewithId = new Map<String,String>();
           List<Price_Authorization_Request__c> listPARToUpdate = new List<Price_Authorization_Request__c>();
           for(RecordType rec : [select id,Name from recordType where SObjectType = 'Price_Authorization_Request__c']) //-->system does not like this
           {
               maprecTypewithId.put(rec.Name,rec.id);
           }
		   
		   Map<Id, Integer> childRecCount= new Map<id, Integer>();
		   Map<Id, Integer> updatedCount= new Map<Id, Integer>();
		   Set<Id> prI= new Set<Id>();
		   for(Price_Authorization_Request__c parObj : Parlist){
			prI.add(parObj.Id);
			childRecCount.put(parObj.Id, 0);
			updatedCount.put(parObj.Id, 0);
		   }
		   List<Pricing_Input__c> pInput= [select Id,Expiration_Date_Change_To__c,Effective_date__c, Price_Authorization_Request__c from Pricing_Input__c WHERE Price_Authorization_Request__c IN :prI];  
		   
		   //get original count 
		   for(Pricing_Input__c pi :pInput){
				childRecCount.put(pi.Price_Authorization_Request__c, childRecCount.get(pi.Price_Authorization_Request__c)+1);
				if(pi.Expiration_Date_Change_To__c  != null && pi.Expiration_Date_Change_To__c < system.today()){
					updatedCount.put(pi.Price_Authorization_Request__c, updatedCount.get(pi.Price_Authorization_Request__c)+1);
				}
		   }
		   
		    for(Price_Authorization_Request__c parObj : Parlist){
				if(childRecCount.get(parObj.Id) == updatedCount.get(parObj.Id)){
					parObj.Par_Status__c = 'Expired'; 
					if(parObj.RecordType.Name == 'Standard PAR' && maprecTypewithId.get('ReadOnlyStandard PAR') != null)
                      parObj.RecordTypeId = maprecTypewithId.get('ReadOnlyStandard PAR');
                    else if(parObj.RecordType.Name == 'Formula PAR' && maprecTypewithId.get('ReadOnlyFormula PAR') != null)
                      parObj.RecordTypeId = maprecTypewithId.get('ReadOnlyFormula PAR');
                    listPARToUpdate.add(parObj);
				}
		   }
           
           if(!listPARToUpdate.isEmpty())
               update listPARToUpdate;
       }
   }
    
    global void finish(Database.BatchableContext BC)
    {}
 }

Hope this helps