Field to field comparison in WHERE clause of SOQL
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
Best Answer
Salesforce does not allow direct field to field comparison in SOQL query. Workaround for this is mentioned here
In your case, add new formula checkbox field called
CreatedLessThenModified__c
on Opportunity:then the following soql will work: