+ Start a Discussion
DbjensenDbjensen 

help with query: Too Many SOQL Queries: 101

Hello - I'm running into an error when I run my test class. Below is the code from the class. The test class says line 17 (below) is causing too many soql queries.  The query looks for opporutnity line items where the OpportunityId is in the mapOfOppIds maps. Could you please let me know why this soql error is happening?
  1.  //Map of new opp values
  2.         Map<Id, Decimal> mapOfOppIds = new Map<Id, Decimal>();
  3.         Map<Id, Decimal> mapOfUpdatedOppIds = new Map<Id, Decimal>();
  4.         
  5.         for(Opportunity newOpp : newUpdatedOpp) {
  6.             if(mapOfOldOppArr.get(newOpp.Id) != mapOfUpdatedArr.get(newOpp.Id)
  7.                && newOpp.Renewed_Opportunity__c == true){
  8.                 mapOfOppIds.put(newOpp.Originating_Opp_Id__c, newOpp.ARR__c);
  9.                 mapOfUpdatedOppIds.put(newOpp.Id, newOpp.ARR__c);
  10.                     }        
  11.             }
  12.         
  13.         system.debug('values in mapOfOppIds ' +mapOfOppIds);
  14.         system.debug('values in mapOfUpdatedOppIds ' +mapOfUpdatedOppIds);
  15.         
  16.         //Query Original Opp Line Items
  17.          List<OpportunityLineItem> oli = [Select Id, OpportunityId, UnitPrice, Originating_OLI_Id__c FROM OpportunityLineItem
  18.                                               WHERE OpportunityId IN :mapOfOppIds.keySet()];
  19.         
  20.         System.debug('Opp Line Items from original opp ' +oli);
debradebra
Are you doing other SOQL queries somewhere else in the test class - can't really tell from your snippet of code as it looks like only one query is executed here but there must be other code or loops that are also doing queries.  Debug log with Apex Profiling can sometimes help with this type of issue.
Raj VakatiRaj Vakati
Share ther trigger and test class to see the issue ?
DbjensenDbjensen
Hi Debra - In the test class, I create an opp with products. After it's created, I change the stage to closed won which triggers a flow that creates a clone of the opp. Below are the additional queries I'm running to verify the new opp was creaed. On line 13, I'm updating the cloned opp. The test is passing down to line 13. Once line 13 hits, I get the "too many soql queries on line 17" mentioned above.  
  1. List<Opportunity> renwalOppList = [Select Id, Manager_override_contract_length__c, Originating_Opp_Id__c, Amount FROM Opportunity WHERE Originating_Opp_Id__c = :opp4.Id];
  2.         
  3.         system.debug('renewal opp found ' + renwalOppList);
  4.        
  5.        List<OpportunityLineItem> renwalOLI = [Select Id, Quantity, Originating_OLI_Id__c FROM OpportunityLineItem WHERE Originating_OLI_Id__c = :oli.Id]; 
  6.       
  7.         system.debug('opp line item 1 found ' + renwalOLI);
  8.         
  9.         List<OpportunityLineItem> renwalOLI2 = [Select Id, Quantity, Originating_OLI_Id__c FROM OpportunityLineItem WHERE Originating_OLI_Id__c = :oli2.Id]; 
  10.       
  11.         system.debug('opp line item 1 found ' + renwalOLI2);
  12.                
  13.         for(Opportunity renewalOpp : renwalOppList) {
  14.             renewalOpp.ARR__c = 4;
  15.             update renewalOpp;
  16.         }
DbjensenDbjensen
Hi Raj - Here is the class and test class. (the test class has no system.asserts yet)

class: 
public class UpdateRenewalOLI {

