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
AlSawtoothAlSawtooth 

Compile Error: unexpected token: ')' at line 8 column 61

I want to populate a field that tells me how many years (consecutive or otherwise) an opportunity has been closed for a specific account. Why can't I do CloseDate.year() inside the aggregate function? (If I try to run count_distinct on a variable, I also get an error message).

Thank you!!
  
trigger YearsOfOpps on Opportunity (after update, after insert) {
List<Account> accs = 
        [SELECT Id, Years_of_Opps__c FROM Account WHERE Id IN
             (SELECT AccountId FROM Opportunity WHERE Id in:Trigger.newMap.keySet())];
    
     for(Account a: accs) {
        AggregateResult[] groupedResult = 
            [SELECT AccountId, COUNT_DISTINCT(CloseDate.year()) Yearr FROM Opportunity 
//the line above is throwing the compile error
                WHERE AccountId = :a.Id
                GROUP BY Account];
         Decimal aggregateYears = (Decimal)groupedResult[0].get('Yearr');
         a.Years_of_Opps__c = aggregateYears;
    }
    
    update accs;
}
}

 
Tyler Mowbrey 1Tyler Mowbrey 1
Hello,

Unfortunately what you are doing is not valid. The only date functions you can use within SOQL are located here: http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_date_functions.htm. At this time CALENDAR_YEAR() is not supported in the mannor of COUNT_DISTINCT().

Consider creating a formula field on the opportunity to convert the close date into a year format and then completing your aggregate query without needing to convert it to a year on the fly.

Tyler