+ Start a Discussion
ppiyushppiyush 

SQL query not working

I have been trying to write a particular query in apex which is failing (during a batch apex). here it is:

 

 

query = 'SELECT id, Umantis_Account_ID__c, Ultimate_Parent_Account__c, Ultimate_Parent_Id__c, Related_Contact__c FROM Opportunity WHERE (Project_Code__c !=\'Budget\') AND (Umantis_NUM_ID__c LIKE Primary_Contacts_Umantis_ID__c)

 

 

This just doesnt work, and I get an error message in my Apex Jobs Log that looks like this:

 

 

First error: unexpected token: 'Primary_Contacts_Umantis_ID__c'

 

If I remove the second condition in the WHERE statement, everything works fine... which is also strange. For the LIKE operator, do both columns need to be text columns?

 

Best,

Pranav

 

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

I'd suggest you simply create a new list of opportunities in your execute method, and only add the opportunity to that list if Primary_Contacts_Umantis_ID__c != Umantis_NUM_ID__c.  Then just update using the new list rather than the original scope.

All Answers

bob_buzzardbob_buzzard

According to the docs:

 

--- snip ---

 

LIKE -  Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value

 

--- snip ---

 

Thus it looks like you can only compare with string literals, not with the value of a field.  I guess this makes sense as LIKE is allowing wildcards etc, and it can't apply those to a value specific to each row it is considering.

ppiyushppiyush

Hi Bob,

 

I thought so, and replaced LIKE with just a simple equals "=", but that didnt solve the problem - i am still getting the same error....! 

 

'SELECT id, Umantis_Account_ID__c, Ultimate_Parent_Account__c, Ultimate_Parent_Id__c, Related_Contact__c FROM Opportunity WHERE (Project_Code__c !=\'Budget\') AND (Umantis_NUM_ID__c = Primary_Contacts_Umantis_ID__c) '

 

Any ideas?

Imran MohammedImran Mohammed

Looks like you are missing the colon(:) in the query for Primary_Contacts_Umantis_ID__c.

query = 'SELECT id, Umantis_Account_ID__c, Ultimate_Parent_Account__c, Ultimate_Parent_Id__c, Related_Contact__c FROM Opportunity WHERE (Project_Code__c !=\'Budget\') AND (Umantis_NUM_ID__c LIKE :Primary_Contacts_Umantis_ID__c)

bob_buzzardbob_buzzard

Again from the docs, regarding field comparison syntax:

 

fieldName  comparisonOperator  value

 

You must supply a native value—other field names or calculations are not permitted. 

 

Imran MohammedImran Mohammed

Add just colon(:)

Use this query

'SELECT id, Umantis_Account_ID__c, Ultimate_Parent_Account__c, Ultimate_Parent_Id__c, Related_Contact__c FROM Opportunity WHERE (Project_Code__c !=\'Budget\') AND (Umantis_NUM_ID__c = :Primary_Contacts_Umantis_ID__c) '

 

Let us know if it worked

ppiyushppiyush

Imran, this doesnt work...

bob_buzzardbob_buzzard

That's because the ':<name>' syntax is used to bind an Apex variable into the SOQL.

 

 Correct me if I'm wrong, but is what you are trying to do here is compare the value of one field with the value of another field?

ppiyushppiyush

Yes Bob,

 

I am trying to compare the value of one field with the value of another field..

 

And this is being done in a Batch Apex query - the results of which I would do an update on with certain criteria...!

 

thanks for your help...

Imran MohammedImran Mohammed

What error message are you getting now?

Can you tell what is the type of Umantis_NUM_ID__c field and Primary_Contacts_Umantis_ID__c

ppiyushppiyush

My code looks like this for now - and it works fine....

 

What I would like to do is:

 

EITHER restrict the query to items where Primary_Contacts_Umantis_ID__c and Umantis_NUM_ID__c are equal (both a formula fields of type TEXT.

 

OR remove any items from the variable SCOPE where Primary_Contacts_Umantis_ID__c and Umantis_NUM_ID__c are NOT equal...

 

hope this is clear?

 

 

global class MassUpdateParentsProject implements Database.Batchable<sObject>{
	
	global final string query; 
	
	global MassUpdateParentsProject ()
	{
	   query = 'SELECT id, Umantis_Account_ID__c, Ultimate_Parent_Account__c, Ultimate_Parent_Id__c, Related_Contact__c, Umantis_NUM_ID__c, Primary_Contacts_Umantis_ID__c FROM Opportunity WHERE Project_Code__c !=\'Budget\' ';
	}
	
	global Database.QueryLocator start(Database.BatchableContext BC){
	
	   return Database.getQueryLocator(query);
	}
	
	global void execute(Database.BatchableContext BC, List<Opportunity> scope){
	
	  for(Opportunity o : scope){
	  	Integer i;
	  	if(o.Umantis_Account_ID__c !=null){
	  		i = Integer.valueOf(o.Umantis_Account_ID__c);
	  	}
	  	else{
	  		i=0;
	  	}
	  		if(i >= 600 && o.Related_Contact__c!=null){
	  			o.Ultimate_Parent_Account__c = o.Ultimate_Parent_Id__c;
	  		}
	  }
	  update scope;
	}
	
	
	global void finish(Database.BatchableContext BC){
	  // Get the ID of the AsyncApexJob representing this batch job  
	  // from Database.BatchableContext.    
	  // Query the AsyncApexJob object to retrieve the current job's information.  
	
	 AsyncApexJob a = [Select Id, Status, NumberOfErrors, JobItemsProcessed,
	   TotalJobItems, CreatedBy.Email
	   from AsyncApexJob where Id =:BC.getJobId()];
	
	  // Send an email to the Apex job's submitter notifying of job completion.  
	  Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
	  String[] toAddresses = new String[] {a.CreatedBy.Email};
	  mail.setToAddresses(toAddresses);
	  mail.setSubject('Apex Sharing Recalculation ' + a.Status);
	  mail.setPlainTextBody('The batch Apex job processed ' + a.TotalJobItems +
	    ' batches with '+ a.NumberOfErrors + ' failures.');
	
	  Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
	}	
}

 

 

bob_buzzardbob_buzzard

I'd suggest you simply create a new list of opportunities in your execute method, and only add the opportunity to that list if Primary_Contacts_Umantis_ID__c != Umantis_NUM_ID__c.  Then just update using the new list rather than the original scope.

This was selected as the best answer
ppiyushppiyush

Bob,

 

this makes sense - thanks a lot... was simpler than i had expected! :) 

 

Best,

Pranav