    public static void updateOPI(List<Opportunity> newUpdatedOpp, List<Opportunity> oldUpdatedOpp) {
        
        List<OpportunityLineItem> opiList = new List<OpportunityLineItem>();
        
        //map of old map
        Map<Id, Decimal> mapOfOldOppArr = new Map<Id, Decimal>();
        
        for(Opportunity oldOpp : oldUpdatedOpp){
            mapOfOldOppArr.put(oldOpp.Id, oldOpp.ARR__c);
        }
        
        system.debug('old opp in Map: ' +mapOfOldOppArr);
        
        //map of updated opp
        Map<Id, Decimal> mapOfUpdatedArr = new Map<Id, Decimal>();
        
        for(Opportunity newOpp : newUpdatedOpp){
            mapOfUpdatedArr.put(newOpp.Id, newOpp.ARR__c);
        }
        
        system.debug('new opp in Map: ' +mapOfUpdatedArr);
        
        //Map of new opp values
        Map<Id, Decimal> mapOfOppIds = new Map<Id, Decimal>();
        Map<Id, Decimal> mapOfUpdatedOppIds = new Map<Id, Decimal>();
        
        for(Opportunity newOpp : newUpdatedOpp) {
            if(mapOfOldOppArr.get(newOpp.Id) != mapOfUpdatedArr.get(newOpp.Id)
               && newOpp.Renewed_Opportunity__c == true){
                mapOfOppIds.put(newOpp.Originating_Opp_Id__c, newOpp.ARR__c);
                mapOfUpdatedOppIds.put(newOpp.Id, newOpp.ARR__c);
                    }        
            }
        
        system.debug('values in mapOfOppIds ' +mapOfOppIds);
        system.debug('values in mapOfUpdatedOppIds ' +mapOfUpdatedOppIds);
        
        //Query Original Opp Line Items
         List<OpportunityLineItem> oli = [Select Id, OpportunityId, UnitPrice, Originating_OLI_Id__c FROM OpportunityLineItem
                                              WHERE OpportunityId IN :mapOfOppIds.keySet()];
        
        System.debug('Opp Line Items from original opp ' +oli);
        
        Map<Id, Decimal> mapOfOriginalOLIUnitPrice = new Map<Id, Decimal>(); 
        
        for(OpportunityLineItem originalOLI : oli) {
            mapOfOriginalOLIUnitPrice.put(originalOLI.Id, originalOLI.UnitPrice);
        }
        
        system.debug('original oli in map ' +mapOfOriginalOLIUnitPrice);
        
        //Query Renewal Opp Line Items
         List<OpportunityLineItem> oliRenewal = [Select Id, OpportunityId, UnitPrice, Originating_OLI_Id__c FROM OpportunityLineItem
                                              WHERE OpportunityId IN :mapOfUpdatedOppIds.keySet()];
        
        System.debug('Opp Line Items from renewal opp ' +oliRenewal);
        
       //Map of renewal Opp Line Items
        Map<Id, Decimal> mapOfRenewalUnitPrice = new Map<Id, Decimal>();
        Map<Id, Id> mapOfRenewalId = new Map<Id, Id>();
        
        for(OpportunityLineItem renewalOLI : oliRenewal){
            if(!oliRenewal.isEmpty()) {
            mapOfRenewalUnitPrice.put(renewalOLI.Id,renewalOLI.UnitPrice);  
            mapOfRenewalId.put(renewalOLI.Id,renewalOLI.OpportunityId); 
            }
        }
        system.debug('values in mapOfRenewalUnitPrice ' +mapOfRenewalUnitPrice);
        system.debug('values in mapOfRenewalId ' +mapOfRenewalId);
        
        for(OpportunityLineItem updateOLI : oliRenewal){
            if(!oliRenewal.isEmpty() && updateOLI.Originating_OLI_Id__c != null) {
            updateOLI.UnitPrice = (mapOfOriginalOLIUnitPrice.get(updateOLI.Originating_OLI_Id__c) * 
                                    (mapOfUpdatedOppIds.get(updateOLI.OpportunityId)/100))
                                    + mapOfOriginalOLIUnitPrice.get(updateOLI.Originating_OLI_Id__c)
                                    ;
            
            system.debug('value of unit price ' +updateOLI.UnitPrice);
            
            try{
            opiList.add(updateOLI);
            }catch(Exception e) {
                system.debug('Opp did not update ' +e);
            }
            }
        }
        
        update opiList;
    }
    
}


Test class ************************

@isTest
public class UpdateRenewalOLI_Test {

