Apex Code Development You need to sign in to do that
Don't have an account?
Lakshmi S SOQL Query Exception : AggregateResult caused too many rows exception?
Hi Team,
Requirement : Calculating sum and count using Trigger. We have to calculate for 20 fields in child object.
1. For each field we have to check condition : year, accountname(parent id) and field value.
2. For this i am using aggregate result function individually, because of that i am getting soql query exception.
SOQL Query Exception : AggregateResult caused too many rows exception?
Trigger:
------------
Regards
Lakshmi S
Requirement : Calculating sum and count using Trigger. We have to calculate for 20 fields in child object.
1. For each field we have to check condition : year, accountname(parent id) and field value.
2. For this i am using aggregate result function individually, because of that i am getting soql query exception.
SOQL Query Exception : AggregateResult caused too many rows exception?
Trigger:
------------
trigger KingsleyRollupSummaryTrig on KingsleyTranscript__c (after insert, after Update, after delete, after undelete) {
/*
if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUndelete)){
KingsleyRollupSummaryTrigHandlerCls.insertRecords((List<KingsleyTranscript__c>)Trigger.New);
}
if(Trigger.isAfter && Trigger.isUpdate){
//KingsleyRollupSummaryTrigHandlerCls.updateRecords((List<KingsleyTranscript__c>)Trigger.old);
}
if(Trigger.isAfter && Trigger.isDelete){
// KingsleyRollupSummaryTrigHandlerCls.deleteRecords((List<KingsleyTranscript__c>)Trigger.old);
}
*/
String recordYear = '2017';
Decimal amsum ;
Integer amcount ;
Decimal emeasum ;
Integer emeacount ;
Decimal apacsum ;
Integer apaccount ;
Decimal osrsum ;
Integer osrcount ;
Decimal vcrsum ;
Integer vcrcount ;
Decimal adorsum ;
Integer adorcount ;
Decimal tmpaorsum ;
Integer tmpaorcount ;
Decimal tmlrsum ;
Integer tmlrcount ;
Decimal tmpa_off_acct_sum ;
Integer tmpa_off_acct_count ;
Decimal fm_or_sum ;
Integer fm_or_count ;
Decimal fm_lr_sum ;
Integer fm_lr_count ;
Decimal fm_off_acct_lr_sum ;
Integer fm_off_acct_lr_count ;
Decimal pjm_off_acct_lr_sum ;
Integer pjm_off_acct_lr_count ;
Decimal pjm_or_sum ;
Integer pjm_or_count ;
Decimal pjm_lr_sum ;
Integer pjm_lr_count ;
Decimal remc_or_sum ;
Integer remc_or_count ;
Decimal off_acct_remc_sum ;
Integer off_acct_remc_count ;
Decimal thought_lp_sum ;
Integer thought_lp_count ;
Decimal exec_or_sum ;
Integer exec_or_count ;
Decimal peer_nps_sum ;
Integer peer_nps_count ;
List<Account> updateAccs = new List<Account>();
Set<Id> accIds = new Set<Id>();
if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUpdate || Trigger.isUndelete)){
for(KingsleyTranscript__c kt : Trigger.New){
if(kt.Account_Name__c != null){
accIds.add(kt.Account_Name__c);
}
}
}
if(Trigger.isAfter && (Trigger.isUpdate || Trigger.isDelete)){
for(KingsleyTranscript__c kt : Trigger.New){
if(kt.Account_Name__c != null){
accIds.add(kt.Account_Name__c);
}
}
}
if(accIds.size() > 0){
List<Account> acclist = [Select id,name,RS_Reg_Client_Satisfaction_Ams_Sum__c,RS_Reg_Client_Satisfaction_Ams_Count__c,RS_Reg_Client_Satisfaction_EMEA_Sum__c,
RS_Reg_Client_Satisfaction_EMEA_Count__c,RS_Reg_Client_Satisfaction_APAC_Sum__c,RS_Reg_Client_Satisfaction_APAC_Count__c,
RS_Overall_Satisfaction_Rating_Sum__c,RS_Overall_Satisfaction_Rating_Count__c,RS_Value_Contribution_Rating_Sum__c,
RS_Value_Contribution_Rating_Count__c,RS_AD_Overall_Rating_Sum__c,RS_AD_Overall_Rating_Count__c,RS_TM_PA_Overall_Rating_Sum__c,
RS_TM_PA_Overall_Rating_Count__c,RS_TM_Leader_Rating_Sum__c,RS_TM_Leader_Rating_Count__c,RS_TM_PA_Off_Acct_Leader_Rating_Sum__c,
RS_TM_PA_Off_Acct_Leader_Rating_Count__c,RS_FM_Overall_Rating_Sum__c,RS_FM_Overall_Rating_Count__c,RS_FM_Leader_Rating_Sum__c,
RS_FM_Leader_Rating_Count__c,RS_FM_Off_Acct_Leadership_Rating_Sum__c,RS_FM_Off_Acct_Leadership_Rating_Count__c,RS_PJM_Overall_Rating_Sum__c,
RS_PJM_Overall_Rating_Count__c,RS_PJM_Leader_Rating_Sum__c,RS_PJM_Leader_Rating_Count__c,RS_PJM_Off_Acct_Leadership_Rating_Sum__c,
RS_PJM_Off_Acct_Leadership_Rating_Count__c,RS_REMC_Overall_Rating_Sum__c,RS_REMC_Overall_Rating_Count__c,RS_Off_Acct_REMC_Leader_Rating_Sum__c,
RS_Off_Acct_REMC_Leader_Rating_Count__c,RS_Thought_Leadership_progs_Rating_Sum__c,RS_Thought_Leadership_prog_Rating_Count__c,
RS_Exec_Leadership_Overall_Rating_Sum__c,RS_Exec_Leadership_Overall_Rating_Count__c,RS_Peer_Recommendation_NPS_Rating_Sum__c,RS_Peer_Recommend_NPS_Rating_Count__c,
(Select id,Regional_Client_Satisfaction_Americas__c,Regional_Client_Satisfaction_EMEA__c,Regional_Client_Satisfaction_APAC__c,
Overall_satisfaction_Rating__c,Value_Contribution_Rating__c,Additional_Vakue_Focus_Areas__c,AD_Overall_Rating__c,
TM_PA_Overall_Rating__c,TM_Leader_Rating__c,TM_PA_Off_Account_Leader_Rating__c,FM_Overall_Rating__c,FM_Leader_Rating__c,
FM_Off_Acct_Lship_Rating__c,PJM_Overall_Rating__c,PJM_Leader_Rating__c,PJM_Off_Acct_Leadership_Rating__c,REMC_Overall_Rating__c,
Off_Account_REMC_Leader_Rating__c,Thought_Ldrship_Pgm_Rating__c,Exec_Ldrship_Overall_Rating__c,Peer_Recomend_NPS_Rating__c,
Recorded_Year__c from Kingsley_Transcript__r) from Account where Id In :accIds];
for(Account acc : acclist){
// for 'Regional_Client_Satisfaction_Americas__c' field value calculation
AggregateResult[] agres = [Select SUM(Regional_Client_Satisfaction_Americas__c)amsum,COUNT(Regional_Client_Satisfaction_Americas__c)amcount
from KingsleyTranscript__c where Account_Name__c =: acc.id and Regional_Client_Satisfaction_Americas__c > 0 and Recorded_Year__c=: recordYear];
if(agres.size() > 0){
for(AggregateResult ag : agres){
amsum = (Decimal)ag.get('amsum');
amcount = (Integer)ag.get('amcount');
}
acc.RS_Reg_Client_Satisfaction_Ams_Sum__c = amsum;
acc.RS_Reg_Client_Satisfaction_Ams_Count__c = amcount;
}
// for 'Regional_Client_Satisfaction_EMEA__c' field value calculation
AggregateResult[] agres2 = [Select SUM(Regional_Client_Satisfaction_EMEA__c)emeasum,COUNT(Regional_Client_Satisfaction_EMEA__c)emeacount
from KingsleyTranscript__c where Account_Name__c =: acc.id and Regional_Client_Satisfaction_EMEA__c > 0 and Recorded_Year__c=: recordYear];
if(agres2.size() > 0){
for(AggregateResult ag : agres2){
emeasum = (Decimal)ag.get('emeasum');
emeacount = (Integer)ag.get('emeacount');
}
acc.RS_Reg_Client_Satisfaction_EMEA_Sum__c = emeasum;
acc.RS_Reg_Client_Satisfaction_EMEA_Count__c = emeacount;
}
// for 'Regional_Client_Satisfaction_APAC__c' field value calculation
AggregateResult[] agres3 = [Select SUM(Regional_Client_Satisfaction_APAC__c)apacsum,COUNT(Regional_Client_Satisfaction_APAC__c)apaccount
from KingsleyTranscript__c where Account_Name__c =: acc.id and Regional_Client_Satisfaction_APAC__c > 0 and Recorded_Year__c=: recordYear];
if(agres3.size() > 0){
for(AggregateResult ag : agres3){
apacsum = (Decimal)ag.get('apacsum');
apaccount = (Integer)ag.get('apaccount');
}
acc.RS_Reg_Client_Satisfaction_APAC_Sum__c = apacsum;
acc.RS_Reg_Client_Satisfaction_APAC_Count__c = apaccount;
}
// for 'Overall_satisfaction_Rating__c' field value calculation
AggregateResult[] agres4 = [Select SUM(Overall_satisfaction_Rating__c)osrsum,COUNT(Overall_satisfaction_Rating__c)osrcount
from KingsleyTranscript__c where Account_Name__c =: acc.id and Overall_satisfaction_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres4.size() > 0){
for(AggregateResult ag : agres4){
osrsum = (Decimal)ag.get('osrsum');
osrcount = (Integer)ag.get('osrcount');
}
acc.RS_Overall_Satisfaction_Rating_Sum__c = osrsum;
acc.RS_Overall_Satisfaction_Rating_Count__c = osrcount;
}
// for 'Value_Contribution_Rating__c' field value calculation
AggregateResult[] agres5 = [Select SUM(Value_Contribution_Rating__c)vcrsum,COUNT(Value_Contribution_Rating__c)vcrcount from KingsleyTranscript__c where Account_Name__c =: acc.id and Value_Contribution_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres5.size() > 0){
for(AggregateResult ag : agres5){
vcrsum = (Decimal)ag.get('vcrsum');
vcrcount = (Integer)ag.get('vcrcount');
}
acc.RS_Value_Contribution_Rating_Sum__c = vcrsum;
acc.RS_Value_Contribution_Rating_Count__c = vcrcount;
}
// for 'AD_Overall_Rating__c' field value calculation
AggregateResult[] agres6 = [Select SUM(AD_Overall_Rating__c)adorsum,COUNT(AD_Overall_Rating__c)adorcount from KingsleyTranscript__c where Account_Name__c =: acc.id and AD_Overall_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres6.size() > 0){
for(AggregateResult ag : agres6){
adorsum = (Decimal)ag.get('adorsum');
adorcount = (Integer)ag.get('adorcount');
}
acc.RS_AD_Overall_Rating_Sum__c = adorsum;
acc.RS_AD_Overall_Rating_Count__c = adorcount;
}
// for 'TM_PA_Overall_Rating__c' field value calculation
AggregateResult[] agres7 = [Select SUM(TM_PA_Overall_Rating__c)tmpaorsum,COUNT(TM_PA_Overall_Rating__c)tmpaorcount
from KingsleyTranscript__c where Account_Name__c =: acc.id and TM_PA_Overall_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres7.size() > 0){
for(AggregateResult ag : agres7){
tmpaorsum = (Decimal)ag.get('tmpaorsum');
tmpaorcount = (Integer)ag.get('tmpaorcount');
}
acc.RS_TM_PA_Overall_Rating_Sum__c = tmpaorsum;
acc.RS_TM_PA_Overall_Rating_Count__c = tmpaorcount;
}
// for 'TM_Leader_Rating__c' field value calculation
AggregateResult[] agres8 = [Select SUM(TM_Leader_Rating__c)tmlrsum,COUNT(TM_Leader_Rating__c)tmlrcount
from KingsleyTranscript__c where Account_Name__c =: acc.id and TM_Leader_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres8.size() > 0){
for(AggregateResult ag : agres8){
tmlrsum = (Decimal)ag.get('tmlrsum');
tmlrcount = (Integer)ag.get('tmlrcount');
}
acc.RS_TM_Leader_Rating_Sum__c = tmlrsum;
acc.RS_TM_Leader_Rating_Count__c = tmlrcount;
}
// for 'TM_PA_Off_Account_Leader_Rating__c' field value calculation
AggregateResult[] agres9 = [Select SUM(TM_PA_Off_Account_Leader_Rating__c)tmpa_off_acct_sum,COUNT(TM_PA_Off_Account_Leader_Rating__c)tmpa_off_acct_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and TM_PA_Off_Account_Leader_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres9.size() > 0){
for(AggregateResult ag :agres9 ){
tmpa_off_acct_sum = (Decimal)ag.get('tmpa_off_acct_sum');
tmpa_off_acct_count = (Integer)ag.get('tmpa_off_acct_count');
}
acc.RS_TM_PA_Off_Acct_Leader_Rating_Sum__c = tmpa_off_acct_sum;
acc.RS_TM_PA_Off_Acct_Leader_Rating_Count__c = tmpa_off_acct_count;
}
// for 'FM_Overall_Rating__c' field value calculation
AggregateResult[] agres10 = [Select SUM(FM_Overall_Rating__c)fm_or_sum,COUNT(FM_Overall_Rating__c)fm_or_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and FM_Overall_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres10.size() > 0){
for(AggregateResult ag : agres10){
fm_or_sum = (Decimal)ag.get('fm_or_sum');
fm_or_count = (Integer)ag.get('fm_or_count');
}
acc.RS_FM_Overall_Rating_Sum__c = fm_or_sum;
acc.RS_FM_Overall_Rating_Count__c = fm_or_count;
}
// for 'FM_Leader_Rating__c' field value calculation
AggregateResult[] agres11 = [Select SUM(FM_Leader_Rating__c)fm_lr_sum,COUNT(FM_Leader_Rating__c)fm_lr_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and FM_Leader_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres11.size() > 0){
for(AggregateResult ag : agres11){
fm_lr_sum = (Decimal)ag.get('fm_lr_sum');
fm_lr_count = (Integer)ag.get('fm_lr_count');
}
acc.RS_FM_Leader_Rating_Sum__c = fm_lr_sum;
acc.RS_FM_Leader_Rating_Count__c = fm_lr_count;
}
// for 'FM_Off_Acct_Lship_Rating__c' field value calculation
AggregateResult[] agres12 = [Select SUM(FM_Off_Acct_Lship_Rating__c)fm_off_acct_lr_sum,COUNT(FM_Off_Acct_Lship_Rating__c)fm_off_acct_lr_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and FM_Off_Acct_Lship_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres12.size() >0){
for(AggregateResult ag : agres12){
fm_off_acct_lr_sum = (Decimal)ag.get('fm_off_acct_lr_sum');
fm_off_acct_lr_count = (Integer)ag.get('fm_off_acct_lr_count');
}
acc.RS_FM_Off_Acct_Leadership_Rating_Sum__c = fm_off_acct_lr_sum;
acc.RS_FM_Off_Acct_Leadership_Rating_Count__c = fm_off_acct_lr_count;
}
// for 'PJM_Overall_Rating__c' field value calculation
AggregateResult[] agres13 = [Select SUM(PJM_Overall_Rating__c)pjm_or_sum,COUNT(PJM_Overall_Rating__c)pjm_or_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and PJM_Overall_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres13.size() > 0){
for(AggregateResult ag : agres13){
pjm_or_sum = (Decimal)ag.get('pjm_or_sum');
pjm_or_count = (Integer)ag.get('pjm_or_count');
}
acc.RS_PJM_Overall_Rating_Sum__c = pjm_or_sum;
acc.RS_PJM_Overall_Rating_Count__c = pjm_or_count;
}
// for 'PJM_Leader_Rating__c' field value calculation
AggregateResult[] agres14 = [Select SUM(PJM_Leader_Rating__c)pjm_lr_sum,COUNT(PJM_Leader_Rating__c)pjm_lr_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and PJM_Leader_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres14.size()>0){
for(AggregateResult ag : agres14){
pjm_lr_sum = (Decimal)ag.get('pjm_lr_sum');
pjm_lr_count = (Integer)ag.get('pjm_lr_count');
}
acc.RS_PJM_Leader_Rating_Sum__c = pjm_lr_sum;
acc.RS_PJM_Leader_Rating_Count__c = pjm_lr_count;
}
// for 'PJM_Off_Acct_Leadership_Rating__c field value calculation
AggregateResult[] agres15 = [Select SUM(PJM_Off_Acct_Leadership_Rating__c)pjm_off_acct_lr_sum,COUNT(PJM_Off_Acct_Leadership_Rating__c)pjm_off_acct_lr_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and PJM_Off_Acct_Leadership_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres15.size() > 0){
for(AggregateResult ag : agres15){
pjm_off_acct_lr_sum = (Decimal)ag.get('pjm_off_acct_lr_sum');
pjm_off_acct_lr_count = (Integer)ag.get('pjm_off_acct_lr_count');
}
acc.RS_PJM_Off_Acct_Leadership_Rating_Sum__c = pjm_off_acct_lr_sum;
acc.RS_PJM_Off_Acct_Leadership_Rating_Count__c = pjm_off_acct_lr_count;
}
// for 'REMC_Overall_Rating__c' field value calculation
AggregateResult[] agres16 = [Select SUM(REMC_Overall_Rating__c)remc_or_sum,COUNT(REMC_Overall_Rating__c)remc_or_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and REMC_Overall_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres16.size()>0){
for(AggregateResult ag : agres16){
remc_or_sum = (Decimal)ag.get('remc_or_sum');
remc_or_count = (Integer)ag.get('remc_or_count');
}
acc.RS_REMC_Overall_Rating_Sum__c = remc_or_sum;
acc.RS_REMC_Overall_Rating_Count__c = remc_or_count;
}
// for 'Off_Account_REMC_Leader_Rating__c' field value calculation
AggregateResult[] agres17 = [Select SUM(Off_Account_REMC_Leader_Rating__c)off_acct_remc_sum,COUNT(Off_Account_REMC_Leader_Rating__c)off_acct_remc_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and Off_Account_REMC_Leader_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres17.size()>0){
for(AggregateResult ag : agres17) {
off_acct_remc_sum = (Decimal)ag.get('off_acct_remc_sum');
off_acct_remc_count = (Integer)ag.get('off_acct_remc_count');
}
acc.RS_Off_Acct_REMC_Leader_Rating_Sum__c = off_acct_remc_sum;
acc.RS_Off_Acct_REMC_Leader_Rating_Count__c = off_acct_remc_count;
}
// for 'Thought_Ldrship_Pgm_Rating__c field value calculation
AggregateResult[] agres18 = [Select SUM(Thought_Ldrship_Pgm_Rating__c)thought_lp_sum,COUNT(Thought_Ldrship_Pgm_Rating__c)thought_lp_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and Thought_Ldrship_Pgm_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres18.size()>0){
for(AggregateResult ag : agres18){
thought_lp_sum = (Decimal)ag.get('thought_lp_sum');
thought_lp_count = (Integer)ag.get('thought_lp_count');
}
acc.RS_Thought_Leadership_progs_Rating_Sum__c = thought_lp_sum;
acc.RS_Thought_Leadership_prog_Rating_Count__c = thought_lp_count;
}
// for 'Exec_Ldrship_Overall_Rating__c' field value calculation
AggregateResult[] agres19 = [Select SUM(Exec_Ldrship_Overall_Rating__c)exec_or_sum,COUNT(Exec_Ldrship_Overall_Rating__c)exec_or_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and Exec_Ldrship_Overall_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres19.size()>0){
for(AggregateResult ag : agres19){
exec_or_sum = (Decimal)ag.get('exec_or_sum');
exec_or_count = (Integer)ag.get('exec_or_count');
}
acc.RS_Exec_Leadership_Overall_Rating_Sum__c = exec_or_sum;
acc.RS_Exec_Leadership_Overall_Rating_Count__c = exec_or_count;
}
// for 'Peer_Recomend_NPS_Rating__c' field value calculation
AggregateResult[] agres20 = [Select SUM(Peer_Recomend_NPS_Rating__c)peer_nps_sum,COUNT(Peer_Recomend_NPS_Rating__c)peer_nps_count
from KingsleyTranscript__c where Account_Name__c =: acc.id and Peer_Recomend_NPS_Rating__c > 0 and Recorded_Year__c=: recordYear];
if(agres20.size()>0){
for(AggregateResult ag : agres20){
peer_nps_sum = (Decimal)ag.get('peer_nps_sum');
peer_nps_count = (Integer)ag.get('peer_nps_count');
}
acc.RS_Peer_Recommendation_NPS_Rating_Sum__c = peer_nps_sum;
acc.RS_Peer_Recommend_NPS_Rating_Count__c = peer_nps_count;
}
updateAccs.add(acc);
}
Database.update(updateAccs,false);
}
}
Can any one let me know how to solve this problem?Regards
Lakshmi S
Could you check line no 110... Soql query inside the for loop.
Thanks
Jayabalaji
Thanks for your response...
can you please advise me, how to resolve this issue...
my requirement is, calculate the sum and count based on condition (calculating the value based on year and field value >0).
Let me know how can i do this?
Regards
Lakshmi S.
Instead of the query in loop, you can query outside the loop and use a filter of account_name__c In setOfAccIds. And store aggregate result in a map.
Thanks