0
global class FRB_BatchToUpdateExceptionFields implements Database.Batchable<SObject>{
    global database.querylocator start(Database.BatchableContext bc)
    {  
       string FRBRecordTypeID = utility.getFRBProfileNameLabel();                 
       string query= 'select id,Account_Standing__c,Organization_Name__c,Organization_Name__r.Number_of_exceptions_under_4_6_bucket__c,Organization_Name__r.Number_of_Exception__c,Organization_Name__r.Number_of_exceptions_under_7_10_bucket__c,Organization_Name__r.Number_of_exceptions_under_11_19_bucket__c from Process_Adherence_Monitoring__c WHERE  Status__c != \'Closed\' AND REcordtypeId =: FRBRecordTypeID ';
       return Database.getQueryLocator(query);
    }
    global void execute(Database.BatchableContext bc, Process_Adherence_Monitoring__c [] pList1)
   { 
              List<Account> accUpdateListunderfrst = new List<Account>();
        List<Account> accList1 = new List<Account>();
        List<Account> accList2 = new List<Account>();
        List<Account> accList3 = new List<Account>();
        List<Account> accList = new List<Account>();
        set<id>accIdSet = new set<id>();
        List <AggregateResult> pamList = new List<AggregateResult>();
         List <AggregateResult> pamList1 = new List<AggregateResult>();
          List <AggregateResult> pamList2 = new List<AggregateResult>();
           List <AggregateResult> pamList3 = new List<AggregateResult>();

        string FRBRecordTypeID = utility.getFRBProfileNameLabel(); 
        for(Process_Adherence_Monitoring__c  p:pList1){
            accIdSet.add(p.Organization_Name__c);
        } 
        accList = [select id,Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id In :accIdset];
        accList1 = [select id,Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id In :accIdset];
        accList2 = [select id,Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id In :accIdset];
        accList3 = [select id,Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id In :accIdset];

        pamList = [Select Organization_Name__c, Count(Id) From Process_Adherence_Monitoring__c Where Organization_Name__c IN: accIdSet AND Account_Standing__c = '0-3 Days (Green - New Exception)' AND Status__c != 'Closed'Group By Organization_Name__c];
        pamList1 = [Select Organization_Name__c, Count(Id) From Process_Adherence_Monitoring__c Where Organization_Name__c IN: accIdSet AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' AND Status__c != 'Closed'Group By Organization_Name__c];
        pamList2 = [Select Organization_Name__c, Count(Id) From Process_Adherence_Monitoring__c Where Organization_Name__c IN: accIdSet AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' AND Status__c != 'Closed'Group By Organization_Name__c];
        pamList3 = [Select Organization_Name__c, Count(Id) From Process_Adherence_Monitoring__c Where Organization_Name__c IN: accIdSet AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' AND Status__c != 'Closed'Group By Organization_Name__c];

        for(AggregateResult ar: pamList){
            for(AggregateResult ar1: pamList1){
               for(AggregateResult ar2: pamList2){
                 for(AggregateResult ar3: pamList3){

                   for(Account  p:accList){

                    if(ar.get('Organization_Name__c') == p.Id && ar1.get('Organization_Name__c') == p.Id && ar2.get('Organization_Name__c') == p.Id && ar3.get('Organization_Name__c') == p.Id){

                        p.Number_of_Exception__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
                        p.Number_of_exceptions_under_4_6_bucket__c= Decimal.ValueOf(String.ValueOf(ar1.get('expr0')));
                        p.Number_of_exceptions_under_7_10_bucket__c= Decimal.ValueOf(String.ValueOf(ar2.get('expr0')));
                        p.Number_of_exceptions_under_11_19_bucket__c = Decimal.ValueOf(String.ValueOf(ar3.get('expr0')));

                    }

                   }
           }
          }
         }
       }

    update(accList);

   }
      global void finish(Database.BatchableContext bc)
    {
      //FRB_UpdateExceptionsforBatch.FRB_Exceptions();
    }
    }
  • You need to change for loop inside the for loop as no SOQL statement should be executed inside the for loop – RCS Feb 14 '17 at 9:15
  • You have three useless queries in your batch: accList1, accList2, accList3. Also, you can query all the Process_Adherence_Monitoring__c objects with only one query and separate it in four lists depending on the Account_Standing__c later. – Martin Lezer Feb 14 '17 at 9:16
0
  1. Use advance for loop which helps retrive records using querymore because as there is governor limit for querying data from list at once is 50k which is why you are getting exception of 50001 query rows.

    //Use Advance For Loop which retrives records using query More.
    for(Process_Adherence_Monitoring__c objPAM : [Select Id,Organization_Name__c,Account_Standing__c
                                            From Process_Adherence_Monitoring__c Where Organization_Name__c IN: accIdSet AND 
                                                (Account_Standing__c = '0-3 Days (Green - New Exception)' OR 
                                                 Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)') OR
                                                 Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' OR
                                                 Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) AND 
                                                 Status__c != 'Closed'                 
                                         ]){
    
  2. Remove unnecessary accList and pamList you can query directly in advance for loop

  3. Remove unnecessary for within for you can handle that in single query by putting different conditions
  4. I didn't understand why you are using string FRBRecordTypeID = utility.getFRBProfileNameLabel(); two times if not necessary use it once whereever required. Also you can refer to this question Please help me understand the casue of error this will also help you to write optimized code.
  5. Also refer this quetion for rollup summary trigger Updating Parent Field After Child Record is Deleted

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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