    static testMethod void testLeadCreate() {
        
        //Create account
        Account act = new Account();
            act.Name = 'Test Account';
            act.Customer_Segment__c = 'Higher Education';
            act.Name = 'Test Product';
            insert act;
        
        System.debug('Account created ' +act);
        
       //Create Product  
       Product2 pd = new Product2();
            pd.Family = 'Distributed Applications';
            pd.Name = 'Prod 1';
            pd.IsActive = true;
            insert pd;    
        
        System.debug('Product created ' +pd);
        
        //Create Product
        Product2 pd2 = new Product2();
            pd2.Family = 'Distributed Applications';
            pd2.Name = 'Prod 2';
            pd2.IsActive = true;
            insert pd2;    
        
        System.debug('Product 2 created ' +pd2);
        
        // Get standard pricebook
        Id pricebookId = Test.getStandardPricebookId();
        
        System.debug('Pricebook retreived ' +pricebookId);
        
        //Create your pricebook entry
        PricebookEntry pbEntry = new PricebookEntry(
             Pricebook2Id = pricebookId,
             Product2Id = pd.Id,
             UnitPrice = 1000.00,
            IsActive = true);
            insert pbEntry;
        
        System.debug('Pricebook entry 1 ' +pbEntry);
        
        //Create your pricebook entry
        PricebookEntry pbEntry2 = new PricebookEntry(
             Pricebook2Id = pricebookId,
             Product2Id = pd2.Id,
             UnitPrice = 2000.00,
            IsActive = true);
            insert pbEntry2;
        
        System.debug('Pricebook entry 2 ' +pbEntry2);
        
        //Original Opp
        Opportunity opp4 = new Opportunity();
            opp4.Account = act;
            opp4.Name = 'Test 2';
            opp4.LeadSource = 'RFP';
            opp4.Type = 'New';
            opp4.CurrencyIsoCode = 'USD';
            opp4.StageName = 'Qualified';
            opp4.Renewed_Opportunity__c = false;
            opp4.Originating_Opp_Id__c = '123rw';
            opp4.ARR__c = 6;
            opp4.CloseDate = date.today();
            opp4.NextStep = 'Test';
            opp4.Description = 'Test';
            opp4.Manager_override_contract_length__c = 1;
            insert opp4;    
        
        System.debug('Opportunity created ' +opp4);
        
        //create your opportunity line item.  
            OpportunityLineItem oli = new OpportunityLineItem();
             oli.OpportunityId = opp4.Id;
             oli.Quantity = 1;
             oli.PricebookEntryId = pbEntry.Id;
             oli.TotalPrice = oli.Quantity * pbEntry.UnitPrice;
            insert oli;
        
        System.debug('Opp line item 1 created ' +oli);
        
            OpportunityLineItem oli2 = new OpportunityLineItem();
             oli2.OpportunityId = opp4.Id;
             oli2.Quantity = 1;
             oli2.PricebookEntryId = pbEntry2.Id;
             oli2.TotalPrice = oli2.Quantity * pbEntry.UnitPrice;
            insert oli2;
        
        System.debug('Opp line item 2 created ' +oli2);
        
        List<Opportunity> oppList = [Select Id, Manager_override_contract_length__c FROM Opportunity WHERE Id = :opp4.Id];
        
        system.debug('original opp found ' + oppList);
        
        for(Opportunity oppClosed : oppList) {
            oppClosed.StageName = 'Closed Won';
            oppClosed.Manager_override_contract_length__c = 1;
            update oppClosed;
        }
       
       List<Opportunity> renwalOppList = [Select Id, Manager_override_contract_length__c, Originating_Opp_Id__c, Amount FROM Opportunity WHERE Originating_Opp_Id__c = :opp4.Id];
        
        system.debug('renewal opp found ' + renwalOppList);
       
       List<OpportunityLineItem> renwalOLI = [Select Id, Quantity, Originating_OLI_Id__c FROM OpportunityLineItem WHERE Originating_OLI_Id__c = :oli.Id]; 
      
        system.debug('opp line item 1 found ' + renwalOLI);
        
        List<OpportunityLineItem> renwalOLI2 = [Select Id, Quantity, Originating_OLI_Id__c FROM OpportunityLineItem WHERE Originating_OLI_Id__c = :oli2.Id]; 
      
        system.debug('opp line item 1 found ' + renwalOLI2);
               
        for(Opportunity renewalOpp : renwalOppList) {
            renewalOpp.ARR__c = 4;
            update renewalOpp;
        }
        
       Opportunity renwalOppUpdated = [Select Id, Manager_override_contract_length__c, Originating_Opp_Id__c, Amount FROM Opportunity WHERE renewed_Opportunity__c = true];
        
        system.debug('renewal opp with updated amount ' + renwalOppUpdated);
        
    }
}
PawanKumarPawanKumar
Please remove outside the loop and try.

update renewalOpp;

User-added image

Regards,
Pawan Kumar