You need to sign in to do that
Don't have an account?
Narmadha 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)
{
}
}
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)
{
}
}
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 :
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