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
Christopher_JChristopher_J 

Help returning AggregateResult in Batch Apex update.

I'm new to Apex and trying to understand how to accomplish a fairly simple task in Batch Apex.  I'm trying to sum a field from a group of records and write it to a field on every account.  My code looks is below.  The bolded part is throwing the error Compile Error: Illegal assignment from LIST<AggregateResult> to Decimal at line 15 column 9. I understand what the error is but I'm not sure how to return the result correctly in this context.  

Thanks for any help you can provide!

global class CalculateACPY implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contract_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
        for(Account a : scope)
        {
       a.Active_Contracts_Per_Year__c = [select sum(amount_per_year__c) from contract where EndDate < TODAY and id =:a.id];
        }
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    {  
    }
}
Jim JamJim Jam
something like ...

AggregateResult[] agList = [SELECT SUM(amount_per_year__c) totalSum FROM contract where Enddate < TODAY and Id=:a.id];
Decimal totalSum = Decimal.valueOf (agList[0].get('totalSum'));
a.Active_Contracts_Per_Year__c = totalSum;

..as an aside, not advisable to have a SOQL within a for loop for bulk processing reasons. 
Christopher_JChristopher_J
Thanks much.  So now I'm getting the following error?  Compile Error: Variable does not exist: Decimal at line 15 column 27

global class CalculateACPY implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contract_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
        for(Account a : scope)
        {
       
       AggregateResult[] agList = [SELECT SUM(amount_per_year__c) totalSum FROM contract where Enddate < TODAY and Id=:a.id];
       Decimal totalSum = Decimal.valueOf (agList[0].get('totalSum'));
       a.Active_Contracts_Per_Year__c = totalSum;
        }
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    { 
    }
}

Also if it's not advisible to do this in a batch.  What is the best way to run a nightly calculation like this on related records and put it in a field on the common record?

Thanks again!
Vinit_KumarVinit_Kumar
Chris,

Try below code :-

global class CalculateACPY implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contract_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
		List<Id> accIds = new List<Id>();
        for(Account a : scope)
        {
			    accIds.add(a.id);
        }
            // query outside for loop
		List<AggregateResult> groupedResults = [SELECT sum(amount_per_year__c)totalsum from contract where EndDate < TODAY and id in:accIds];
		for(Account a : Scope)
		{
			a.Active_Contracts_Per_Year__c = groupedResults[0].get('totalsum');
		}
		
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    {  
    }
}

If this helps,please mark it as best answer to help others :)
Christopher_JChristopher_J
Thanks so much but that returns the following error when I save it Error: Compile Error: Illegal assignment from Object to Decimal at line 20 column 13?
Vinit_KumarVinit_Kumar
Ohk change your code from 

a.Active_Contracts_Per_Year__c = groupedResults[0].get('totalsum');

to

a.Active_Contracts_Per_Year__c = Decimal.Valueof(groupedResults[0].get('totalsum'));
Christopher_JChristopher_J
That gives this error Error Error: Compile Error: Variable does not exist: Decimal at line 20 column 47.  Sorry to be a pain!

global class CalculateACPY implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contract_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
        List<Id> accIds = new List<Id>();
        for(Account a : scope)
        {
                accIds.add(a.id);
        }
            // query outside for loop
        List<AggregateResult> groupedResults = [SELECT sum(amount_per_year__c)totalsum from contract where EndDate < TODAY and id in:accIds];
        for(Account a : Scope)
        {
            a.Active_Contracts_Per_Year__c = Decimal.Valueof(groupedResults[0].get('totalsum'));
        }
       
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    { 
    }
}
Vinit_KumarVinit_Kumar
Ohk try below one :-

global class CalculateACPY implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contract_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
		List<Id> accIds = new List<Id>();
        for(Account a : scope)
        {
			    accIds.add(a.id);
        }
            // query outside for loop
		List<AggregateResult> groupedResults = [SELECT sum(amount_per_year__c)totalsum from contract where EndDate < TODAY and id in:accIds];
		Decimal ttlSum =  (Decimal).groupedResults[0].get('totalsum');
		for(Account a : Scope)
		{
			a.Active_Contracts_Per_Year__c = ttlSum;
		}
		
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    {  
    }
}

If this helps,please mark it as best answer to help other :)
Christopher_JChristopher_J
It seems like that should work but I still get Error: Compile Error: Variable does not exist: Decimal at line 18 column 28

