function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Codi Kodama 9Codi Kodama 9 

System.LimitException: Too many SOQL queries: 101 Stack Trace

I'm a system administrator (we don't have an onsite developer currently) and I worked through some of the Validation Errors on Inbound Change Sets that were occurring due to some Apex Test Classes by updating them to insert values into fields we require at our company. Now that those errors are gone, I'm trying to deploy an Inbound Change Set and I'm getting the following error from an Apex Trigger:

System.LimitException: Too many SOQL queries: 101
Stack Trace: Trigger.AccountBefore: line 96, column 1

Here is the snippet of code that begins at line 96, column 1:

List<Postal_Code__c> postalCodesList = [Select Id, Name, Postal_Code_Identifier__c, Territory__c,      Territory__r.Territory_Code__c, Territory__r.Region__r.Region_Code__c, Territory__r.Region__r.Parent_Region__r.Region_Code__c,      Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c FROM Postal_Code__c      WHERE Postal_Code_Identifier__c in :postalCodes AND isActive__c = true];

I'm familiar with SOQL but not the List function/tag. How can we bypass this error?

 
Ravikant kediaRavikant kedia
In apex governer limit you can apply only 100 soql query so please first check above code should not  inside for loop if above code is not in for loop  then your code always will work fine.
Virendra ChouhanVirendra Chouhan
Hi codi,
Its ok about you don't have an onsite developer, we help you in any situation don't worry.

And about this case im sure your soql query is in any loop.check this (or give us code snippt)
example- 
for (** some code **){
      List<Postal_Code__c> postalCodesList = [Select Id, Name, Postal_Code_Identifier__c, Territory__c,      Territory__r.Territory_Code__c, Territory__r.Region__r.Region_Code__c, Territory__r.Region__r.Parent_Region__r.Region_Code__c,      Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c FROM Postal_Code__c      WHERE Postal_Code_Identifier__c in :postalCodes AND isActive__c = true];


}

so there is a gouverner limit for SOQL and that is - we can fire one 100 SOQL queries so make sure that your SOQL is not in any loop.

Thanks
Regards
Virendra
version7.7@hotmail.com (mailto:version7.7@hotmail.com)
 
Codi Kodama 9Codi Kodama 9
Virenda Chouhan,

Here is the Apex Trigger's code in its entirety:

