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?

2
  • 1
    This question feels like an X-Y problem. What are you really trying to accomplish?
    – sfdcfox
    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
    – user569925
    Aug 20, 2019 at 19:31

2 Answers 2

3

As the [doc states(https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_database.htm?search_text=getquerylocator), 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)
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

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
    – user569925
    Aug 20, 2019 at 16:11
  • implement this interface in your batch class: Database.Batchable
    – Ayub
    Aug 21, 2019 at 5:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .