0

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?

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.