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
MaheemSamMaheemSam 

Query inside method Number of SOQL queries: 100 out of 100

Hi, 

 In below method when i call from visualforce page i am getting Number of SOQL queries: 100 out of 100

 Please suggest me how to modify the code i think query inside the code is the issue please suggest me how to modifiy  
/* calculate disti discount */  
  public static Decimal reCalRslrDisc(Decimal listprice,String productcat, Decimal reslrdistdiscount){
        Boolean isService = false;
        Decimal s;
        
        system.debug('listprice = ' + listprice);
        system.debug('productcat = ' + productcat);
        system.debug('reslrdistdiscount = ' + reslrdistdiscount);
        
        if ( reslrdistdiscount == null || reslrdistdiscount < 1 ){
            reslrdistdiscount = 0;
         } 
        
        if ( productcat=='C' || productcat=='E'|| productcat=='D'|| productcat=='H'|| productcat=='I'|| productcat=='J' ){
            isService = true;
          }
        
        if ( isService == true && Limits.getQueries() <   Limits.getLimitQueries()) { 
            try
            {
            NSP_Margin_Schedule__c NMS =  [ Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                           where  Reseller_Discount__c = :reslrdistdiscount and 
                                           Service__c = :isService 
                                           and createddate < 2015-01-17T00:00:00-08:00 ];
                                           
            System.debug('Total Number of SOQL Queries allowed in this Apex code context: ' +  Limits.getLimitQueries());                               
            System.debug('1. Number of Queries used in this Apex code so far: ' + Limits.getQueries());
                    
                                        
                s = NMS.Distributor_Discount__c;
                 
             }
            
            catch (System.NullPointerException e) {
             system.debug('Null Exception');
             }
                return s;
         
                                       
        }
        else {
            return 0;
        }      
        
  }

Thanks
Sudhir
Prateek Singh SengarPrateek Singh Sengar
Hi Sudhir,
The error occurs when your code are executing 100 queries within 1 transaction. From your code that you have shared it doesnt look like its the culprit (unless reCalRslrDisc is called within a for loop). You can turn on the debug log and trace which code is executing the most queries.
Once you have identified the culprit code consider bulkifing it to reduce the number of queries within the transaction.
MaheemSamMaheemSam
Hi Prateek, 

  Your right i am calling the reCalRslrDisc  method inside a for loop as mentioned below in another method will this be causing the issue please advice me how to change the code
  
/* On Reseller Discount Change changes disti discount */
  public PageReference calcOnReDistDschange (){
      Decimal glistprice; 

      for(OptyLineWrapper linew : lineWrapr){        
          OpportunityLineItem backuplne = backUpLines.get(lineW.line.id);
     
          if ( lineW.line.Reseller_discount_rate__c <> null ){
              if ( lineW.line.PricebookEntry.name != 'COTERM' ){
                    glistprice = lineW.line.listprice;
                 }
               else {
                   glistprice = lineW.line.CoTerm_List_Price__c;                 
                }
               lineW.line.Discount_rate__c = String.valueOf(reCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,Decimal.Valueof(lineW.line.Reseller_discount_rate__c)));
               
           }
        }
        editlinestatuschng();  
        return null;
  }

Thanks
Sudhir
Prateek Singh SengarPrateek Singh Sengar

Hi Sudhir,
Yes this can cause issues such as 101 soql error.  There are multiple ways you can bulkify your code. Please refer to the below articles on how to bulkify your code

https://developer.salesforce.com/page/Best_Practice%3A_Bulkify_Your_Code
https://developer.salesforce.com/page/Apex_Code_Best_Practices


 
Jamal RidaJamal Rida
Hi Sudhir,

I suggest to use Maps to fetch all records from your sObject like this before entering inside the Loop :  
NSP_Margin_Schedule__c NMS = [ Select Distributor_Discount__c From NSP_Margin_Schedule__c where Reseller_Discount__c in :accountdsIdsSet and createddate < 2015-01-17T00:00:00-08:00 ];

Once you have this map, you can then change thiis method reCalRslrDisc parameters to accept this NSP_Margin_Schedule__c, and then you can perform your logic...

Hope that helps !
Mark is as resolved if it does.



 
MaheemSamMaheemSam
Hi Jamal,

   Please see below code does this work? Please suggest me a change with map code how to fix this issue. 

   