Does it have something to do with the field types by any chance?  They are set to number I believe not currency.  I could change them however.
Vinit_KumarVinit_Kumar
Ohk try this last one 

global class CalculateACPY implements Database.Batchable<sObject>
{
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contract_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
		List<Id> accIds = new List<Id>();
        for(Account a : scope)
        {
			    accIds.add(a.id);
        }
            // query outside for loop
		List<AggregateResult> groupedResults = [SELECT sum(amount_per_year__c)totalsum from contract where EndDate < TODAY and id in:accIds];
		Double ttlSum =  (Double).groupedResults[0].get('totalsum');
		for(Account a : Scope)
		{
			a.Active_Contracts_Per_Year__c = ttlSum;
		}
		
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    {  
    }
}

else let me know datatype of fields Active_Contracts_Per_Year__c and amount_per_year__c !!
Christopher_JChristopher_J
It gives the same error but this time that Double doesn't exist.

The fields are: 
Active_Contracts_Per_Year__c Number(18, 0)
Amount_per_year__c Formula (Number)

I could change these to currency if that would help.  Is it that the field is a formula?  I suppose I could overcome that with workflow field update if I needed to.

Thanks again!
Vinit_KumarVinit_Kumar
The way I would approach is by setting the debug log and see whats coming from SOQL and whats getting assigned to variable.
Christopher_JChristopher_J
I'm not totally sure I know how to do that.  I know that the SOQL query works as expected and returns the correct number.  I tried not using a formula field and changing all of the fields to type currency but I'm still getting the same error.  
Jim JamJim Jam
I guess it's possible if the sum returns null you'll hit this error. Maybe just declare the ttlsum variable as .. object ttlsum and see if that compiles without error, but anyway I agree you need to debug the result to see what is being returned.
Christopher_JChristopher_J
I"m getting the error just trying to save the class not executing it.
Jim JamJim Jam
Just noticed active_contracts_per_year__c doesn't have any precison declared, perhaps change it to Number(18, 2) to there can be something after the decimal point and not just a whole integer. Also, what happens if you don't use a variable at all and set the active_contracts_per_year__c directly, ie.

a.active_contracts_per_year__c = Decimal.valueOf(groupedresults[0].get('totalsum'));
Christopher_JChristopher_J
Thanks for the suggestions.  I tried both of those things and neirther worked.  If I use the line above it returns the error that the variable Decimal doesn't exist.  I'm sort of lost at this point!
Jim JamJim Jam
OK, a little confused by your code now. Not sure how it looks now, but when I look at your first post, the query in the start method doesn't actually select the field you are later trying to update in the execute method. In the start method ensure the query select list contains Active_contracts_per_year__c.
Vinit_KumarVinit_Kumar
Your query in start method is querying Active_contract_per_year__c and you are trying to update Active_contracts_per_year__c.So,there is a difference here.

Can you verify that ??
Christopher_JChristopher_J
Thanks for the pointers.  I've gotten a little further but it's still not working.  I created a new field and made sure all of the fields are set as Number fields with no decimal places.  The only way I was able to save the class was to replace decimal. with integer (bolded below).  I sorted out how to schedule the job and it runs through all of the batches without error but the field isn't getting updated.  I know the query works as long as the id number is getting passed to it.  Any idea as to what I'm doing wrong?

global class CalculateCPY implements Database.Batchable<sObject>
{

    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'Select Id, Name, Active_Contracts_Per_Year_Amount__c from Account';
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Account> scope)
    {
        for(Account a : scope)
        {
      
       AggregateResult[] agList = [SELECT SUM(amount_per_year__c) totalSum FROM contract where Enddate > TODAY and Id=:a.id];
      
       a.Active_Contracts_Per_Year_Amount__c = integer.valueOf (agList[0].get('totalSum'));
        }
        update scope;
    }
    global void finish(Database.BatchableContext BC)
    {
    }
}
Ariel GorfinkelAriel Gorfinkel
Apparently, you cannot convert directly from AggregateResult to decimal.
I had a similar issue and found that converting to String and then to Decimal worked.
Try: 
Decimal totalSum = Decimal.valueOf (agList[0].get('totalSum') + '');
Not sure how much this is still relevant to Christopher, but for future references... ;)