+ Start a Discussion
GMASJGMASJ 

Too Many SOQL in list query

Hi,

 In below trigger am getting too many soql query in line 65 Please suggest me how to modify this issue to resolve. 

  /* Get subscription list of all quote id */
    list<QuoteLineItem> subqtlne = [select id  from  QuoteLineItem
                               where quoteid in :listIds and
                                     Subscription_Terms__c <> 0];


 
trigger GenQuoteApproval on QuoteLineItem (After Insert, After Update,After Delete) {

  Decimal LineMaxDiscount;
  Decimal LineMaxACV;
  Decimal SubLineMaxDiscount;
  Decimal SubLineMaxACV;
  Decimal SerLineMaxDiscount;
  Decimal SerLineMaxACV;
  Decimal TraLineMaxDiscount;
  Decimal TraLineMaxACV;
  Map<ID, Quote> ParentQuote = new Map<ID, Quote>();
  String SALREPID;
  String MRGID;
  String SALID;
  String CFOID;
  String SERID;
  String TRAID;
  String Level;
  Integer GSublevel1Count;
  Integer GSublevel2Count;
  Integer GSublevel3Count;
  Integer GSublevel4Count;
  Integer GSerlevel1Count;
  Integer GSerlevel2Count;
  Integer GSerlevel3Count;
  Decimal SubscriptionTerm;
  Integer GTralevel1Count;
  Integer GTralevel2Count;
  Integer GTralevel3Count;
 

 
Try 
{
if ( Trigger.isAfter)
  {
 List<Id> listIds = new List<Id>();
 //Set<Id> listIds = new Set<Id>();

 List<Quote> QuotetList = new List<Quote>();

 /* Get Quote ID */
 for (QuoteLineItem childquoteline :  Trigger.new)
 {
        listIds.add(childquoteline.QuoteId);
    }
 
   
 ParentQuote = new Map<Id, Quote>([SELECT id,Level_1__c,Level_2__c,Level_3__c,Level_4__c,Level_5_Service__c,Level_6_Training__c FROM Quote WHERE ID IN :listIds]);
 
  /* Get service list of all quote id */
   list<QuoteLineItem> serqtlne = [select id  from  QuoteLineItem
                                     where quoteid in :listIds and
                                           product2.Productcode  like 'CBSVC%'];
   
  list<id> serviceid = new list<id>();
  for(QuoteLineItem getserviceid :  serqtlne)
   {
      serviceid.add(getserviceid.id);

   }


  /* Get subscription list of all quote id */
    list<QuoteLineItem> subqtlne = [select id  from  QuoteLineItem
                               where quoteid in :listIds and
                                     Subscription_Terms__c <> 0];
                                     
  list<id> subscriptionid = new list<id>();
  for(QuoteLineItem getsubscriptionid :  subqtlne )
   {
      subscriptionid.add(getsubscriptionid.id);

   }

 /* Subscription Discount and ACV */
    List<AggregateResult> MaxSubscription = [select max(Discount_Percent__c) SubQuoteLineMaxDiscount,sum(f_ACV__c) SubQuoteLineMaxACV
                                                   from  QuoteLineItem
                                                   where quoteid in :listIds and Subscription_Terms__c <> 0 and
                                                         ID not in :serviceid
                                                 ];

  for (AggregateResult SubQuoteMaxDiscount : MaxSubscription)
  {
  SubLineMaxDiscount = (Decimal)SubQuoteMaxDiscount.get('SubQuoteLineMaxDiscount');
  SubLineMaxACV = (Decimal)SubQuoteMaxDiscount.get('SubQuoteLineMaxACV');
  }

   system.debug('Subscription Line Discount  :' + SubLineMaxDiscount);
   system.debug('Subscription Line ACV  :' + SubLineMaxACV);

   /* Service Discount and ACV */
  List<AggregateResult> MaxService = [select max(Discount_Percent__c) SerQuoteLineMaxDiscount,SUM(f_ACV__c) SerQuoteLineMaxACV
                                      from  QuoteLineItem
                                      where quoteid in :listIds and product2.Productcode  like 'CBSVC%' and
                                            id not in :subscriptionid];

  for (AggregateResult SerQuoteMaxDiscount : MaxService)
  {
  SerLineMaxDiscount = (Decimal)SerQuoteMaxDiscount.get('SerQuoteLineMaxDiscount');
  SerLineMaxACV = (Decimal)SerQuoteMaxDiscount.get('SerQuoteLineMaxACV');
  }

   system.debug('Service Line Discount  :' + SerLineMaxDiscount);
   system.debug('Service Line ACV  :' + SerLineMaxACV);
   
   
   /* Training Discount and ACV */
  List<AggregateResult> MaxTraining = [select max(Discount_Percent__c) TraQuoteLineMaxDiscount,SUM(f_ACV__c) TraQuoteLineMaxACV
                                      from  QuoteLineItem
                                      where quoteid in :listIds and product2.Productcode  like 'T%' and
                                            id not in :subscriptionid and id not in :subscriptionid];

  for (AggregateResult TraQuoteMaxDiscount : MaxTraining)
  {
  TraLineMaxDiscount = (Decimal)TraQuoteMaxDiscount.get('TraQuoteLineMaxDiscount');
  TraLineMaxACV = (Decimal)TraQuoteMaxDiscount.get('TraQuoteLineMaxACV');
  }

   system.debug('Training Line Discount  :' + TraLineMaxDiscount);
   system.debug('Training Line ACV  :' + TraLineMaxACV);
   

  Opportunity Opp = [select ownerid
                    from opportunity
                    where
                    id in (select OpportunityId from quote where id in :listIds) ];

  system.debug(listIds);
  
  SubscriptionTerm = ApprovalUtils.GetSubscriptionTerm(listIds);
  

  system.debug('Get New Subscription Term' + SubscriptionTerm);
  
  /* Get Manager ID */
  User Usr = [select managerid from user where id = :opp.ownerid];

 /* Subscription Query to get level 1..4 values */
   if ( SubscriptionTerm != null &&
        SubLineMaxACV != null &&
        SubLineMaxDiscount != null)
   {
       GSublevel1Count = ApprovalUtils.SubLevel1(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level1 :' + GSublevel1Count);

       GSublevel2Count = ApprovalUtils.SubLevel2(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level2 :' + GSublevel2Count);

       GSublevel3Count = ApprovalUtils.SubLevel3(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level3 :' + GSublevel3Count);

       GSublevel4Count = ApprovalUtils.SubLevel4(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level4 :' + GSublevel4Count);

        
   }    
   
      /* Service Query to get level 1..4 values */
   if (SerLineMaxACV != null && SerLineMaxDiscount != null)
    {
       GSerlevel1Count = ApprovalUtils.SerLevel1(SerLineMaxACV,SerLineMaxDiscount);

       system.debug('Service Level1 :' + GSerlevel1Count);

        GSerlevel2Count = ApprovalUtils.SerLevel2(SerLineMaxACV,SerLineMaxDiscount);

       system.debug('Service Level2 :' + GSerlevel2Count);

        GSerlevel3Count = ApprovalUtils.SerLevel3(SerLineMaxACV,SerLineMaxDiscount);

       system.debug('Service Level3 :' + GSerlevel3Count);
    } 
    
        /* Training Query to get level 1..4 values */
   if (TraLineMaxACV != null && TraLineMaxDiscount != null)
    {
        GTralevel1Count = ApprovalUtils.TraLevel1(TraLineMaxACV,TraLineMaxDiscount);
        system.debug('Training Level1 :' + GTralevel1Count);
        
        GTralevel2Count = ApprovalUtils.TraLevel2(TraLineMaxACV,TraLineMaxDiscount);
        system.debug('Training Level2 :' + GTralevel2Count);
        
        GTralevel3Count = ApprovalUtils.TraLevel3(TraLineMaxACV,TraLineMaxDiscount);
        system.debug('Training Level3 :' + GTralevel3Count);
    }
    
     If( GSublevel1Count >= 1  || GSerlevel1Count >= 1  || GTralevel1Count >= 1)
     {
      SALREPID = Opp.OwnerId;
      }   
     
     
    If (GSublevel2Count >= 1 || GSerlevel2Count >= 1  || GTralevel2Count >= 1)
    {
      SALREPID = NULL;
      MRGID = Usr.managerid;
    }
 
    /* Future here you may have to change for amount > 1000000 if its going only to SVP */
   If ( GSublevel3Count >= 1 || GSerlevel3Count >= 1 ||  GTralevel3Count >= 1)
      {
      SALREPID = NULL;
      MRGID = Usr.managerid;
      SALID = '00580000007jaoA';
      }
   
    /* Assign this to Pete */ 
    If ( GTralevel3Count >= 1)
    {
     TRAID = '00580000006GbpI';
     }
    else
    {
     TRAID = NULL;
     }  
     
     /* Asssing to asif */
    If ( GSerlevel3Count >= 1)
    {
     SERID = '0053400000839zg';
     }
    else
    {
     SERID = NULL;
     } 
      
    If ( GSublevel4Count >= 1 )
    {
    SALREPID = NULL;
    MRGID = Usr.managerid;
    SALID = '00580000007jaoA';
    CFOID = '00534000008oOCr';
    Level = '4sub';
    }  
  
    //system.debug('Which Level :' + Level);
    system.debug('Sales Rep :' + SALREPID);
    system.debug('Manager :' + MRGID);
    system.debug('Sales Ops :' + SALID);
    system.debug('CEO CFO :'  + CFOID);
    system.debug('Service:' + SERID);
    system.debug('Training:' + TRAID);
 
  
for (QuoteLineItem gqtl : Trigger.new)
 {
   Quote MyParentQuote = ParentQuote.get(gqtl.QuoteId);

    MyParentQuote.Level_1__c = SALREPID;
    MyParentQuote.Level_2__c=MRGID;
    MyParentQuote.Level_3__c=SALID;
    MyParentQuote.Level_4__c=CFOID;
    MyParentQuote.Level_5_Service__c = SERID;
    MyParentQuote.Level_6_Training__c = TRAID;
 
  }

  update ParentQuote.values();
  

 }
  
}

catch(Exception e) {
    System.debug('The following exception has occurred: ' + e.getMessage());    
}
  
}


Thanks

Sudhir

Best Answer chosen by GMASJ
GauravGargGauravGarg
Hi Sudhir,

Can we share screen and found the actuall root cause for this issue. Please connect with me on skype: gaurav62990

Thanks,
Gaurav

All Answers

GauravGargGauravGarg
Hi Sudhir,

What is the exact error you are facing.
1. Too many soql query rows : 10000
2. Too many soql 101

Thanks,
Gaurav
ShikibuShikibu
I have two suggestions.
GMASJGMASJ
This is the below error am receiving 
 
System.LimitException: Too many SOQL queries: 101 
Stack Trace: Trigger.GenQuoteApproval: line 65, column 1

 
ShikibuShikibu
You also have a structure like the following. This is a bad idea; it doesn't help with debugging (because the debugger would show you an exception anyway), and it prevents unhandled exceptions from being visible anywhere except in the debugger.

See https://developer.salesforce.com/forums/?id=906F00000009BhUIAU
 
try {
  ...
}
catch(Exception e) {
    System.debug('The following exception has occurred: ' + e.getMessage());    
}


 
ShikibuShikibu
The error you gave means that line 65 has been executed 101 times, and failed on the 101st. I don't see a loop in your code that would make that happen. Is it possible that you are causing the trigger to fire 101 times?
GMASJGMASJ
This is happening when i push the code into production also this is coming when test class below when i move to production 
 
@IsTest(seeAllData=true)
private class ApprovalUtils_Test_New{
    @isTest(SeeAllData=true) // required for using the standard pricebook, unfortunately
    private static void ApprovalUtilsTest() {
    
     test.StartTest();
     
                 ApprovalUtils.SubLevel1(1,0,0);
                 ApprovalUtils.SubLevel2(1,0,10);
                 ApprovalUtils.SubLevel3(1,0,11);
                 ApprovalUtils.SubLevel4(1,0,41);
               
                 ApprovalUtils.SerLevel1(1,1);
                 ApprovalUtils.SerLevel2(1,10);
                 ApprovalUtils.SerLevel3(1,41);
                 
                 ApprovalUtils.TraLevel1(1,1);
                 ApprovalUtils.TraLevel2(1,10);
                 ApprovalUtils.TraLevel3(1,41);
                 
                 
        Account a = new Account(Name = 'Test Account');
        insert a;

        Opportunity o = new Opportunity(Name = 'Test Opp', StageName = 'Test Stage', CloseDate = Date.today(), AccountId = a.Id);
        insert o;

        Pricebook2 pb = [select Id from Pricebook2 where IsStandard = true limit 1];

        Product2 p = new Product2(Name = 'CBSVC', isActive = true);
        insert p;

        PricebookEntry pbe = new PricebookEntry(Pricebook2Id = pb.Id, Product2Id = p.Id, UnitPrice = 1, isActive = true);
        insert pbe;

        Quote q = new Quote(Name = 'Test Quote', OpportunityId = o.Id, PriceBook2Id = pb.Id);
        insert q;

        QuoteLineItem qli = new QuoteLineItem(
            QuoteId = q.Id, 
            PriceBookEntryId = pbe.Id, 
            Quantity = 1, 
            UnitPrice = 500,
            Discount_Percent__c = 0,
            Subscription_Terms__c = 1,
            Number_of_Nodes__c = 10
            );
        insert qli;
        
        /* Training */
        Product2 pt = new Product2(Name = 'T', isActive = true);
        insert pt;

        PricebookEntry pbet = new PricebookEntry(Pricebook2Id = pb.Id, Product2Id = pt.Id, UnitPrice = 1, isActive = true);
        insert pbet;

        Quote qt = new Quote(Name = 'Test Quote', OpportunityId = o.Id, PriceBook2Id = pb.Id);
        insert qt;

        QuoteLineItem qlit = new QuoteLineItem(
            QuoteId = q.Id, 
            PriceBookEntryId = pbet.Id, 
            Quantity = 1, 
            UnitPrice = 500,
            Discount_Percent__c = 10,
            Subscription_Terms__c = 1,
            Number_of_Nodes__c = 10
            );
        insert qlit;
        
       test.StopTest(); 
                     
    }
     
    }

Please suggest me how to modify I also have a helper class which is been used inside the trigger
 
public with sharing class ApprovalUtils {

  /* Subscription Discount Level 1 */
  public static Integer SubLevel1(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L1Count;  
    
   List<AggregateResult> Level1 = [select count(id) Level1Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Sales_Rep_Lower__c <= :Discount and Sales_Rep_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel1 : Level1)
     {
       L1Count = ((Integer)arLevel1.get('Level1Count'));
       
     }         
                                     
    return L1Count;
  }
  
   /* Subscription Discount Level 2 */
   public static Integer SubLevel2(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L2Count;  
   
   List<AggregateResult> Level2 = [select count(id) Level2Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Direct_Manager_Lower__c <= :Discount and Direct_Manager_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel2 : Level2)
     {
      L2Count = ((Integer)arLevel2.get('Level2Count'));  
     }        
                                      
    return L2Count;
  }
  
   /* Subscription Discount Level 3 */
   public static Integer SubLevel3(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L3Count;  
   
   List<AggregateResult> Level3 = [select count(id) Level3Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              SVP_Lower__c <= :Discount and SVP_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel3 : Level3)
     {
      L3Count = ((Integer)arLevel3.get('Level3Count'));
     }        
                                      
    return L3Count;
  }
  
  /* Subscription Discount Level 4 */
  public static Integer SubLevel4(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L4Count;  

   List<AggregateResult> Level4 = [select count(id) Level4Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              CEO_CFO__c <= :Discount and CEO_CFO_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel4 : Level4)
     {
      L4Count = ((Integer)arLevel4.get('Level4Count'));
     }        
                                      
    return L4Count;
  }
  
  /* Service Discount Level 1 */
  public static Integer SerLevel1(Decimal ACV,Decimal Discount) {
    
   Integer L1Count;  
   
   List<AggregateResult> Level1 = [select count(id) Level1Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Services' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Sales_Rep_Lower__c <= :Discount and Sales_Rep_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel1 : Level1)
     {
      L1Count = ((Integer)arLevel1.get('Level1Count'));
     }        
                                      
    return L1Count;
  }
  
  /* Service Discount Level 2 */
    public static Integer SerLevel2(Decimal ACV,Decimal Discount) {
    
   Integer L2Count;  
   
   List<AggregateResult> Level2 = [select count(id) Level2Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Services' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Direct_Manager_Lower__c <= :Discount and Direct_Manager_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel2 : Level2)
     {
      L2Count = ((Integer)arLevel2.get('Level2Count'));
     }        
                                      
    return L2Count;
  }


  /* Service Discount Level 3 */
    public static Integer SerLevel3(Decimal ACV,Decimal Discount) {
    
   Integer L3Count;  
   
   List<AggregateResult> Level3 = [select count(id) Level3Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Services' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              SVP_Lower__c <= :Discount and SVP_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel3 : Level3)
     {
      L3Count = ((Integer)arLevel3.get('Level3Count'));
     }        
                                      
    return L3Count;
  }
 
 
   /* Training Discount Level 1 */
  public static Integer TraLevel1(Decimal ACV,Decimal Discount) {
    
   Integer L1Count;  
   
   List<AggregateResult> Level1 = [select count(id) Level1Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Training' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Sales_Rep_Lower__c <= :Discount and Sales_Rep_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel1 : Level1)
     {
      L1Count = ((Integer)arLevel1.get('Level1Count'));
     }        
                                      
    return L1Count;
  }
 
 
   /* Training Discount Level 2 */
    public static Integer TraLevel2(Decimal ACV,Decimal Discount) {
    
   Integer L2Count;  
   
   List<AggregateResult> Level2 = [select count(id) Level2Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Training' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Direct_Manager_Lower__c <= :Discount and Direct_Manager_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel2 : Level2)
     {
      L2Count = ((Integer)arLevel2.get('Level2Count'));
     }        
                                      
    return L2Count;
  }
  
  
   /* Training Discount Level 3 */
   public static Integer TraLevel3(Decimal ACV,Decimal Discount) {
    
   Integer L3Count;  
   
   List<AggregateResult> Level3 = [select count(id) Level3Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Training' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              SVP_Lower__c <= :Discount and SVP_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel3 : Level3)
     {
      L3Count = ((Integer)arLevel3.get('Level3Count'));
     }        
                                      
    return L3Count;
  }
  
  
    /* Service Discount Level 3 */
    public static Decimal GetSubscriptionTerm(list<Id> Quoteid) {
     
     Decimal SubscriptionTerm;
     
    /* Get Subscription Term */ 
    List<AggregateResult> MaxSubscrioptionYear = [select max(f_Approval_Subscription_Years__c) QSubscriptionYear 
                                                  from QuoteLineItem where quoteid in :Quoteid]; 
  
    
    for (AggregateResult GetSubscriptionYear : MaxSubscrioptionYear)
    { 
     SubscriptionTerm = (Decimal)GetSubscriptionYear.get('QSubscriptionYear');
      }
 
       return SubscriptionTerm;
    }
    
    
    public static List<QuoteLineItem> GetServiceItem(list<Id> listIds){
    
     list<QuoteLineItem> serqtlne = new list<QuoteLineItem>();
     Try
     {
     /* Get service list of all quote id */
      serqtlne = [select id  from  QuoteLineItem
                                     where quoteid in :listIds and
                                           product2.Productcode  like 'CBSVC%'];
      }
      catch(Exception ex)
     {
      System.debug(ex);

      }                                       
     
     return serqtlne;
     
     }
    
     public static List<QuoteLineItem> GetSubscripItem(list<Id> listIds){
      list<QuoteLineItem> subqtlne = new list<QuoteLineItem>();
     try
     {
     /* Get service list of all quote id */
      subqtlne = [select id  from  QuoteLineItem
                                     where quoteid in :listIds and
                                           Subscription_Terms__c <> 0];
                                           
                                             
      }
     catch(Exception ex)
     {
      System.debug(ex);
      system.debug('============='+ex.getMessage());
      system.debug('============='+ex.getLineNumber());
      system.debug('============='+ex.getStackTraceString());
      }                                       
     
     return subqtlne;
     
     }
       
}


Thanks
Sudhir

ShikibuShikibu
You no longer need seealldata to see standardpricebook (since Summer 14; (http://resources.docs.salesforce.com/190/latest/en-us/sfdc/pdf/salesforce_summer14_release_notes.pdf) see page 2.
ShikibuShikibu

Well, I don't see a loop in that test class either. But somehow your line 65 is being invoked > 100 times.
 

 

GauravGargGauravGarg
Hi Sudhir,

Can we share screen and found the actuall root cause for this issue. Please connect with me on skype: gaurav62990

Thanks,
Gaurav
This was selected as the best answer
GMASJGMASJ
Hi Gaurav,

   I have added you in skype please accept

Thanks
Sudhir
ShikibuShikibu

Sudhir:

You have marked Guarav's invitation to you to examine your problem together as a best answer. But best answer is supposed to be useful to other people. I suggest that you ask Guarav to post a reply about what the problem was, and you mark that as Best Answer, not his invitation.