function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
dmchengdmcheng 

Inserting a set into a query string's where clause

I have a query string that I am passing to a Batch Apex method.  I want to include a Set's values in the where clause of the string.  How do I include the set in the string so that it will be parsed correctly?  The example below results in an "unexpected token error" in the debug log when the batch executes.

 

Set<Id> oppIDs = new Set<Id>{id1, id2, id3. etc etc};
CalYearEndBatch cyBatch = new CalYearEndBatch();
cyBatch.query = 'select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in:\'' + oppIDs + '\'';
Id batchprocessID = Database.executeBatch(cyBatch);

 

 

kurtz_wolfgangkurtz_wolfgang

Won't this part : 

'select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in:\'' + oppIDs + '\'';

produce the following string: select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in:'oppIDs''; ?

 

I mean if that's so, oppIDs shouldn't be enclosed in quotes.

 

select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in:\'' + oppIDs + '\'';

 

it should be:

select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in :oppIDs;

 

What does debug log show the statement as?

 

 

BritishBoyinDCBritishBoyinDC

Take a look at this post:

http://developer.force.com/cookbook/recipe/using-batch-apex-to-reassign-account-owners

 

In their example, they pass in a user record in the constructor, but I am pretty sure you could declare a set, pass that in via the constructor, and the execute a query using a bind variable to the set?

kurtz_wolfgangkurtz_wolfgang

 

Try in the following way:
BatchTrial{
global string query;
BatchTrial(Set<Id> oppIDs){
BatchTrial();
this.query = 'select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in ' + :oppIDs;
}
start(Database.BatchableContext BC){
        return Database.getQueryLocator(query);
    }

 

....

}

 

sfdcfoxsfdcfox

Close, but no cigar. It's just like this:

 

 

cyBatch.query = 'SELECT Role, Opportunity.Amount FROM OpportunityContactRole WHERE Opportunity__c IN :oppIDs';

You don't need to do any fancy string concatenation. Just make sure that oppIDs is visible from the start() method when you actually perform your query.

 

dmchengdmcheng

@sfdcfox: ah that's good, so I just need to make oppIDs global then, that's easy.

 

Thanks to everyone else for your replies.

dmchengdmcheng

@sfdcfox: I'm getting a "variable does not exist" error from the batch query even though the variable seems to be visible to the batch start method - a system.debug shows it in the start method.

The reason I'm trying to limit the query is that I'm writing a unit test for the batch apex code, so I only want to get the set of test opps.  In normal operation the query is not limited by a set.

 

Code that generates the set:

 

//Create test records for unit tests.
public class UnitTestData {
	Public static Set<Id> testOppIDs = new Set<Id>();

	public static void CreateTestData() {
	    //code to create opp records.
	    // ...
		insert oppList;
		
		for(Opportunity opp : oppList) {
			testOppIDs.add(opp.Id);
		}
	}
}

 

Test method that calls the class and then calls the batch.  (query string has been simplified)

 

	static testmethod void testCalYearEndBatch() {
		UnitTestData.CreateTestData();
		Test.startTest();
		CalYearEndBatch cyBatch = new CalYearEndBatch();
		cyBatch.query = 'select Role, Opportunity.Amount from OpportunityContactRole where Opportunity__c in :UnitTestData.testOppIDs';
		Id batchprocessID = Database.executeBatch(cyBatch);
		Test.stopTest();
	}

 

 

Batch Apex.  System.debug shows the set has values, but getQueryLocator throws System.Queryexception "variable does not exist"

 

global class CalYearEndBatch implements Database.Batchable<sObject>, Database.Stateful {

    //Query for OpportunityContactRole records with related Opportunity data.
    Public String query;
   
    //Execute the query.
    global database.querylocator start(Database.BatchableContext BC) {
system.debug(UnitTestData.testOppIDs);
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope) {
	}
    global void finish(Database.BatchableContext BC) {
	}
}

 

 

 

 

sfdcfoxsfdcfox

That sounds like it may be a bug related to variable binding in Dynamic Apex (maybe, not able to read static variables?). I would raise a case with Support, just to make sure. For now, I'd suggest you copy the variable reference to a local scope so you can get your test method to work. I believe the examples in the documentation all use this sort of syntax, perhaps hinting that it won't work otherwise.

BritishBoyinDCBritishBoyinDC

So looking at the example I linked to and what  the original post was trying to achieve, this code works for opportunities in my dev environment...

 

First the batch - there are two constructors, one that receives a Set, and one that is blank to execute all records

 

