2

I would like to use aggregate query in batch job but I'm running into an error:

HAVING expression must be grouped or aggregated

Would also like to understand how can I use count from the query when I'm looping through the record. Here is code for my batch job:

global Database.QueryLocator start(Database.BatchableContext bc) {
    return Database.getQueryLocator([SELECT Account__c, COUNT(Name) FROM MyCustomObject__c GROUP BY Account__c HAVING COUNT(Name) > 0 AND CustomField__c = 'myValue' ORDER BY Account__c]);
}

global void execute(Database.BatchableContext bc, List<SObject> batch) {
    Map<Id, Integer> accCoops = new Map<Id, Integer>();
    for (MyCustomObject__c h : (List<MyCustomObject__c>) batch) {
        accCoops.put(h.Account__c, h.COUNT(Name));
    }
}

When I update query locator query to:

[SELECT Account__c, COUNT(Name) FROM MyCustomObject__c GROUP BY Account__c HAVING COUNT(Name) > 0 ORDER BY Account__c]

I run into following error:

Argument cannot be an aggregate result inline query

Please advice why I'm not able to use HAVING with AND as shown here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_having_considerations.htm

Is there any workaround for this?

Improve this question
2
  • 1
    This question feels like an X-Y problem. What are you really trying to accomplish? Commented Aug 20, 2019 at 14:50
  • HAVING query provided by @Ayub is correct: SELECT Account__c, COUNT(Name) FROM MyCustomObject__c where CustomField__c = 'myValue' GROUP BY Account__c HAVING COUNT(Name) > 0 ORDER BY Account__c however, it is not working with batch apex as it does not allow aggregate queries Commented Aug 20, 2019 at 19:31

2 Answers 2

Reset to default
4

As the doc states, the argument to a Database.getQueryLocator can not be an Aggregate query

You can't use getQueryLocator with any query that contains an aggregate function.

There are two workarounds:

  1. If the number of rows coming back from the aggregate query is small, then just do all the work in a schedulable's execute() method. Assumes you won't blow up any other limits like CPU time (60 sec) or Heap (12MB)
  2. Convert the results of the AggregateQuery into a collection and return an iterable on that collection. The start() method can return either a queryLocator or an Iterable (and in fact, a queryLocator implements Iterable. Assumes that the collection returned from the AggregateQuery doesn't blow up Heap (12 MB)
Improve this answer
1

HAVING COUNT(Name) > 0 AND CustomField__c = 'myValue'

Only aggregated fields can be added in HAVING filter. CustomField__c is not being aggregated in query. In above given query, you are grouping on Account__c and you can add filter in HAVING clause only on this field.

I don't your problem but a valid query similar to your query would be:

SELECT Account__c, COUNT(Name) FROM MyCustomObject__c where CustomField__c = 'myValue' GROUP BY Account__c HAVING COUNT(Name) > 0 ORDER BY Account__c

Also, as a separate note, implement this interface in your batch class: Database.Batchable

Improve this answer
2
  • thanks for the query but I'm still unable to use the query in a batch class with the following error: Compile Error: Argument cannot be an aggregate result inline query Commented Aug 20, 2019 at 16:11
  • implement this interface in your batch class: Database.Batchable Commented Aug 21, 2019 at 5:41

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.