Map<ID, NSP_Margin_Schedule__c> m = new Map<ID, NSP_Margin_Schedule__c>([Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                                                                     where  Reseller_Discount__c = :reslrdistdiscount and 
                                                                                     Service__c = :isService and createddate < 2015-01-17T00:00:00-08:00 limit 1]);                               
            
            for (ID idKey : m.keyset()) {
                 NSP_Margin_Schedule__c a = m.get(idKey);
                 System.debug(a.Distributor_Discount__c);
                 s = a.Distributor_Discount__c;
               }
            
           
            return s;

Thanks
Sudhir
Jamal RidaJamal Rida
Hi,

This code should be fine for you  : 
  
/* On Reseller Discount Change changes disti discount */
  public PageReference calcOnReDistDschange (){
    Decimal glistprice; 
	set<Decimal> resellerDiscountSet = new set<Decimal>();
	for(OptyLineWrapper linew : lineWrapr){
		resellerDiscountSet.add(lineW.line.Reseller_discount_rate__c);
	}
	Map<Decimal, NSP_Margin_Schedule__c> marginScheduleMap = new Map<Decimal, NSP_Margin_Schedule__c>();                               
    for(NSP_Margin_Schedule__c margSchedule : [Select Distributor_Discount__c From NSP_Margin_Schedule__c 
													where  Reseller_Discount__c in :resellerDiscountSet and 
													createddate < 2015-01-17T00:00:00-08:00 ]){
		if(!marginScheduleMap.containsKey(margSchedule.Reseller_Discount__c)){
			marginScheduleMap.put(margSchedule.Reseller_Discount__c, margSchedule);
		}
	}    
      for(OptyLineWrapper linew : lineWrapr){        
          OpportunityLineItem backuplne = backUpLines.get(lineW.line.id);
     
          if ( lineW.line.Reseller_discount_rate__c <> null ){
              if ( lineW.line.PricebookEntry.name != 'COTERM' ){
                    glistprice = lineW.line.listprice;
                 }
               else {
                   glistprice = lineW.line.CoTerm_List_Price__c;                 
                }
				//Decimal discountRate =	marginScheduleMap.get(Decimal.Valueof(lineW.line.Reseller_discount_rate__c)) != null ? 
				//						marginScheduleMap.get(Decimal.Valueof(lineW.line.Reseller_discount_rate__c)) : 0;
               lineW.line.Discount_rate__c = String.valueOf(reCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,Decimal.Valueof(lineW.line.Reseller_discount_rate__c), marginScheduleMap));
               
           }
        }
        editlinestatuschng();  
        return null;
  }
  
  /* calculate disti discount */  
  public static Decimal reCalRslrDisc(Decimal listprice,String productcat, Decimal reslrdistdiscount, Map<Decimal,NSP_Margin_Schedule__c> marginScheduleMap){
        Boolean isService = false;
        Decimal s;
        
        system.debug('listprice = ' + listprice);
        system.debug('productcat = ' + productcat);
        system.debug('reslrdistdiscount = ' + reslrdistdiscount);
        
        if ( reslrdistdiscount == null || reslrdistdiscount < 1 ){
            reslrdistdiscount = 0;
         } 
        
        if ( productcat=='C' || productcat=='E'|| productcat=='D'|| productcat=='H'|| productcat=='I'|| productcat=='J' ){
            isService = true;
          }
        
        if ( isService == true && Limits.getQueries() <   Limits.getLimitQueries()) { 
            try
            {
            NSP_Margin_Schedule__c marginSchedule =  marginScheduleMap.get(reslrdistdiscount) != null ? 
										marginScheduleMap.get(reslrdistdiscount) : new NSP_Margin_Schedule__c();
                                           
            System.debug('Total Number of SOQL Queries allowed in this Apex code context: ' +  Limits.getLimitQueries());                               
            System.debug('1. Number of Queries used in this Apex code so far: ' + Limits.getQueries());
                    
                                        
                s = marginSchedule.Distributor_Discount__c;
                 
             }
            
            catch (System.NullPointerException e) {
             system.debug('Null Exception');
             }
                return s;
         
                                       
        }
        else {
            return 0;
        }      
        
  }

Mark as resolved if it's fine.
MaheemSamMaheemSam
Hi Jamal, 

    I am getting below error from the code I have made the code in bold which is throwing the error Please suggest me how to fix this issue. 


SObject row was retrieved via SOQL without querying the requested field: NSP_Margin_Schedule__c.Reseller_Discount__c
Error is in expression '{!callDistSaleMth}' in page optylineedit: Class.OptyLineEditCnt.callDistSaleMth: line 376, column 1

  
/* Call Reseller Discount change */
  public PageReference callDistSaleMth(){
        Decimal glistprice = 0;
        Decimal chkresller = 0;
        //String p_discount_rate;
      
        grandtotal = 0;
    
    /* New code added to handel too many soql */
    set<Decimal> resellerDiscountSet = new set<Decimal>();
    for(OptyLineWrapper linew : lineWrapr){
        if ( lineW.line.Reseller_discount_rate__c <> NULL)
        {
        resellerDiscountSet.add(decimal.valueof(lineW.line.Reseller_discount_rate__c));
        }
    }
        
    Map<Decimal, NSP_Margin_Schedule__c> marginScheduleMap = new Map<Decimal, NSP_Margin_Schedule__c>();  
                                 
    for(NSP_Margin_Schedule__c margSchedule : [Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                                    where  Reseller_Discount__c in :resellerDiscountSet and 
                                                    createddate < 2015-01-17T00:00:00-08:00 ]){
        if(!marginScheduleMap.containsKey(margSchedule.Reseller_Discount__c)){
            marginScheduleMap.put(margSchedule.Reseller_Discount__c, margSchedule);
        }
    } 
        
        for(OptyLineWrapper linew : lineWrapr){        
            OpportunityLineItem backuplne = backUpLines.get(lineW.line.id);     
            
            // Reseller 
            if ( lineW.line.PricebookEntry.name != 'COTERM' ){
                glistprice = lineW.line.listprice;
            }
            else {
                glistprice = lineW.line.CoTerm_List_Price__c;                 
            }   
            
            if ( lineW.line.Reseller_discount_rate__c  == NULL ){
                chkresller = 0;
            } 
            else{
                chkresller = Decimal.Valueof(lineW.line.Reseller_discount_rate__c);   
            } 
            
            //lineW.line.Discount_rate__c = String.valueOf(reCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,chkresller)); 
            lineW.line.Discount_rate__c = String.valueOf(newreCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,Decimal.Valueof(lineW.line.Reseller_discount_rate__c), marginScheduleMap));
            lineW.line.UnitPrice = reCalRslrDiscSalPrc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,chkresller);
            lineW.line.TotalPrice = lineW.line.Quantity * lineW.line.UnitPrice; 
           }  
           
          /* To get Grand Total */
        decimal total=0;
        for(integer i=0;i<lineWrapr.size();i++){
          total=total+lineWrapr[i].line.totalprice;
         } 
       
      grandtotal = total;
      differenceamount = oppamount - grandtotal; 
             
         editlinestatuschng(); 
         return null;
  }


Thanks

Sudhir
Jamal RidaJamal Rida
Hi Sudhir,

This is an abvious error, all fields from your sObject NSP_Margin_Schedule__c like Reseller_Discount__c and others that you're trying to fill should be part of your SOQL query as example : [Select Reseller_Discount__c.... from NSP_Margin_Schedule__c ]
 
MaheemSamMaheemSam
Hi Jamal, 

    There are 300 rows in visualforce page when i enter reseller discount value in one row and other is blank is this causing the error?

     Please adive me how to change the code. 

Thanks
Sudhir
Jamal RidaJamal Rida
No you have this error, when you're trying to put a value in a field that is not selected from Query, So whereever you need to fill a value of a field either from Apex Class  or VF Page it should be present in the Query like i stated before

[Select AllYourUsedFieldsfromThissObject from YourSObject]
MaheemSamMaheemSam
Thanks Jamal I got you I need another help there in adding one more condition is


  if ( productcat=='C' || productcat=='E'|| productcat=='D'|| productcat=='H'|| productcat=='I'|| productcat=='J' ){
            isService = true;
          }

when product cat is of any of above type isservice becomes true else by default it is false

  NSP_Margin_Schedule__c NMS =  [ Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                           where  Reseller_Discount__c = :reslrdistdiscount and 
                                           Service__c = :isService 
                                           and createddate < 2015-01-17T00:00:00-08:00 ];

Also how to handle the errow when query dont return any value it is throwing the error

Thanks
Sudhir
Jamal RidaJamal Rida
Yes i saw that, i don't think that this condition should be part of the query, you can do the folowing on  Line 63 : 
  
