SOQL Where Object.Field != RelatedObject.Field

Question

Is it possible, to have a self-referencing where clause in a SOQL query?

For example, I am trying to find where Account and Opportunity currency codes do not match, and I thought something like the following would work?

SELECT Id, CurrencyIsoCode, Account.Name, Account.CurrencyIsoCode 
          FROM Opportunity 
          WHERE Account.CurrencyIsoCode != CurrencyIsoCode

However, when I try and execute this I get an Unknown Error Parsing Query message.

Is what I am trying to do possible? I have reviewed the whole Force.com SOQL and SOSL Reference guide and cannot see anything suggesting how to do this.

Answer 1

No. The standard workaround is to use a formula.

CurrencyIsoCode_Equals_Parent__c:

TEXT(CurrencyIsoCode) = TEXT(Account.CurrencyIsoCode)

Then filter on that:

WHERE CurrencyIsoCode_Equals_Parent__c = false

Please note that this behavior is covered in the Force.com SOQL and SOSL Reference documentation on fieldExpresion Syntax (emphasis mine):

  fieldName comparisonOperator value

...

Syntax
value

Description
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.