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 does not 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: