2

Could you please help me for fix my SOQL Query?

I have SOQL Scrip like :

    SELECT Employee_No__c FROM HREMPASSIGNMENT__c  
WHERE Payroll_Name__c=:selectedPayrollName 
AND Payroll_Name__c != null  
AND  Employee_No__r.Terminate_Date__c < Employee_No__r.Pay_Thru_Date__c 
GROUP BY Employee_No__c

But I get Error :

expecting a colon, found 'Employee_No__r.Pay_Thru_Date__c' and In Query Editor I get error : Unknown error parsing query

And when I try to change AND Employee_No__r.Terminate_Date__c < Employee_No__r.Pay_Thru_Date__c with Employee_No__r.Terminate_Date__c != null

I Get the result..

So how to compare date in SOQL ?

0

You cannot really compare two fields in SOQL. Instead you can achieve the desired result in the following way:

for(HREMPASSIGNMENT__c objHREMPASSIGNMENT : [SELECT Employee_No__c,
                                                  Employee_No__r.Terminate_Date__c
                                                  Employee_No__r.Pay_Thru_Date__c
                                             FROM HREMPASSIGNMENT__c  
                                             WHERE Payroll_Name__c=:selectedPayrollName 
                                              AND Payroll_Name__c != null 
                                          GROUP BY Employee_No__c ]) {

if(objHREMPASSIGNMENT.Employee_No__r.Terminate_Date__c < objHREMPASSIGNMENT.Employee_No__r.Pay_Thru_Date__c )
    lstHREMPASSIGNMENT.add(objHREMPASSIGNMENT);

}

4

The issue you have got here is not just about comparing date fields, it is more about comparing two fields of the same record. Please refer to this post: https://developer.salesforce.com/forums/?id=906F000000093VkIAI

Simply put, queries like below:

Select Id, Name From CustomObject__c Where A_Field__c < B_Field__c

Simply won't work in SOQL.

If you don't hit the governor limit, you can try to following:

List<HREMPASSIGNMENT__c > QResults = new List<HREMPASSIGNMENT__c  >();
for(HREMPASSIGNMENT__c h: [SELECT id,Employee_No__r.Terminate_Date__c,Employee_No__r.Pay_Thru_Date__c FROM HREMPASSIGNMENT__c LIMIT 30000]){
   if(h.Employee_No__r.Terminate_Date__c < h.Employee_No__r.Pay_Thru_Date__c){
      QResults.add(h);
   }
}

Alternatively, you can create a custom formula field to compare those two fields and put it under your custom object.

  • +1 for formula field. Probably the easiest, safest and fastest solution for this case – Novarg Dec 14 '16 at 7:22
0

You cannot filter a field against another field. Take a look at the documentation for fieldExpression (emphasis mine):

The field expression syntax of the WHERE clause in a SOQL query consists of a field name, a comparison operator, and a value that’s used to compare with the value in the field name.

fieldExpression uses the following syntax:

fieldName comparisonOperator value

fieldName
The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.

comparisonOperator
Case-insensitive operators that compare values.

value
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.

0

You can use salesforce Date functions. In a SOQL query you can specify either a particular date or a date literal. A date literal is a fixed expression that represents a relative range of time, such as last month, this week, or next year. For example

  • LAST_N_DAYS:n For the number n provided, starts 00:00:00 of the current day and continues for the past n days. SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365
  • NEXT_N_DAYS:n For the number n provided, starts 00:00:00 of the current day and continues for the next n days. SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15

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.