+ Start a Discussion
Frances WottonFrances Wotton 

Invalid field expr0 for AggregateResult

The following part of a trigger is giving error " execution of AfterInsert caused by: System.SObjectException: Invalid field expr0 for AggregateResult ()".  Do I need a way of passing the decimal value into the expr0?

for(AggregateResult q : [select Project__c, sum(Amount) AmountSum1, sum(npe01__Amount_Outstanding__c)  AmountSum2 from Opportunity 
where (StageName = 'Complete - Won' or StageName = 'Sign Contract' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c]){

    decimal value=((decimal)q.get('AmountSum1')-(decimal)q.get('AmountSum2'));
      ProjectMap.put((Id)q.get('Project__c'),(Double)q.get('expr0'));
Best Answer chosen by Frances Wotton
Hemant_SoniHemant_Soni
Hi,
Then Please try like below code 
for(AggregateResult q : [select Project__c, sum(Amount) AmountSum1, sum(npe01__Amount_Outstanding__c)  AmountSum2 from Opportunity 
where (StageName = 'Complete - Won' or StageName = 'Sign Contract' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c]){
      ProjectMap.put((Id)q.get('Project__c'),((decimal)q.get('AmountSum1')-(decimal)q.get('AmountSum2')));
Thanks​

All Answers

Hemant_SoniHemant_Soni
Hi Frances,
Problem is 'expr0' is not referencing any field in aggregate result that why you getting error. You have already declaired all aggregated fields with the aggregated name.
So please hightlight what exactly you are trying to put in "ProjectMap".

Thanks
Raj VakatiRaj Vakati
Try this .. you need to use alias name insted of the exp0
 
for(AggregateResult q : [select Project__c, sum(Amount) AmountSum1, sum(npe01__Amount_Outstanding__c)  AmountSum2 from Opportunity 
where (StageName = 'Complete - Won' or StageName = 'Sign Contract' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c]){

    decimal value=((decimal)q.get('AmountSum1')-(decimal)q.get('AmountSum2'));
      ProjectMap.put((Id)q.get('Project__c'),(Double)q.get('AmountSum1'));

 
Frances WottonFrances Wotton
Hi Hermant

Looking at other similar triggers ProjectMap is what populates the field, and the value I want to output to the field is ((decimal)q.get('AmountSum1')-(decimal)q.get('AmountSum2')), so I think it's this, or an alias for this, that needs passing into ProjectMap

Many thanks
Frances
Hemant_SoniHemant_Soni
Hi,
Then Please try like below code 
for(AggregateResult q : [select Project__c, sum(Amount) AmountSum1, sum(npe01__Amount_Outstanding__c)  AmountSum2 from Opportunity 
where (StageName = 'Complete - Won' or StageName = 'Sign Contract' or StageName = 'Funding approved') and Project__c != null and Project__c IN :ProjectIds group by Project__c]){
      ProjectMap.put((Id)q.get('Project__c'),((decimal)q.get('AmountSum1')-(decimal)q.get('AmountSum2')));
Thanks​
This was selected as the best answer