You need to sign in to do that
Don't have an account?
GMASJ
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];
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
Can we share screen and found the actuall root cause for this issue. Please connect with me on skype: gaurav62990
Thanks,
Gaurav
All Answers
What is the exact error you are facing.
1. Too many soql query rows : 10000
2. Too many soql 101
Thanks,
Gaurav
See https://developer.salesforce.com/forums/?id=906F00000009BhUIAU
Please suggest me how to modify I also have a helper class which is been used inside the trigger
Thanks
Sudhir
Well, I don't see a loop in that test class either. But somehow your line 65 is being invoked > 100 times.
Can we share screen and found the actuall root cause for this issue. Please connect with me on skype: gaurav62990
Thanks,
Gaurav
I have added you in skype please accept
Thanks
Sudhir
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.