Unknown Error Parsing Lead Query

Question

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)

Answer 1

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];