You need to sign in to do that
Don't have an account?
ppiyush
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
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
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.
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?
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)
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.
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
Imran, this doesnt work...
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?
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...
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
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?
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.
Bob,
this makes sense - thanks a lot... was simpler than i had expected! :)
Best,
Pranav