When we call the batch, you can either specify a set, and so limit the scope, or it will execute the 'full scope' SOQL when the Set is null.

 

 

global class CalYearEndBatch implements Database.Batchable<sObject>, Database.Stateful {

    //Query for OpportunityContactRole records with related Opportunity data.
    String query;
    Set<Id> soppids {get; set;}

//Constructor that receives set    
global  CalYearEndBatch (Set<Id> soppids ) {
this.soppids = soppids ;
} 

//empty constructor
global  CalYearEndBatch () {
}  
    //Execute the query. Check if set is null and execute SOQL accordingly
    global database.querylocator start(Database.BatchableContext BC) {
    if (soppids == null ) {
    return Database.getQueryLocator([Select Id, Amount from Opportunity order by closedate desc limit 10 ]);}
    else {
        return Database.getQueryLocator([Select Id, Amount from Opportunity WHERE Id IN :soppids ]);}
        }
    
    global void execute(Database.BatchableContext BC, List<sObject> scope) {
    List<Opportunity> opc = (List<Opportunity>)scope;
    for (Opportunity op: opc) {
//execute any code you want to as normal    
op.Amount = 888;
    }
    update opc;
    }
    
    global void finish(Database.BatchableContext BC) {
    }
}

 

To call batch with a set:

 

Set<Id> testids = new Set<Id> ();

for (opportunity o: [Select Id from Opportunity where closedate = LAST_YEAR]) {
testids.add(o.Id);
}

CalYearEndBatch cyBatch = new CalYearEndBatch(testids);
Id batchprocessID = Database.executeBatch(cyBatch);

 And without:

 

 

CalYearEndBatch cyBatch = new CalYearEndBatch();
Id batchprocessID = Database.executeBatch(cyBatch);

 

 

Hope that helps!

 

 

 

nagalakshminagalakshmi

Hi,

 

did you got the solution for this. I am also having the same issue. If you solve this issue, please help me.

 

Thanks

ramifyramify

Facing a kind of same issue.

icemft1976icemft1976

Same type of  issue here - only the unrecognized variable i have is not a Collection but a locally created SObject. Same result though

 

 


.
.
.
String queryString = 'SELECT Stage__c, Project_Reviewer__c, Research_Track__c, Divisions__c, Forecasted_Delivery_Date__c FROM Projects__c WHERE Stage__c IN :stagesfilter '; if( tempProject.Approval_Date__c != null && tempProject.End_Date__c != null ) { queryString = queryString + ' AND ( ( Forecasted_Delivery_Date__c >= :tempProject.Approval_Date__c) AND ( Forecasted_Delivery_Date__c <= :tempProject.End_Date__c ) ) '; } for(Projects__c p : Database.query(queryString) ) { .... }

 

Variable does not exist: tempProject.Approval_Date__c

 

If i hard code the query, it works fine. And when looking at the visualforce page, it displays the tempProject object fine, even responding to modifying the inputfield values.

 

Only when I submit the page (via a reRender) and use the querystring does it fail.

icemft1976icemft1976

Update: I got around my problem by converting the variable within the Apex code (see below) instead of including its symbol in the querystring.  Since it wasn't recognizing the tempProject i just turned it into a string  - first I had to cast it to a datetime so I could use the Java messageformat notation to get the correct String date rendering (i.e. mimic what it resolves to at runtime).

 

didn't work:

if( tempProject.Approval_Date__c != null &&  tempProject.End_Date__c != null )
{
            queryString = queryString  + ' AND ( ( Forecasted_Delivery_Date__c >= :tempProject.Approval_Date__c ) AND  (  Forecasted_Delivery_Date__c <= :tempProject.End_Date__c ) ) ';    
        } 

 

 

works:

 

if( tempProject.Approval_Date__c != null &&  tempProject.End_Date__c != null )
{
        	datetime startDate = datetime.newInstance(tempProject.Approval_Date__c, time.newInstance(0, 0, 0,0)); 
        	datetime endDate  = datetime.newInstance(tempProject.End_Date__c, time.newInstance(0, 0, 0, 0)); 

            queryString = queryString  + ' AND ( ( Forecasted_Delivery_Date__c >= ' + startDate.format('yyyy-MM-dd') + ' ) AND  (  Forecasted_Delivery_Date__c <= ' + endDate.format('yyyy-MM-dd') + ' ) ) ';    
        } 

 

 

I believe that would also work for a set, and would be easier since you wouldn't have the intermediate steps to recast and format the output....not 100% sure. Did you already solve this another way? If so let me know.

 

It definitely seems like a bug, as its not a documented limitation.