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.
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..
WHERE Contact__r.Con_Custom_Date__c = TODAY and Opportunity__r.Opp_Custom_Date__c = TODAY
WHERE Contact__r.Con_Custom_Date__c = Opportunity__r.Opp_Custom_Date__c
triggers a parsing error?