1

I want to make a query that field Source = 'SPS', I have a batch class that is using Iterable, but I always get the error: variable does not exist: sourceVal.

How do I make it work? This is my code:

global class UT_MDM_Email_MonthlyReport_SPS implements Database.Batchable<AggregateResult> {
    // The batch job starts
    global Iterable<AggregateResult> start(Database.BatchableContext bc){
        String sourceVal = 'SPS';
        System.debug('sourceVal :' + sourceVal);
        String query = 'SELECT Owner.UserRole.Name plant FROM Contact WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND Source__c=:sourceVal GROUP BY Owner.UserRole.Name';
        return new AggregateResultIterable(query);
    }

    // The batch job executes and operates on one batch of records
    global void execute(Database.BatchableContext bc, List<sObject> scope){
        for(sObject sObj : scope) {
            AggregateResult ar = (AggregateResult)sObj;
            System.debug('>>>> plant : ' + ar.get('plant'));
        }
    }
}

AggregateResultIterator Class:

global class AggregateResultIterator Implements Iterator<AggregateResult>{

    AggregateResult [] results {get;set;}
    // tracks which result item is returned
    Integer index {get; set;}

    global AggregateResultIterator(String query) {
        index = 0;
        results = Database.query(query);
    }

    global boolean hasNext(){
        return results != null && !results.isEmpty() && index < results.size();
    }

    global AggregateResult next(){
        return results[index++];
    }
}

AggregateResultIterable Class:

global class AggregateResultIterable implements Iterable<AggregateResult>{
    private String query;

    global AggregateResultIterable(String soql){
        query = soql;
    }

    global Iterator<AggregateResult> Iterator(){
        return new AggregateResultIterator(query);
    }
}
2

2 Answers 2

4

Database.query can only see variables in the current scope. When you pass the query string here:

    return new AggregateResultIterable(query);

That variable is no longer in scope. You'll want to read more about scope in the Variables documentation.

Since there's only a maximum of 2,000 rows that you can return in an aggregate result query, you may as well just pass in the query directly:

public class AggregateResultIterator Implements Iterator<AggregateResult> {
    AggregateResult [] results;
    public AggregateResultIterator(AggregateResult[] results) {
        this.results = results.clone();
    }
    public boolean hasNext(){
        return !results.isEmpty();
    }
    public AggregateResult next(){
        return results.remove(0);
    }
}

public class AggregateResultIterable implements Iterable<AggregateResult>{
    AggregateResult[] results;
    public AggregateResultIterable(AggregateResult[] results){
        this.results = results;
    }
    public Iterator<AggregateResult> iterator(){
        return new AggregateResultIterator(results);
    }
}

return new AggregateResultIterable([
  SELECT Owner.UserRole.Name plant FROM Contact WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND Source__c=:sourceVal GROUP BY Owner.UserRole.Name
]);

All that said, there's no reason to do this. A List<AggregateResult> already implements Iterable<AggregateResult>, so you can return it directly:

public AggregateResult[] start(Database.BatchableContext bc){
    String sourceVal = 'SPS';
    System.debug('sourceVal :' + sourceVal);
    return [
      SELECT Owner.UserRole.Name plant 
      FROM Contact 
      WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND Source__c=:sourceVal 
      GROUP BY Owner.UserRole.Name
    ];
}

One final note: you can implement interfaces using public, rather than global, and this is recommended unless you intend for a class outside of your package scope running this batchable class, which is usually never for unpackaged batchable classes, and rarely for ISV packages.

0

I tried this code, and works perfectly

String query = 'SELECT Owner.UserRole.Name plant FROM Contact WHERE Email_Support__c != NULL AND Is_Drop__c = FALSE AND Source__c=\'' + String.escapeSingleQuotes(sourceVal)+'\'GROUP BY Owner.UserRole.Name';
    
3
  • Yes, it works fine, but please, do not close the question, because bind variable as single String should also work just fine, and the question is, why it IS NOT) Commented Dec 20, 2022 at 10:21
  • I found this page that explained it, developer.salesforce.com/forums/… Commented Dec 20, 2022 at 10:25
  • No it doesn't, you can run this code sample and see that it works just fine with bind variables and aggregate result: String sourceVal = 'Outbound'; System.debug('sourceVal: ' + sourceVal); String query = 'SELECT Owner.UserRole.Name plant FROM Lead WHERE LeadSource = :sourceVal AND CreatedDate = TODAY GROUP BY Owner.UserRole.Name'; List<AggregateResult> res = Database.query(query); System.debug(res); . And in your link there is also and answer from vbs that confirms it. It doesn't explain, why it doesn't work in your case. Commented Dec 20, 2022 at 10:29

You must log in to answer this question.

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