Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Karna ShivaKarna Shiva 

i am getting too many SOQL Error and business needs I have to write SOQL inside For loop, is there any other way to write SOQL outside for loop, please look at my code? Start Method :

query += ' select id from opportunity ';
query += ' where company__c = \'' + company + '\' ';
query += ' and stagename in (\'E1\',\'E2\',\'E3\',\'D1\',\'D2\',\'D3\',\'C\',\'B\',\'S\')';


Execute Method :

   set<Id> optyWithQuoteSet = new set<Id>();
    
      List<Rebate_Amount__c> RBTableList = new List<Rebate_Amount__c>();
  
      for (sObject obj :scope)
      {
         Opportunity op = (Opportunity)obj;
          
         optyWithQuoteSet.add(op.id);
      }

List<Quote_Line_Item__c> Shiped_data = [select  Product__r.name,Net_Amount_5__c,Quote__r.Opportunity.Distribution_Channel__c
,Quote__r.Net_Amount__c,Product__r.Product_Group_Report__c,Line_Item_Number__c  from Quote_Line_Item__c where Quote__r.QuoteNumber<> null and
Quote__r.Opportunity.StageName='S' and Quote__r.Opportunity.id=:optyWithQuoteSet ];


for(Quote_Line_Item__c oli : Shiped_data)
{

Rebate_Amount__c rv= new Rebate_Amount__c();
   rv.Sale_Group__c= oli.Quote__r.Opportunity.Sale_Group__c;
   rv.Sale_Office__c = oli.Quote__r.Opportunity.Sale_Office__c;
   rv.Enquiry_No__c = oli.Quote__r.Opportunity.Auto_Number__c;
   rv.Rebate_by_Qountity__c=0;
   rv.Customer_Id__c = oli.Quote__r.Opportunity.Account.Account_Number__c;
   rv.Item_Number__c = oli.Line_Item_Number__c;
        system.debug(' out side shipped for loop ');
   for (Shipped__c sch :[select cost__c,Material__c  ,Opportunity2__r.id from Shipped__c where Quote__c!=null and Material__c=:oli.Product__r.name and Opportunity2__r.id=:oli.Quote__r.Opportunity.id])
   {
      system.debug(' Shipped for loop ');
    
   if (sch.Opportunity2__r.id == oli.Quote__r.Opportunity.id && oli.Product__r.name ==sch.Material__c)
   {
   rv.Cost__c = sch.cost__c;
   }
   RBTableList.add(rv);
   }
   }  
 
   if(RBTableList.size()>0)
   {
   insert RBTableList;
   }
Tony TannousTony Tannous
Karna ,

you can do the below 



set<Id> optyWithQuoteSet = new set<Id>();
   
      List<Rebate_Amount__c> RBTableList = new List<Rebate_Amount__c>();
 
      for (sObject obj :scope)
      {
         Opportunity op = (Opportunity)obj;
         optyWithQuoteSet.add(op.id);
      }

List<Quote_Line_Item__c> Shiped_data = [select  Product__r.name,Net_Amount_5__c,Quote__r.Opportunity.Distribution_Channel__c
,Quote__r.Net_Amount__c,Product__r.Product_Group_Report__c,Line_Item_Number__c  from Quote_Line_Item__c where Quote__r.QuoteNumber<> null and
Quote__r.Opportunity.StageName='S' and Quote__r.Opportunity.id=:optyWithQuoteSet ];


list<Shipped__c>  listSch =[select cost__c,Material__c  ,Opportunity2__c 
                                                  from Shipped__c
                                                     where Quote__c!=null ];

map<string,Shipped__c> mapShipped= new map<string,Shipped__c>();
for(Shipped__c sch : listSch)
{
mapShipped.put(sch.Opportunity2__c +sch.Material__c,sch);
}

for(Quote_Line_Item__c oli : Shiped_data)
{

  Rebate_Amount__c rv= new Rebate_Amount__c();
   rv.Sale_Group__c= oli.Quote__r.Opportunity.Sale_Group__c;
   rv.Sale_Office__c = oli.Quote__r.Opportunity.Sale_Office__c;
   rv.Enquiry_No__c = oli.Quote__r.Opportunity.Auto_Number__c;
   rv.Rebate_by_Qountity__c=0;
   rv.Customer_Id__c = oli.Quote__r.Opportunity.Account.Account_Number__c;
   rv.Item_Number__c = oli.Line_Item_Number__c;
  
   string keyMap=oli.Quote__r.Opportunity.id+oli.Product__r.name;
     if (mapShipped.containskey(keyMap))
     {
            rv.Cost__c = mapShipped.get(keyMap).cost__c;
     }
      RBTableList.add(rv);
  
    } 

   if(RBTableList.size()>0)
   {
       insert RBTableList;
   }


Good Luck
Karna ShivaKarna Shiva
thanks tony, let me try
Karna ShivaKarna Shiva
It's worked very well, but i am getting Too many SOQL -10001 error.
Tony TannousTony Tannous
can you try this i used the SoqlFor loop:

set<Id> optyWithQuoteSet = new set<Id>();
  
    

      for (sObject obj :scope)
      {
         Opportunity op = (Opportunity)obj;
         optyWithQuoteSet.add(op.id);
      }



list<Shipped__c>  listSch =[select cost__c,Material__c  ,Opportunity2__c
                                                  from Shipped__c
                                                     where Quote__c!=null ];

map<string,Shipped__c> mapShipped= new map<string,Shipped__c>();
for(Shipped__c sch : listSch)
{
  mapShipped.put(sch.Opportunity2__c +sch.Material__c,sch);
}

for( list<Quote_Line_Item__c> listQuote : [select  Product__r.name,Net_Amount_5__c,Quote__r.Opportunity.Distribution_Channel__c
,Quote__r.Net_Amount__c,Product__r.Product_Group_Report__c,Line_Item_Number__c  from Quote_Line_Item__c where Quote__r.QuoteNumber<> null and
Quote__r.Opportunity.StageName='S' and Quote__r.Opportunity.id=:optyWithQuoteSet])
{
List<Rebate_Amount__c> RBTableList = new List<Rebate_Amount__c>();
for(Quote_Line_Item__c oli : listQuote)
{

  Rebate_Amount__c rv= new Rebate_Amount__c();
   rv.Sale_Group__c= oli.Quote__r.Opportunity.Sale_Group__c;
   rv.Sale_Office__c = oli.Quote__r.Opportunity.Sale_Office__c;
   rv.Enquiry_No__c = oli.Quote__r.Opportunity.Auto_Number__c;
   rv.Rebate_by_Qountity__c=0;
   rv.Customer_Id__c = oli.Quote__r.Opportunity.Account.Account_Number__c;
   rv.Item_Number__c = oli.Line_Item_Number__c;
 
   string keyMap=oli.Quote__r.Opportunity.id+oli.Product__r.name;
     if (mapShipped.containskey(keyMap))
     {
            rv.Cost__c = mapShipped.get(keyMap).cost__c;
     }
      RBTableList.add(rv);
 
    }

     if(RBTableList.size()>0)
     {
          insert RBTableList;
     }
  }
}