SOQL doesn't currently support direct field to field comparison in WHERE clauses. From Field to field comparison in WHERE clause of SOQL
Field to field comparison in WHERE clause of SOQL
Knowledge Article Number: 000187460
Description
I want to execute a query by comparing two fields of the same object in WHERE clause, but I am not able to use a field in
the right hand side of a condition in a WHERE clause:
List<user> users = [SELECT Id,name FROM User WHERE (FirstName != Lastname)];
The above query returns: "System.QueryException: unexpected token:
'Lastname' "
Resolution
Salesforce doesn't allow direct field to field comparison in SOQL query.
To achieve this you may create a formula field that will compare
fields and return a value (like true or false) which you may use in a
WHERE clause.
So for the above query, you could create a formula field on User
object with return type Text e.g. NameCompare, with the formula
IF(User.FirstName != User.LastName, 'true', 'false')
Now our query will be:
List<User> Users = [SELECT id, name FROM User where NameCompare= 'true'];
Following idea has been posted on ideaexchange portal for allowing
field-to-field comparison in SOQL:
https://success.salesforce.com/ideaView?id=08730000000BrHAAA0
You could create a formula field of type Checkbox to return a boolean:
Approved_Date__c > Last_Upload_Date__c
Then rewrite your query with your WHERE clause like:
SELECT Id
FROM Price_Agreement_Item__c
WHERE Approved_Date_Greater_Than_Last_Upload_Date__c = true
Be careful about how many rows this will need to scan over. It can't use an index, so will result in a full table scan. If the number of rows gets too large you could get other errors.
Incidentally, the Salesforce Stack Exchange is a great place to ask Salesforce specific questions. This answer references a question from there answered by Jesse Altman with comments by Keith C and sfdcfox.
