7

When I try to run the below query in the developer console, I am getting the error message:

Unknown error parsing query

What could cause this exception with the below query?

SELECT Name, AccountLookup__r.OwnerId, OwnerId FROM Lead WHERE (OwnerId <> AccountLookup__r.OwnerId)
16

As per documentation:

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:
https://success.salesforce.com/ideaView?id=08730000000BrHAAA0

You could simplify/clean up the above approach by using a checkbox formula:

User.FirstName != User.LastName

Which would change the query to:

List<User> lstUser = [SELECT Id, Name FROM User WHERE NameCompare = true];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.