Loading

Field to field comparison in an SOQL WHERE clause

Fecha de publicación: Aug 22, 2024
Descripción

I want to execute a query by comparing two fields of the same object in a WHERE clause, but I am not able to use a field on 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' "
Solución

Salesforce does not allow direct field to field comparison in SOQL query.

To achieve this you can create a formula field that will compare fields and return a value (such as true or false) which you can 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 the query will be: 
List<User> Users = [SELECT id, name FROM User where NameCompare = 'true'];
We can do this with Reports now. As of Spring '20 release, this feature has been implemented. See: Filter Reports by Field Comparisons with Field-to-Field Filters
 
Recursos adicionales

Vote and comment on the Salesforce Idea for Field-to-Field filters for SOQL.

Número del artículo de conocimiento

000386076

 
Cargando
Salesforce Help | Article