function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
mmaxtrammaxtra 

SOQL 21 Error Please help...

I am getting the SOQL Query 21 Error and I really do not know what to do can someone please suggest what I should do....

I know the For loop has something to do with my query being in it, but if I take it out I do not know what to do or how to actual do this then...

 

I am getting my error :

 

for (opportunitylineitem oppline : opp.OpportunityLineItems) { i++; OpportunityLineItem sf = [SELECT Id, Product_Family__c FROM OpportunityLineItem where Id=:oppline.Id];

 This is my class...

 

 

 

public static void copyLineItemsFromOpp(List<SFDC_520_Quote__c> qs) { // We will modify and update the quotes with totals at the end, so we clone it // first since Trigger.new records cannot be modified in after triggers. Notice that // we use the deepClone method on the list as clone for sobjects creates a shallow // copy which is still read-only. List<SFDC_520_quote__c> quoteList = qs.deepClone(true); try { // By setting the inQuoteTrigger class variable, this method tells // the quoteLine trigger that it should disable itself during this // operation (the quoteLine trigger is described in Example 3) inQuoteTrigger = true; //Now the method creates an empty array of quote line items List<SFDC_520_QuoteLine__c> quoteItemList = new List<SFDC_520_QuoteLine__c>(); // Next get the opportunity line items for the respective Opportunities. // This demonstrates the aggregate relationship query pattern for getting // children correlated back to their parent. You could also query // OpportunityLineItem directly but would then have to correlate the items // back to their respective parents (opportunity) inside the code manually. // We are constructing a map so the respective opportunity // (with its line items) can be easily correlated back to quote as we process // them below. Map<Id, Opportunity> opportunityMap = new Map<Id, Opportunity>( [select Id,Partner__c, (Select Id, OpportunityId, SortOrder, PricebookEntryId, Quantity, TotalPrice, UnitPrice, ListPrice, ServiceDate, Description, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, IsDeleted , PricebookEntry.Name, PricebookEntry.Product2id,CurrencyIsoCode from opportunitylineitems order by createddate) from Opportunity where Id IN :getOpportunityIds(quoteList)]); // Iterate through the quotes in the bulk operation. for(SFDC_520_Quote__c quote:quoteList) { // Next, the method manually calculates the overall total for the quote // by iterating over an array of OpportunityLineItem records from the // preceeding SOQL query. The query selects the data from opportunity // line items that match the opportunity associated with the quote. Double tot = 0; // The running total Integer i = 0; // Number the line items // Get the respective opportunity and its line items from the map. Opportunity opp = opportunityMap.get(quote.opportunity__c); // If this quote is not associated to an opportunity then skip it. if(opp == null) continue; for (opportunitylineitem oppline : opp.OpportunityLineItems) { i++; OpportunityLineItem sf = [SELECT Id, Product_Family__c FROM OpportunityLineItem where Id=:oppline.Id]; // For each opportunity line item, the method // calculates the running total if (oppline.unitprice != null && oppline.quantity != null) tot += oppline.unitprice * oppline.quantity; // And then it adds a new quote line item to the quoteitems, // array, initializing it with values. Reseller__c[] r =[Select Name, License__c from Reseller__c where Name=:quote.Partner_Direct__c]; if(r.isEmpty()){ quoteItemList.add(new SFDC_520_quoteline__c( quote__c = quote.id, // The method uses the counter variable to number // the line items. Because the SOQL query ordered // them by the date they were created with an ORDER BY // clause, they will be numbered that way as well. //name = String.valueOf(i), // The method then uses the values for product, unit // price, quantity, total price, and color from the // opportunity line item. Sales_Discount__c = 0.0, Qty_Ordered__c = oppline.Quantity, Unit_Price__c = oppline.UnitPrice, Description__c = oppline.Description, ServiceDate__c = oppline.ServiceDate, Product2__c = oppline.PricebookEntry.Product2id, CurrencyIsoCode = oppline.CurrencyIsoCode ));} // The method uses the counter variable to number // the line items. Because the SOQL query ordered // them by the date they were created with an ORDER BY // clause, they will be numbered that way as well. //name = String.valueOf(i), // The method then uses the values for product, unit // price, quantity, total price, and color from the // opportunity line item. if(!r.isEmpty() && sf.Product_Family__c =='License'){ quoteItemList.add(new SFDC_520_quoteline__c( quote__c = quote.id, Sales_Discount__c = r[0].License__c, Qty_Ordered__c = oppline.Quantity, Unit_Price__c = oppline.UnitPrice, Description__c = oppline.Description, ServiceDate__c = oppline.ServiceDate, Product2__c = oppline.PricebookEntry.Product2id, CurrencyIsoCode = oppline.CurrencyIsoCode )); } if(!r.isEmpty() && sf.Product_Family__c <>'License'){ quoteItemList.add(new SFDC_520_quoteline__c( quote__c = quote.id, Sales_Discount__c = 0.0, Qty_Ordered__c = oppline.Quantity, Unit_Price__c = oppline.UnitPrice, Description__c = oppline.Description, ServiceDate__c = oppline.ServiceDate, Product2__c = oppline.PricebookEntry.Product2id, CurrencyIsoCode = oppline.CurrencyIsoCode )); } } } //The method adds the whole array of quote line items //to the database with a single insert statement. insert quoteItemList; update quoteList; } finally { // To clean up the working variable, the method resets inQuoteTrigger to false; inQuoteTrigger = false; } }

 

 

 

sfdcfoxsfdcfox

You're using queries inside of a FOR loop. This will cause you to quickly exceed your limit-- the query would have to run once per iteration of the loop, instead of once for all the values in the list. This means that it would take only 20 line items, quotes, or some combination thereof to exceed the limit.

 

Outside of your main loop, I would suggest the following changes:

 

1) Use this for the licenses from the Reseller__c object (before main loop):

 

 

Map<String,Reseller__c> mapReseller = new Map<String,Reseller__c>(); for(SFDC_520_Quote__c quote:quoteList) mapReseller.put(quote.Partner_Direct__c,null); for(Reseller__c reseller: [select name,license__c from reseller__c where name in :mapReseller.keyset()]) mapReseller.put(reseller.name,reseller);

 

 2) Reference the license values from above using:

 

