So, I'm working on a batch apex which is supposed to read from a Custom_Object_A__c according to a given criteria, and update another Custom_Object_B__c.
The deal is that a long result set is returned when I query Custom_Object_A__c and I'm mapping the query results from Custom_Object_A__c into a List <String> which I use to query Custom_Object_B__c and instantiate it, so I can update the records that I need.
When I first run my batch I actually noticed a strange error message on dev console:
19:59:08:034 EXCEPTION_THROWN [69]|System.QueryException: expecting a colon, found '.'
So I decided to put a debug on my code to check the soql statement that is generated to query Custom_Object_B__c and I realized that 3 dots (...) have been added to the end of the dinamically generated list of Ids, and that might be the cause for the error I mentioned above. Plus, as a matter of trial to figure out the cause for the error, I have put a LIMIT clause on the first query which generates the list FROM Custom_Object_A__c. I started limiting for the first 20 records and still got the error. Then I changed it to limit by 11 records and so the code ran successfully:
USER_DEBUG [67]|DEBUG|select Code__c, Record_Type__c from Custom_Object_B__c where Record_Type__c = 'Format' and StatusProcess__c = 'Processed'and Code__c IN ('CAE3PDU49372PRG_BTCH2', 'TEST_L0_81', 'ROS-0004', 'TestStagingNA1-5', 'TestStagingNA3-3', 'TEST_L0_90', 'CAE3PDU49372PRG15TEST', 'FAUST_12345', 'CAE3', 'CAE4', ...)
The character length for the dinamically generated query is 304 so this should not be the problem as salesforce states that character length limit for SOQL statemets is up to 100,000. Still according to them, I may use 4,000 characters on SOQL WHERE clause.
Can anybody shed some light on it? Am I violating any other SOQL limit that could led to this behaviour?
Can anybody help me with this code? I'm trying to generate a list of string values to use as input parameter for another query but when I run it, I keep getting the error System.QueryException: IN operator must be used with an iterable expression. Refer to the code below:
global class BatchUpdateProductsStagingStatus implements Database.Batchable<sObject>, Database.Stateful {
global Integer varCountParent = 0;
global Integer varCount = 1;
global String listParam = '';
global String listParamfinal ;
global String query;
global String queryCondition;
global Database.QueryLocator start(Database.BatchableContext scope) {
List<AggregateResult> ListCatalogItens = new List<AggregateResult>();
List<AggregateResult> referenceList = [select Parent_Catalog_Item__r.External_Id__c a from Catalog_Item__c where RecordType.Name = 'Format' and Parent_Catalog_Item__r.External_Id__c != null group by Parent_Catalog_Item__r.Id ,Parent_Catalog_Item__r.External_Id__c , Reference_Catalog__c HAVING Count(Id) >1 ORDER BY Parent_Catalog_Item__r.External_Id__c];
for (AggregateResult c: referenceList) {
listParam = string.valueOf(c).removeStart('AggregateResult:').removeStart('{a=').removeEnd('}');
if ( referenceList.size() > 1){
if (varCount < referenceList.size()){
listParam = '\''+ String.escapeSingleQuotes(listParam) + '\', ';
listParamfinal = listParamfinal + listParam;
}else if (varCount == referenceList.size()){
listParam = '\''+ String.escapeSingleQuotes(listParam) + '\' ';
listParamfinal = listParamfinal + listParam;
}
}else{
listParam = '\''+ String.escapeSingleQuotes(listParam) + '\'';
listParamfinal = listParamfinal + listParam;
}
varCount = varCount + 1;
System.debug(referenceList.size());
}
if(varCount > 1){
listParamfinal = '('+listParamfinal+')';
queryCondition = 'and Code__c IN: listParamfinal';
}else queryCondition = 'and Code__c =: listParamfinal';
System.debug('Qtde de registros: '+varCount);
System.debug(listParamfinal );
System.debug(queryCondition);
query = 'select Id, StatusMessage__c from Products__c where StatusProcess__c = \'Processed\' '+queryCondition;
System.debug(query);
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext scope, List<Products__c> lstProducts ){
List<Products__c> Prod2Update = new List<Products__c>();
for (Products__c varProd2Update : lstProducts) {
varProd2Update.StatusMessage__c = 'The staging record was created on Catalog Item as a Stem but it does not have any associated Leaf record with attribute Reference Catalog = TRUE';
Prod2Update.add(varProd2Update);
varCountParent = varCountParent + 1;
}
update Prod2Update;
}
global void finish(Database.BatchableContext scope ){
System.debug(varCountParent + ' records processed.');
}
}
So, I'm working on a batch apex which is supposed to read from a Custom_Object_A__c according to a given criteria, and update another Custom_Object_B__c.
The deal is that a long result set is returned when I query Custom_Object_A__c and I'm mapping the query results from Custom_Object_A__c into a List <String> which I use to query Custom_Object_B__c and instantiate it, so I can update the records that I need.
When I first run my batch I actually noticed a strange error message on dev console:
19:59:08:034 EXCEPTION_THROWN [69]|System.QueryException: expecting a colon, found '.'
So I decided to put a debug on my code to check the soql statement that is generated to query Custom_Object_B__c and I realized that 3 dots (...) have been added to the end of the dinamically generated list of Ids, and that might be the cause for the error I mentioned above. Plus, as a matter of trial to figure out the cause for the error, I have put a LIMIT clause on the first query which generates the list FROM Custom_Object_A__c. I started limiting for the first 20 records and still got the error. Then I changed it to limit by 11 records and so the code ran successfully:
USER_DEBUG [67]|DEBUG|select Code__c, Record_Type__c from Custom_Object_B__c where Record_Type__c = 'Format' and StatusProcess__c = 'Processed'and Code__c IN ('CAE3PDU49372PRG_BTCH2', 'TEST_L0_81', 'ROS-0004', 'TestStagingNA1-5', 'TestStagingNA3-3', 'TEST_L0_90', 'CAE3PDU49372PRG15TEST', 'FAUST_12345', 'CAE3', 'CAE4', ...)
The character length for the dinamically generated query is 304 so this should not be the problem as salesforce states that character length limit for SOQL statemets is up to 100,000. Still according to them, I may use 4,000 characters on SOQL WHERE clause.
Can anybody shed some light on it? Am I violating any other SOQL limit that could led to this behaviour?
Can anybody help me with this code? I'm trying to generate a list of string values to use as input parameter for another query but when I run it, I keep getting the error System.QueryException: IN operator must be used with an iterable expression. Refer to the code below:
global class BatchUpdateProductsStagingStatus implements Database.Batchable<sObject>, Database.Stateful {
global Integer varCountParent = 0;
global Integer varCount = 1;
global String listParam = '';
global String listParamfinal ;
global String query;
global String queryCondition;
global Database.QueryLocator start(Database.BatchableContext scope) {
List<AggregateResult> ListCatalogItens = new List<AggregateResult>();
List<AggregateResult> referenceList = [select Parent_Catalog_Item__r.External_Id__c a from Catalog_Item__c where RecordType.Name = 'Format' and Parent_Catalog_Item__r.External_Id__c != null group by Parent_Catalog_Item__r.Id ,Parent_Catalog_Item__r.External_Id__c , Reference_Catalog__c HAVING Count(Id) >1 ORDER BY Parent_Catalog_Item__r.External_Id__c];
for (AggregateResult c: referenceList) {
listParam = string.valueOf(c).removeStart('AggregateResult:').removeStart('{a=').removeEnd('}');
if ( referenceList.size() > 1){
if (varCount < referenceList.size()){
listParam = '\''+ String.escapeSingleQuotes(listParam) + '\', ';
listParamfinal = listParamfinal + listParam;
}else if (varCount == referenceList.size()){
listParam = '\''+ String.escapeSingleQuotes(listParam) + '\' ';
listParamfinal = listParamfinal + listParam;
}
}else{
listParam = '\''+ String.escapeSingleQuotes(listParam) + '\'';
listParamfinal = listParamfinal + listParam;
}
varCount = varCount + 1;
System.debug(referenceList.size());
}
if(varCount > 1){
listParamfinal = '('+listParamfinal+')';
queryCondition = 'and Code__c IN: listParamfinal';
}else queryCondition = 'and Code__c =: listParamfinal';
System.debug('Qtde de registros: '+varCount);
System.debug(listParamfinal );
System.debug(queryCondition);
query = 'select Id, StatusMessage__c from Products__c where StatusProcess__c = \'Processed\' '+queryCondition;
System.debug(query);
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext scope, List<Products__c> lstProducts ){
List<Products__c> Prod2Update = new List<Products__c>();
for (Products__c varProd2Update : lstProducts) {
varProd2Update.StatusMessage__c = 'The staging record was created on Catalog Item as a Stem but it does not have any associated Leaf record with attribute Reference Catalog = TRUE';
Prod2Update.add(varProd2Update);
varCountParent = varCountParent + 1;
}
update Prod2Update;
}
global void finish(Database.BatchableContext scope ){
System.debug(varCountParent + ' records processed.');
}
}
