SOQL query failing when comparing formula field and currency field values

Question

I'm having trouble trying to figure out what's wrong with this SOQL sentence:

SELECT
    Id,
    FormulaField__c,
    CurrencyField__c 
FROM
    MyTable 
WHERE
    FormulaField__c != CurrencyField__c AND
    FieldA__c = 1

This is the context:

FormulaField__c:  is a formula field returning a currency value
CurrencyField__c: currency field edited manually in the point & click view.
FieldA__c:        just another field

Developer console, introverted as usual, only says: "Unknown error parsing query". I know the conflicted part of the query is the following comparison:

FormulaField__c != CurrencyField__c

because when I remove it, query executes just fine.

Can any one give me a hint of what's going on here and how can I solved it?

Thanks in advance

Answer 1

Unlike other query languages, Salesforce does not allow field to field comparisons in SOQL.

Per the salesforce knowledge article linked below, Salesforce recommends creating a boolean formula field that compares, in this case, FormulaField__c and CurrencyField__c and filtering by where that field == false. http://help.salesforce.com/HTViewSolution?id=000187460

Feel free to vote for this on the IdeaExchange. https://success.salesforce.com/ideaView?id=08730000000BrHAAA0

Answer 2

Marcel,

In SoQL, you are not allowed to compare a field to another. For more info, check here