mapReseller.get(quote.Partner_Direct__c).License__c;

 

3) Change the opportunitylineitem sub-query to include the Product_Family__c, and remove that query as well from your main loop; you can reference the Product_Family__c from oppline instead of querying for it inside the loop.

 

 

mmaxtrammaxtra

Thank you for the great help.. Really appreciate it... So I added the Mapping and took away the Reseller Query from the loop... But where I had this code:

 

if(r.isEmpty()){

 I substituted:

 

//if(r.isEmpty()){ if(mapReseller.get(quote.Partner_Direct__c).License__c==null){

 

 I got an error stating:

System.NullPointerException: Attempt to de-reference a null object

 

Did I reference it incorrectly? Is there a better way to reference it when the query is empty or not null...

 

Also, should I take this out from my loop as well:

 

OpportunityLineItem sf = [SELECT Id, Product_Family__c FROM OpportunityLineItem where Id=:oppline.Id];

 Is this the same way that you have shown in your example.... or is this ok to be left in..

Thank you sooo much for your help

really appreciate everything

 

 

 

 

 

 

WesNolte__cWesNolte__c

Hey

 

Although this code uses a trigger as an example it should get you going in the correct direction:

 

http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content%2FlangCon_apex_loops_for_SOQL.htm|SkinName=webhelp

 

You can also look at this article, which uses a slightly simpler example:

 

http://developinthecloud.wordpress.com/2009/07/01/how-to-avoid-avoid-governor-limits-part-1-of-n/

 

Cheers,

Wes