[SalesForce] the coalesce() eqivalent

Much like this post here: SOQL: Avoid count with NULL results return 0 (zero) instead

I have an instance where I am using aggregate functions like COUNT() and MAX() on a collection of Opportunities. These work just fine as long as my WHERE conditions don't filter out all opportunities, unfortunately if there are no rows to aggregate then my query returns 0 rows rather than returning 1 row as expected with the aggregate columns equal to '0'

The answer in the original response spoke of the coalesce() function. From what I can tell that is not a Salesforce method available to me. So is there an equivalent? How do I get my expected row returned with 0 values rather than null?

Here is an example of my SOQL:

SELECT AccountId, COUNT(Id) Invoices, AVG(Days_Past_Due__c) Average, Max(Days_Past_Due__c) Max
FROM Opportunity
WHERE AccountId IN :accountIds
AND IsDeleted = FALSE
AND {more conditions}
GROUP BY AccountId 
ORDER BY AccountId ASC NULLS FIRST

Thanks in advance for any help you can provide here

Best Answer

No. There's no way to get the results you're looking for. SOQL is a very limited query language that isn't as robust as full SQL, although it handles relationships easier than SQL.

Related Topic