function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Dave The RaveDave The Rave 

SOQL compare 2 fields in same object

Hi All,

I having created an SOQL which should show records where the value of two number fields is NOT EQUAL to each other.
  
SELECT Id, name, ContactPerson__c, CertRegOrganisation2__c, CertRegStatus__c, obligatoryPointsReq__c, ObligatoryPointsObtainedYr1__c  
FROM Registration_Layer__c
WHERE CertRegOrganisation2__c = 'SCVM' AND CertRegStatus__c = 'Valid' AND obligatoryPointsReq__c <> ObligatoryPointsObtainedYr1__c

The two fields in question are obligatoryPointsReq__c <> ObligatoryPointsObtainedYr1__c, what is the correct syntax for this?

Thanks,

David
Alain CabonAlain Cabon
Hi,

That could seem incredible if you know SQL but it is impossible directly with SOQL.

The only common technique (given by Salesforce) is to create a new formula field (that seems very heavy but that is the only alternative without reading all the data without the filter and by filtering in a loop).

Formula field: type checkbox on Registration_Layer__c
Name: isObligatoryPointsDifferent
Value:  obligatoryPointsReq__c <> ObligatoryPointsObtainedYr1__c
  
SELECT Id, name, ContactPerson__c, CertRegOrganisation2__c, CertRegStatus__c, obligatoryPointsReq__c, ObligatoryPointsObtainedYr1__c  
FROM Registration_Layer__c
WHERE CertRegOrganisation2__c = 'SCVM' AND CertRegStatus__c = 'Valid'
 AND isObligatoryPointsDifferent = true