trigger AccountBefore on Account (before insert, before update) {

  Set<String> countryCodes = new Set<String>();
  Set<String> postalCodes = new Set<String>();
  Set<String> stateCodes = new Set<String>();

  Set<Id> aliasIDs = new Set<Id>();
  Set<String> aliasNames = new Set<String>();

  Map<String, String> aliasStdCustomerMap = new Map<String, String>();
  Map<String, String> nameAliasMap = new Map<String, String>();
  //Map<String, String> nameStdCustomerMap = new Map<String, String>();
  
  Map<String, Country__c> codeCountryMap = new Map<String, Country__c>();
  Map<String, State_Province__c> codeStateMap = new Map<String, State_Province__c>();
  Map<String, Postal_Code__c> codePostalMap = new Map<String, Postal_Code__c>();
        
  RecordType rt = [Select Id from RecordType where sObjectType = 'Account' AND DeveloperName = 'Lattice_Account_Record_Type' limit 1];
  String rtID = null;
  
  if (rt != null)
    rtID = rt.Id;
          
  for (Account a : Trigger.new)
  {
    if (a.BillingCountry != null && !countryCodes.contains(a.BillingCountry))
    {
      countryCodes.add(a.BillingCountry);
    }
    /* NoAlias
    if (a.RecordTypeId == rtId && a.Alias__c != null)  //&& a.Standard_Customer__c == null
    {
      aliasIDs.add(a.Alias__c);
    }
    if (a.Account_Name__c != null && a.Alias__c == null)
    {
      aliasNames.add(a.Account_Name__c);
    }
    */
  }
  /* NoAlias
  List<Alias__c> aliasList = [Select Id, Standard_Customer__c, Alias_Name__c from Alias__c where Id in :aliasIDs or Alias_Name__c in :aliasNames];
  if (aliasList != null)
  {
    for (Alias__c alias : aliasList)
    {
      aliasStdCustomerMap.put(alias.Id, alias.Standard_Customer__c);
      nameAliasMap.put(alias.Alias_Name__c.toUpperCase(), alias.Id);
      //nameStdCustomerMap.put(alias.Alias_Name__c, alias.Standard_Customer__c);
    }
  }
  */
  List<Country__c> countries = [Select Id, Territory_Level_Identifier__c, Country_Code__c, Territory__c, 
    Territory__r.Territory_Code__c, Territory__r.Region__r.Region_Code__c, Territory__r.Region__r.Parent_Region__r.Region_Code__c, 
    Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c FROM Country__c 
    WHERE Country_Code__c in :countryCodes AND isActive__c = true];
  
  if (countries != null)
  {
    for (Country__c c : countries)
    {
      codeCountryMap.put(c.Country_Code__c, c);
    }
  }
  
  for (Account a : Trigger.new)
  {
    Country__c currCountry = codeCountryMap.get(a.BillingCountry);
    if (currCountry != null && currCountry.Territory_Level_Identifier__c != null && currCountry.Territory_Level_Identifier__c.toUpperCase() == 'S' && a.BillingCountry != null && a.BillingState != null)
    {
      if (!stateCodes.contains('' + a.BillingCountry + a.BillingState))
        stateCodes.add('' + a.BillingCountry + a.BillingState);
    }
    else if (currCountry != null && currCountry.Territory_Level_Identifier__c != null && currCountry.Territory_Level_Identifier__c.toUpperCase() == 'P' && a.BillingCountry != null && a.BillingPostalCode != null)
    {
      String massagedPostalCode = null;
      if (a.BillingCountry.equalsIgnoreCase('US') && a.BillingPostalCode.length() > 5)  //For US, we can have Zip+4 on some records
        massagedPostalCode = a.BillingPostalCode.subString(0,5);
      else if (a.BillingCountry.equalsIgnoreCase('CA') && a.BillingPostalCode.length() > 0)
      {
        massagedPostalCode = a.BillingPostalCode.subString(0,1);
      }
      else
        massagedPostalCode = a.BillingPostalCode;
        
      if (!postalCodes.contains('' + a.BillingCountry + a.BillingPostalCode))
        postalCodes.add('' + a.BillingCountry + massagedPostalCode);
    }
  }
  
  List<State_Province__c> stateCodesList = [Select Id, State_Code__c, State_Identifier__c, Territory__c, 
    Territory__r.Territory_Code__c, Territory__r.Region__r.Region_Code__c, Territory__r.Region__r.Parent_Region__r.Region_Code__c, 
    Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c FROM State_Province__c 
    WHERE State_Identifier__c in :stateCodes AND isActive__c = true];
  
  List<Postal_Code__c> postalCodesList = [Select Id, Name, Postal_Code_Identifier__c, Territory__c, 
    Territory__r.Territory_Code__c, Territory__r.Region__r.Region_Code__c, Territory__r.Region__r.Parent_Region__r.Region_Code__c, 
    Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c FROM Postal_Code__c 
    WHERE Postal_Code_Identifier__c in :postalCodes AND isActive__c = true];
  
  if (stateCodesList != null)
  {
    for (State_Province__c s : stateCodesList)
    {
      codeStateMap.put(s.State_Identifier__c, s);
    }
  }
  
  if (postalCodesList != null)
  {
    for (Postal_Code__c p : postalCodesList)
    {
      codePostalMap.put(p.Postal_Code_Identifier__c, p);
    }
  }
  
  for (Account a : Trigger.new)
  {
    /* NoAlias
    if (a.RecordTypeId == rtId)  //&& a.Standard_Customer__c == null  
    {
      if (a.Alias__c != null)
      {
        a.Standard_Customer__c = aliasStdCustomerMap.get(a.Alias__c);
      }
      else if (a.Account_Name__c != null)
      {
        a.Alias__c = nameAliasMap.get(a.Account_Name__c.toUpperCase());
        a.Standard_Customer__c = aliasStdCustomerMap.get(a.Alias__c);
      }
    }  
    */
    Country__c currCountry = codeCountryMap.get(a.BillingCountry);
    if (currCountry != null && currCountry.Territory_Level_Identifier__c != null && currCountry.Territory_Level_Identifier__c.toUpperCase() == 'C' && a.BillingCountry != null)
    {
      //Country__c curCountry = codeCountryMap.get(a.BillingCountry);
      //if (currCountry != null)
      //{
        a.Territory__c = currCountry.Territory__c;
        a.Territory_Code__c = currCountry.Territory__r.Territory_Code__c; 
        a.Region_Code__c = currCountry.Territory__r.Region__r.Region_Code__c;
        a.Area_Code__c = currCountry.Territory__r.Region__r.Parent_Region__r.Region_Code__c;
        a.Theater_Code__c = currCountry.Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c;
      //}
    }
    else if (currCountry != null && currCountry.Territory_Level_Identifier__c != null && currCountry.Territory_Level_Identifier__c.toUpperCase() == 'S' && a.BillingCountry != null && a.BillingState != null)
    {
      State_Province__c curState = codeStateMap.get('' + a.BillingCountry + a.BillingState);
      if (curState != null)
      {
        a.Territory__c = curState.Territory__c;
        a.Territory_Code__c = curState.Territory__r.Territory_Code__c; 
        a.Region_Code__c = curState.Territory__r.Region__r.Region_Code__c;
        a.Area_Code__c = curState.Territory__r.Region__r.Parent_Region__r.Region_Code__c;
        a.Theater_Code__c = curState.Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c;
      }
    }
    else if (currCountry != null && currCountry.Territory_Level_Identifier__c != null && currCountry.Territory_Level_Identifier__c.toUpperCase() == 'P' && a.BillingCountry != null && a.BillingPostalCode != null)
    {
      String massagedPostalCode = null;
      if (a.BillingCountry.equalsIgnoreCase('US') && a.BillingPostalCode.length() > 5)  //For US, we can have Zip+4 on some records
        massagedPostalCode = a.BillingPostalCode.subString(0,5);
      else if (a.BillingCountry.equalsIgnoreCase('CA') && a.BillingPostalCode.length() > 0)
      {
        massagedPostalCode = a.BillingPostalCode.subString(0,1);
      }
      else
        massagedPostalCode = a.BillingPostalCode;
      
      Postal_Code__c curPostal = codePostalMap.get('' + a.BillingCountry + massagedPostalCode);
      if (curPostal != null)
      {
        a.Territory__c = curPostal.Territory__c;
        a.Territory_Code__c = curPostal.Territory__r.Territory_Code__c; 
        a.Region_Code__c = curPostal.Territory__r.Region__r.Region_Code__c;
        a.Area_Code__c = curPostal.Territory__r.Region__r.Parent_Region__r.Region_Code__c;
        a.Theater_Code__c = curPostal.Territory__r.Region__r.Parent_Region__r.Parent_Region__r.Region_Code__c;
      }
    }
        
  }

  AccountHelper.setAccountRepTRA(Trigger.new, Trigger.oldMap, Trigger.isInsert, Trigger.isUpdate);
}