function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Keith Stephens 18Keith Stephens 18 

Aggregate query does not support queryMore(), use LIMIT

Hello All,
I hope someone can help me or point me in the right direction to solve this error I am getting.
This error is occuring on this line: 
AggregateResult[] AARsAB  = [SELECT Count(id) maxRates, Main_Center__c,CPT_Code__c
			FROM Account_Procedure_Rate__c where Main_Center__c IN: centerMap.keySet()  group by CPT_Code__c, Main_Center__c];

I think it is due to it's bringing back to many rows.
Would this line of code resolve such issue? 
List<SObject> AARs= [SELECT Count(id) maxRates, Main_Center__c,CPT_Code__c
		FROM Account_Procedure_Rate__c  group by CPT_Code__c, Main_Center__c];

Sticking it into a list of sobject?  If so what is the difference between the two?
This only occures in our production environment since we do not keep our dev and qa upto date with this many records ect..
Thanks,
Keith.
Prashant Pandey07Prashant Pandey07
Keith,

A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop and that is happening in your case.

You need to limit the number of rows that the AggregateResult retrieved or you may try to process all the records, use asynchronous calls/Batch  so that you can process all the record in small batch.

Again List may not help if you are looping through the list and you may hit the exception "Aggregate query has too many rows for direct assignment, use FOR loop" so I would suggest go for Batch job.


--
Thanks,
Prashant


 
Keith Stephens 18Keith Stephens 18
Hello Prashant,
Thanks for your reply, but how do I do this?
Can anyone help in re-writting this trigger the correct way?
  
