Apex Code Development You need to sign in to do that
Don't have an account?
Lakshmi S Query Exception : Aggregate result error
HI Team,
AfterInsert caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop External entry point.
Can any one let me know, how to do this.......
AfterInsert caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop External entry point.
AfterInsert caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop External entry point
public class KingsleyRollupSummaryTrigHandlerCls {
// Declaring static variables for count,sum and record year.
Public static String recordYear = '2017';
Public static Decimal amsum ;
Public static Integer amcount ;
Public static Decimal emeasum ;
Public static Integer emeacount ;
Public static Decimal apacsum ;
Public static Integer apaccount ;
// Static insert method...
Public Static void insertRecords(List<KingsleyTranscript__c> newrecs){
Set<String> accIds = new Set<String>();
for(KingsleyTranscript__c kt : newrecs){
if(kt.Account_Name__c != null){
accIds.add(kt.Account_Name__c);
}
}
//List<Account> accountsToUpdate = new List<Account>();
//List<Account> accountsToUpdate2 = new List<Account>();
Map<Id,Account> updateAccMap = new Map<Id,Account>();
Map<Id,Account> accountMap = new Map<Id,Account>([Select id,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
from Account where Id In :accIds]);
// for 'Regional_Client_Satisfaction_Americas__c' field value calculation
for(AggregateResult agres : [Select Account_Name__c,Recorded_Year__c, SUM(Regional_Client_Satisfaction_Americas__c)amsum,
COUNT(Regional_Client_Satisfaction_Americas__c)amcount,SUM(Regional_Client_Satisfaction_APAC__c)apacsum,
COUNT(Regional_Client_Satisfaction_APAC__c)apaccount,SUM(Regional_Client_Satisfaction_EMEA__c)emeasum,
COUNT(Regional_Client_Satisfaction_EMEA__c)emeacount from
KingsleyTranscript__c where Account_Name__c IN :accIds and (Regional_Client_Satisfaction_Americas__c >0 or Regional_Client_Satisfaction_Americas__c >0 or Regional_Client_Satisfaction_EMEA__c >0) and Recorded_Year__c=: recordYear Group By Account_Name__c,Recorded_Year__c]){
String aId = (String)agres.get('Account_Name__c');
Account a = accountMap.get(aId);
a.RS_Reg_Client_Satisfaction_Ams_Count__c = (Decimal)agres.get('amcount');
a.RS_Reg_Client_Satisfaction_Ams_Sum__c = (Decimal)agres.get('amsum');
a.RS_Reg_Client_Satisfaction_APAC_Count__c = (Decimal)agres.get('apaccount');
a.RS_Reg_Client_Satisfaction_APAC_Sum__c = (Decimal)agres.get('apacsum');
a.RS_Reg_Client_Satisfaction_EMEA_Count__c = (Decimal)agres.get('emeacount');
a.RS_Reg_Client_Satisfaction_EMEA_Sum__c = (Decimal)agres.get('emeasum');
//accountsToUpdate.add(a);
updateAccMap.put(a.Id, a);
}
}
Can any one let me know, how to do this.......
An aggregate query has too many rows for a direct assignment, use FOR loop**. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a recordset.
For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.
for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) { List<Contact> contactList = acct.Contacts; // Causes an error Integer count = acct.Contacts.size(); // Causes an error }To avoid getting this exception, use a for loop to iterate over the child records, as follows.for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) { Integer count=0; for (Contact c : acct.Contacts) { count++; } }For more information please check below link.- http://www.kineticgrowth.com/system-queryexception-aggregate-query-many-rows-direct-assignment-use-loop/
Hope this helps.Kindly mark this as solved if the information is helpful.
Thanks,
Nagendra