if(isService.equals(marginSchedule.Service__c){
    s = marginSchedule.Distributor_Discount__c;
}
Jamal RidaJamal Rida
The field Service__c should be part of your select also [Select Service__c from yoursObject] to avoid that first Exception.
MaheemSamMaheemSam
Thanks Jamal Please see the code below have added as you mentioned not sure i am getting below error 

Error: Compile Error: unexpected token: '{' at line  which i have highted in bold 
  
/* Call Reseller Discount change */
  public PageReference callDistSaleMth(){
        Decimal glistprice = 0;
        Decimal chkresller = 0;
        //String p_discount_rate;
      
        grandtotal = 0;
        
       /* New code to handle map too many soql */
       set<Decimal> resellerDiscountSet = new set<Decimal>();
       for(OptyLineWrapper linew : lineWrapr){
          if ( lineW.line.Reseller_discount_rate__c <> NULL)
          {
          resellerDiscountSet.add(decimal.valueof(lineW.line.Reseller_discount_rate__c));
          }
       }
        
      Map<Decimal, NSP_Margin_Schedule__c> marginScheduleMap = new Map<Decimal, NSP_Margin_Schedule__c>();                               
      for(NSP_Margin_Schedule__c margSchedule : [Select Distributor_Discount__c From NSP_Margin_Schedule__c 
                                                    where  Reseller_Discount__c in :resellerDiscountSet and 
                                                    createddate < 2015-01-17T00:00:00-08:00 ]){
                                                    
        if(!marginScheduleMap.containsKey(margSchedule.Reseller_Discount__c)){
            marginScheduleMap.put(margSchedule.Reseller_Discount__c, margSchedule);
        }
      }  /* New code to handle map too many soql */    
    
    for(OptyLineWrapper linew : lineWrapr){        
            OpportunityLineItem backuplne = backUpLines.get(lineW.line.id);     
            
            // Reseller 
            if ( lineW.line.PricebookEntry.name != 'COTERM' ){
                glistprice = lineW.line.listprice;
            }
            else {
                glistprice = lineW.line.CoTerm_List_Price__c;                 
            }   
            
            if ( lineW.line.Reseller_discount_rate__c  == NULL ){
                chkresller = 0;
            } 
            else{
                chkresller = Decimal.Valueof(lineW.line.Reseller_discount_rate__c);   
            } 
            
            //lineW.line.Discount_rate__c = String.valueOf(reCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,chkresller)); 
            lineW.line.Discount_rate__c = String.valueOf(newreCalRslrDisc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,Decimal.Valueof(lineW.line.Reseller_discount_rate__c), marginScheduleMap));
            lineW.line.UnitPrice = reCalRslrDiscSalPrc(glistprice,lineW.line.PricebookEntry.Product2.Category__c,chkresller);
            lineW.line.TotalPrice = lineW.line.Quantity * lineW.line.UnitPrice; 
           }  
           
          /* To get Grand Total */
        decimal total=0;
        for(integer i=0;i<lineWrapr.size();i++){
          total=total+lineWrapr[i].line.totalprice;
         } 
       
      grandtotal = total;
      differenceamount = oppamount - grandtotal; 
             
         editlinestatuschng(); 
         return null;
  }


  
/* calculate disti discount */  
  public static Decimal newreCalRslrDisc(Decimal listprice,String productcat, Decimal reslrdistdiscount, Map<Decimal,NSP_Margin_Schedule__c> marginScheduleMap){
        Boolean isService = false;
        Decimal s;
        
        system.debug('listprice = ' + listprice);
        system.debug('productcat = ' + productcat);
        system.debug('reslrdistdiscount = ' + reslrdistdiscount);
        
        if ( reslrdistdiscount == null || reslrdistdiscount < 1 ){
            reslrdistdiscount = 0;
         } 
        
        if ( productcat=='C' || productcat=='E'|| productcat=='D'|| productcat=='H'|| productcat=='I'|| productcat=='J' ){
            isService = true;
          }
        
        if ( isService == true && Limits.getQueries() <   Limits.getLimitQueries()) { 
            try
            {
            NSP_Margin_Schedule__c marginSchedule =  marginScheduleMap.get(reslrdistdiscount) != null ? 
                                        marginScheduleMap.get(reslrdistdiscount) : new NSP_Margin_Schedule__c();
                                           
            System.debug('Total Number of SOQL Queries allowed in this Apex code context: ' +  Limits.getLimitQueries());                               
            System.debug('1. Number of Queries used in this Apex code so far: ' + Limits.getQueries());
                    
                                        
               if(isService.equals(marginSchedule.Service__c){
 
                 s = marginSchedule.Distributor_Discount__c;
                }
                 
             }
            
            catch (System.NullPointerException e) {
             system.debug('Null Exception');
             }
                return s;                             
        }
        else {
            return 0;
        }      
        
  }


Thanks

Sudhir