3

I think I may be hitting some sort of SOQL limitation in the WHERE clause. I am trying to return fields from two parent objects, after comparing two date fields on both Objects.

The goal is to have a list of contacts with associated opportunity fields where the opportunity date field is equal to some contact field.

NOTE: We have a custom junction between Opportunity and Contact named "Opportunity_Contact__c" due to the limits on the standard OCR sObj.

Specifically:

SELECT NAME, Con_Custom_Date__c,
  (SELECT Opportunity__r.Name FROM Opportunity_Contacts__r 
   WHERE Contact__r.Con_Custom_Date__c = Opportunity__r.Opp_Custom_Date__c)
FROM Contact

This returns a parsing error. However, if I try switching the query to something like:

SELECT NAME, Con_Custom_Date__c,
  (SELECT Opportunity__r.Name FROM Opportunity_Contacts__r 
   WHERE Contact__r.Con_Custom_Date__c = TODAY and Opportunity__r.Opp_Custom_Date__c = TODAY)
FROM Contact

This works. So the date fields are working and I am able to use them in the WHERE clause. However, when I compare one field directly to the other, I get a parsing error..

Why would

WHERE Contact__r.Con_Custom_Date__c = TODAY and Opportunity__r.Opp_Custom_Date__c = TODAY

work while

WHERE Contact__r.Con_Custom_Date__c = Opportunity__r.Opp_Custom_Date__c

triggers a parsing error?

Thanks!

6

Ahh, Salesforce doesn't allow direct field to field comparisons in a query. Source:

Field to field comparison in WHERE clause of SOQL

Knowledge Article Number

000187460

Description

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

Resolution

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

  • dont forget to accept your own answer, =P – glls Feb 6 '18 at 17:21
  • 3
    Please note this article is flawed. The formula should simply be a Checkbox formula with the value User.FirstName != User.LastName. Use a truthy value for this sort of situation, not Text. – Adrian Larson Feb 6 '18 at 17:26
  • 1
    @AdrianLarson I wouldn't call it flawed so much as outdated. – sfdcfox Feb 6 '18 at 17:35
  • I believe checkbox formulas existed when it was written. You could argue it's not technically wrong, but it's bad design. – Adrian Larson Feb 6 '18 at 17:36

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.