+ Start a Discussion
Narmadha Chandrasekar 4Narmadha Chandrasekar 4 

too many soql queries 201 batch class

This is my batch class and im getting System.LimitException: Too many SOQL queries: 201  on line 59

global class ShipToCountBatch implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        recordtype RECORD_TYPE = [select id from recordtype where SobjectType = 'Account' and name = 'Customer Sold To' limit 1];
       
        String query = 'SELECT Id, Name FROM Account where Active__c != \'X\'AND RecordTypeId ='+'\''+ RECORD_TYPE.Id+'\'' ;
        
        return Database.getQueryLocator(query);
    }  

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
        List<Account> lAccountsToUpdate = new List<Account>{};
        List<Account> lShiptoAccountsToUpdate = new List<Account>{};
        for ( Account a : scope)
        {
            List<Account> al = new List<Account>{};  
            List<Id> accountIds= new List<Id>();
            al = [SELECT Id, SAP_Account_Id__c FROM Account where ParentId = :a.Id];   
            if( al.size()> 0 ){
                for ( Integer i = 0 ; i < al.size(); i++ )
                {
                    accountIds.add(al[i].Id);                  
                }
                accountIds.add(a.Id);
            } 
           
            Account oAccountToUpdate = new Account();
            oAccountToUpdate = a;
                       
             Sobject so1 = [SELECT COUNT(Id) shipToCount from Account where Id = :accountIds];
            
         
            Sobject so2 = [SELECT SUM(Adjusted_Net_Invoice_Price__c) currInvoicePrice, 
                                  SUM(Customer_Margin_CM1__c) currCM,
                                  SUM(Quantity_Converted__c) currVolume 
                                  from Sales_History__c 
                                  where Sales_Account__r.Sold_To_Account__r.Id = :accountIds  
                                        and Division__c != 'CATALYST'      
                                        and Current_Year_Invoice__c = 'Y'];


            Sobject so3 = [SELECT SUM(Adjusted_Net_Invoice_Price__c) prevInvoicePrice, 
                                  SUM(Customer_Margin_CM1__c) prevCM,
                                  SUM(Quantity_Converted__c) prevVolume 
                                  from Sales_History__c 
                                  where Sales_Account__r.Sold_To_Account__r.Id = :accountIds  
                                        and Division__c != 'CATALYST' 
                                        and Previous_Year_Invoice__c = 'Y'];


            oAccountToUpdate.Ship_To_Accounts__c = (Integer)so1.get('shipToCount');
            if (!String.isBlank(String.valueOf(so2.get('currInvoicePrice')) )) {
                oAccountToUpdate.YTD_LYB_Sales__c = Decimal.valueOf(String.valueOf(so2.get('currInvoicePrice'))).setScale(2);   
            }
            if (!String.isBlank(String.valueOf(so2.get('currCM')) )) {      
                oAccountToUpdate.YTD_LYB_CM1__c = Decimal.valueOf(String.valueOf(so2.get('currCM'))).setScale(2);  
            }
            if (!String.isBlank(String.valueOf(so2.get('currVolume')) )) {        
                oAccountToUpdate.YTD_LYB_Volume__c = Decimal.valueOf(String.valueOf(so2.get('currVolume'))).setScale(2);
            }
            if (!String.isBlank(String.valueOf(so3.get('prevCM')))) {
                oAccountToUpdate.Prior_Year_LYB_CM1__c = Decimal.valueOf(String.valueOf(so3.get('prevCM'))).setScale(2);
            }  
            if (!String.isBlank(String.valueOf(so3.get('prevVolume')) )) {  
                oAccountToUpdate.Prior_Year_LYB_Volume__c = Decimal.valueOf(String.valueOf(so3.get('prevVolume'))).setScale(2);   
            }
            if (!String.isBlank(String.valueOf(so3.get('prevInvoicePrice')) )) { 
                oAccountToUpdate.Prior_Year_LYB_Sales__c = Decimal.valueOf(String.valueOf(so3.get('prevInvoicePrice'))).setScale(2);
            }
            lAccountsToUpdate.add(oAccountToUpdate);
            
            List<Account> shipToAccountList = [SELECT Id,ParentId,Name,Parent.Name,YTD_LYB_CM1__c,YTD_LYB_Vol_KGS__c
                                                       FROM Account 
                                                       WHERE ParentId = :a.Id];
           
            if(shipToAccountList.size()>0){
            for (integer i=0;i<shipToAccountList.size();i++)
            {                
            Account shiptoAccountToUpdate = new Account();
            shiptoAccountToUpdate = shipToAccountList[i];
                
            Sobject so4 = [SELECT SUM(Adjusted_Net_Invoice_Price__c) currInvoicePrice, 
                                  SUM(Customer_Margin_CM1__c) currCM,
                                  SUM(Quantity_Converted__c) currVolume 
                                  from Sales_History__c 
                                  where Ship_To_Account__c = :shipToAccountList[i].Id  
                                        and Division__c != 'CATALYST'      
                                        and Current_Year_Invoice__c = 'Y'];

            List<Sales_History__c> listsh = [SELECT Id from Sales_History__c where Ship_To_Account__c = :accountIds];
            Sobject so5 = [SELECT SUM(Adjusted_Net_Invoice_Price__c) prevInvoicePrice, 
                                  SUM(Customer_Margin_CM1__c) prevCM,
                                  SUM(Quantity_Converted__c) prevVolume 
                                  from Sales_History__c 
                                  where Ship_To_Account__c = :shipToAccountList[i].Id  
                                        and Division__c != 'CATALYST' 
                                        and Previous_Year_Invoice__c = 'Y'];
           
            if (!String.isBlank(String.valueOf(so4.get('currInvoicePrice')) )) {
                shiptoAccountToUpdate.YTD_LYB_Sales__c = Decimal.valueOf(String.valueOf(so4.get('currInvoicePrice'))).setScale(2);   
            }
            if (!String.isBlank(String.valueOf(so4.get('currCM')) )) {      
                shiptoAccountToUpdate.YTD_LYB_CM1__c = Decimal.valueOf(String.valueOf(so4.get('currCM'))).setScale(2);  
            }
            if (!String.isBlank(String.valueOf(so4.get('currVolume')) )) {        
                shiptoAccountToUpdate.YTD_LYB_Volume__c = Decimal.valueOf(String.valueOf(so4.get('currVolume'))).setScale(2);
            }
            if (!String.isBlank(String.valueOf(so5.get('prevCM')))) {
                shiptoAccountToUpdate.Prior_Year_LYB_CM1__c = Decimal.valueOf(String.valueOf(so5.get('prevCM'))).setScale(2);
            }  
            if (!String.isBlank(String.valueOf(so5.get('prevVolume')) )) {  
                shiptoAccountToUpdate.Prior_Year_LYB_Volume__c = Decimal.valueOf(String.valueOf(so5.get('prevVolume'))).setScale(2);   
            }
            if (!String.isBlank(String.valueOf(so5.get('prevInvoicePrice')) )) { 
                shiptoAccountToUpdate.Prior_Year_LYB_Sales__c = Decimal.valueOf(String.valueOf(so5.get('prevInvoicePrice'))).setScale(2);
            }
            lShiptoAccountsToUpdate.add(shiptoAccountToUpdate);
            }
            }

        }
            update lShiptoAccountsToUpdate;
            update lAccountsToUpdate;
    }  

    global void finish(Database.BatchableContext BC)
    {

    }

}
Linga_RaminLinga_Ramin
You are facing this Error because you are using SOQL Query in For loop.
 
Deepali KulshresthaDeepali Kulshrestha
Hi Narmadha,
 
You are getting this error because you are using SOQL query inside the 'For' loop.

I would suggest you make a map of the account and get the required value from it.
please refer to my code below :
 
List<Account> contacList = [SELECT Id, SAP_Account_Id__c FROM Account];
               Map<Id,List<Account>>  AccountsMap = new Map<Id,List<Account>>();
                for(Account c : contList){
                if(AccountsMap .containskey(c.Id)){
                   List<Account> cList = AccountsMap.get(c.Id);
                   cList.add(c);
                   AccountsMap.put(c.Id,c);
                }
                else{
                    AccountsMap.put(c.AccountId,new List<Account>());
                    List<Account> cList= AccountsMap.get(c.Id);
                    cList.add(c);
                    AccountsMap.put(c.Id,c);
                    }
                }
          for ( Account a : scope){
                List<Account> al = new List<Account>{}; 
        if(AccountsMap.containsKey(a.parentId)
        al = AccountsMap.get(a.ParentId);
        // more code here which you have to add

Also, I would like to suggest you remove all the queries which are written inside for loop and use map for the same.
 

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
www.kdeepali.com