trigger ProcedureRateUpdates on Procedure__c (before update, before insert) {
    try{
    // if triggers have been globally disabled via TriggerCustomSettings setting, then exit
        if (TriggerSettings.areTriggersDisabled()) return;
        
        List<AggregateResult> centerRateCount = null;
        List<AggregateResult> acctRateCount = null;
        Integer maxCtrRates = 0;
        Integer maxAcctRates = 0;
        Id centerId = null;
        Boolean isManualProcPricing = false;
        Set<Id> centerIds = new Set<Id>();
        List<Center__c> lstCenter = new List<Center__c>();
        Center__c centerId2 = null;
        Integer centerRateCount2 = 0;
        Integer acctRateCount2 = 0;
        Id mcId2 = null;
    
        
        List<String> lstCenter2 = new List<String>();
        
        for(Procedure__c proc : Trigger.New)
            {        
                centerIds.add(proc.Center__c);   
                
            }  

        Map<string, Center__c> centerMap = new map<string, Center__c> ( [SELECT Main_Center__c FROM Center__c WHERE Id IN: centerIds] );  
        
            //Map<string, Main_Center__c> isManualProcPricingMap = new map<string, Main_Center__c>([SELECT Manual_Procedure_Pricing__c
            //FROM Main_Center__c where Id in: centerIds] );

        Map<string, Main_Center__c> isManualProcPricingMap = new map<string, Main_Center__c>([select Manual_Procedure_Pricing__c 
        FROM Main_Center__c c where c.Id in (SELECT Main_Center__c FROM Center__c WHERE Id IN: centerIds)] );
        
        //Gets a count of the rates and adds them to a map using Key Value pairs/
        //Key (CPT Code, Center) Vaue (maxRates)
        AggregateResult[] ARs  = [SELECT Count(id) maxRates, Center__c,CPT_Code__c
            FROM Center_Procedure_Rate__c group by CPT_Code__c, Center__c];
            
        map<string,integer> cprMap = new map<string,integer>();

        for(AggregateResult ar : ARs){
            cprMap.put((string) ar.get('CPT_Code__c') + ',' + (string) ar.get('Center__c'), (integer) ar.get('maxRates'));
        }

        //Gets a count of the Account_Procedure_Rate and adds them to a map using Key Value pairs/
        //Key (CPT Code, Center) Vaue (maxRates)
        AggregateResult[] AARs  = [SELECT Count(id) maxRates, Main_Center__c,CPT_Code__c
            FROM Account_Procedure_Rate__c  group by CPT_Code__c, Main_Center__c];
            
        map<string,integer> aprMap = new map<string,integer>();

        for(AggregateResult aar : AARs){
            aprMap.put((string) aar.get('CPT_Code__c') + ',' + (string) aar.get('Main_Center__c'), (integer) aar.get('maxRates'));
        }

    //List<string,string> paymap = new List<string,string>([SELECT Amount_To_Pay__c, Procedure_Cost__c FROM Account_Procedure_Rate__c
    //group by CPT_Code__c, Main_Center__c]);
        

    //Account_Procedure_Rate__c rate = [SELECT Amount_To_Pay__c, Procedure_Cost__c FROM Account_Procedure_Rate__c 
    //                                                           WHERE  CPT_Code__c = :proc.CPT_Code__c AND Main_Center__c = :centerId];

    //Latest for Account_Procedure_rate__c
    //Map<string, Account_Procedure_Rate__c> arprMap = new map<string, Account_Procedure_Rate__c>([SELECT Amount_To_Pay__c,CPT_Code__c,
    //Main_Center__c,Procedure_Cost__c FROM Account_Procedure_Rate__c]);

        List<Account_Procedure_Rate__c> ATARs  = [SELECT Amount_To_Pay__c,CPT_Code__c,
            Main_Center__c,Procedure_Cost__c FROM Account_Procedure_Rate__c];
            
        map<string,string> aTprMap = new map<string,string>();

        for(Account_Procedure_Rate__c aTar : ATARs){
            aTprMap.put((string) aTar.get('CPT_Code__c') + ',' + (string) aTar.get('Main_Center__c'), (Decimal) aTar.get('Amount_To_Pay__c')
            +','+ (Decimal)aTar.get('Procedure_Cost__c'));
        }

        //system.debug('aTprMap keys= ' + aTprMap.keySet());
        //system.debug('aTprMap values= ' + aTprMap.values());


        List<Center_Procedure_Rate__c> ACARs  = [SELECT CPT_Code__c,Center__c,Amount_To_Pay__c, Procedure_Cost__c FROM Center_Procedure_Rate__c];
            
        map<string,string> aCprMap = new map<string,string>();

        for(Center_Procedure_Rate__c aCar : ACARs){
            aCprMap.put((string) aCar.get('CPT_Code__c') + ',' + (string) aCar.get('Center__c'), (Decimal) aCar.get('Amount_To_Pay__c')
            +','+ (Decimal)aCar.get('Procedure_Cost__c'));
        }

       // system.debug('aCprMap keys= ' + aCprMap.keySet());
       // system.debug('aCprMap values= ' + aCprMap.values());


        //Center_Procedure_Rate__c rate = [SELECT Amount_To_Pay__c, Procedure_Cost__c FROM Center_Procedure_Rate__c 
        //                                                       WHERE  CPT_Code__c = :proc.CPT_Code__c AND Center__c = :proc.Center__c];

        ////System.debug('get= ' + arprMap.get('a1wg0000001Md4VAAS'));
        ////System.debug('get2= ' + arprMap.get('a1wg0000001NqgDAAS'));
                
        //system.debug('countMap values= ' + cprMap.values());
        //System.debug('=== all keys in the map: ' + cprMap.keySet());  
        //system.debug('GetKey>>> ' + cprMap.get('a0Ng0000005iGkXEAU,a0Qg000000AEbmjEAD'));
        //system.debug('size of center map ' + centerMap.size());
        //System.debug('Size of pricingMap ' + isManualProcPricingMap.size());

                        
        for (Procedure__c proc: trigger.new) {
            if (proc.Override_Procedure_Rate__c == false) {
                // get the Main Center ID for the current procedure
                try {               
                    //system.debug('centerId2');                                
                    centerId2 = centerMap.get(proc.Center__c);   
                    //System.debug(centerId2.Main_Center__c);   
                    //system.debug('CID= ' + centerId2);
                    centerId =  (Id)centerId2.Main_Center__c;
                    //System.debug('new center is= ' + centerId);
                    mcId2 = centerId2.Main_Center__c;
                    //system.debug('MCID2= ' + mcId2);
                    //system.debug('Map.Get= ' + isManualProcPricingMap.get(mcId2));

                } catch (Exception e) {
                    proc.addError('Unable to determine Main Center. Ensure that this procedure has a Center selected.');
                    continue;
                }
                // determine if Manual Procedure Pricing checkbox is checked
                //isManualProcPricing = [SELECT Manual_Procedure_Pricing__c FROM Main_Center__c WHERE Id = :centerId][0].Manual_Procedure_Pricing__c;
                isManualProcPricing = isManualProcPricingMap.get(mcId2).Manual_Procedure_Pricing__c;
                //System.debug('Bool for pricing= ' + isManualProcPricing);
                
                if (isManualProcPricing == false) {
                    // only process the costs if entering a new procedure or updating an existing one that has a blank amount and cost
                    // or updating an existing one only if the Procedure Status is not 'OK to Pay' or 'Paid')
                    //system.debug('before if statment>>>>>>>>>');
                    //system.debug('atp= ' + proc.Amount_To_Pay__c);
                    //System.debug('pcost= ' + proc.Procedure_Cost__c);
                    //System.debug('status= ' + proc.Procedure_Status__c);

                    if (Trigger.isInsert || (Trigger.isUpdate && proc.Amount_To_Pay__c == NULL && proc.Procedure_Cost__c == NULL) || 
                        (Trigger.isUpdate && (proc.Procedure_Status__c != 'OK to Pay' && proc.Procedure_Status__c != 'Paid' &&
                        (proc.Amount_To_Pay__c != NULL && proc.Procedure_Cost__c != NULL)))) {
                        // get count of Center Procedure Rates that match current procedure
                        //centerRateCount = 
                        //  [SELECT COUNT(Id) maxRates FROM Center_Procedure_Rate__c 
                        //   WHERE CPT_Code__c = :proc.CPT_Code__c AND Center__c = :proc.Center__c];

                            centerRateCount2 = cprMap.get(proc.CPT_Code__c + ',' + proc.Center__c)== null ? 0 : cprMap.get(proc.CPT_Code__c + ',' + proc.Center__c);                    

                            
                            //System.debug(proc.CPT_Code__c + ' ' + proc.Center__c);
                            //system.debug('centerRateCount2= ' + centerRateCount2);
                            
                        //maxCtrRates = centerRateCount[0].get('maxRates') == null ? 0 : (Integer)centerRateCount[0].get('maxRates');
                        maxCtrRates = centerRateCount2;
                        //system.debug('maxCtrRate= ' + maxCtrRates);
                        
                        //maxCtrRates = cprCnt.get(proc.Center__c, proc.CPT_Code__c);
                            
                        if (maxCtrRates == 0) {
                            //System.debug('proc.Center__c = ' + proc.Center__c);
                            // no rates found in Center Procedure Rate object, so look at Account Procedure Rate object for rate
                            //acctRateCount = [SELECT COUNT(Id) maxRates FROM Account_Procedure_Rate__c 
                            //               WHERE CPT_Code__c = :proc.CPT_Code__c AND Main_Center__c = :centerId];


                            //maxAcctRates = acctRateCount[0].get('maxRates') == null ? 0 : (Integer)acctRateCount[0].get('maxRates');

                            maxAcctRates = aprMap.get(proc.CPT_Code__c + ',' + centerId2.Main_Center__c)== null ? 0 : aprMap.get(proc.CPT_Code__c + ',' + centerId2.Main_Center__c);                    
                            //system.debug('maxAcctRates= ' + maxAcctRates);

                            
                            if (maxAcctRates == 0) { 
                                proc.addError('No rates found for this procedure.');
                            } else if (maxAcctRates == 1) {
                                //Account_Procedure_Rate__c mcenter = arprMap.get(proc.CPT_Code__c + ',' + centerId2.Main_Center__c);
                                
                                //System.debug('ctp code= ' + proc.CPT_Code__c);
                                //System.debug('main c= ' + centerId2.Main_Center__c);
                                //System.debug('center= ' + proc.Center__c);
                                //system.debug('atp= ' + proc.Amount_To_Pay__c);
                                //System.debug('pcost= ' + proc.Procedure_Cost__c);

                                String rate2 = aTprMap.get(proc.CPT_Code__c + ',' + centerId2.Main_Center__c);                  
                            
                                //System.debug('R2= ' +rate2);
                                String s = String.valueOf(rate2);

                                String[] frontBack = s.split(',');
                                //System.debug('FB:' + frontBack);
                                //System.debug('FR0= '+ frontBack[0]);
                                //System.debug('FR1= '+frontBack[1]);
                                proc.Amount_To_Pay__c = decimal.valueOf(frontBack[0]);
                                proc.Procedure_Cost__c = decimal.valueOf(frontBack[1]);

                            //  Account_Procedure_Rate__c rate = [SELECT Amount_To_Pay__c, Procedure_Cost__c FROM Account_Procedure_Rate__c 
                            //                                   WHERE  CPT_Code__c = :proc.CPT_Code__c AND Main_Center__c = :centerId];
                            //  System.debug(rate.Procedure_Cost__c);
                            //  system.debug(rate.Amount_To_Pay__c);

                            //  proc.Amount_To_Pay__c = rate.Amount_To_Pay__c;
                            //  proc.Procedure_Cost__c = rate.Procedure_Cost__c;
                            } else {
                                proc.addError('More than one procedure rate for the MAIN CENTER was found.');
                            }
                        } else if (maxCtrRates == 1) {
                                String rate2 = aCprMap.get(proc.CPT_Code__c + ',' + centerId2.Main_Center__c);                  
                            
                                //System.debug('R2= ' +rate2);
                                String s = String.valueOf(rate2);

                                String[] frontBack = s.split(',');
                                //System.debug('FB:' + frontBack);
                                //System.debug('FR0= '+ frontBack[0]);
                                //System.debug('FR1= '+frontBack[1]);
                                proc.Amount_To_Pay__c = decimal.valueOf(frontBack[0]);
                                proc.Procedure_Cost__c = decimal.valueOf(frontBack[1]);

                            //Center_Procedure_Rate__c rate = [SELECT Amount_To_Pay__c, Procedure_Cost__c FROM Center_Procedure_Rate__c 
                            //                               WHERE  CPT_Code__c = :proc.CPT_Code__c AND Center__c = :proc.Center__c];
                            //proc.Amount_To_Pay__c = rate.Amount_To_Pay__c;
                            //proc.Procedure_Cost__c = rate.Procedure_Cost__c;
                        } else {
                            proc.addError('More than one procedure rate for the CENTER was found.');
                        }
                    }
                }
            }
        } 
    }catch(Exception e){
       //Package suspended, uninstalled or expired, exit gracefully.
       System.debug('Error ProcedureRateUpdatees Trigger');
       system.debug(e.getMessage());
       
    }
}



Thanks,
K.
Prashant Pandey07Prashant Pandey07
I don't see this query in the given code 
AggregateResult[] AARsAB = [SELECT Count(id) maxRates, Main_Center__c,CPT_Code__c
            FROM Account_Procedure_Rate__c where Main_Center__c IN: centerMap.keySet()  group by CPT_Code__c, Main_Center__c];

Well in order to achieve this, You need to implement an Iterable batch class and call this class into your trigger. Check this url for sample batch class.

https://help.salesforce.com/articleView?id=000192834&language=en_US&type=1
  
trigger ProcedureRateUpdates on Procedure__c (before update, before insert) {

//add some coditions or recursive check to execute the class 
//RunQuery  is the batch class name
RunQuery rqs = new RunQuery ();
Database.executeBacth(rqs);

}




 
Keith Stephens 18Keith Stephens 18
Line 38.
 //Gets a count of the rates and adds them to a map using Key Value pairs/
037        //Key (CPT Code, Center) Vaue (maxRates)
038        AggregateResult[] ARs = [SELECT Count(id) maxRates, Center__c,CPT_Code__c
039            FROM Center_Procedure_Rate__c group by CPT_Code__c, Center__c];

Thanks,
Keith.