9
SELECT count() FROM Case where  lastmodifieddate > Createddate 

basically i am trying to compare the two date time fields, but i am getting below error in workbench.

MALFORMED_QUERY: Case where lastmodifieddate > Createddate

^ ERROR at Row:1:Column:51 Bind variables only allowed in Apex code

14

The way you are trying to do this isn't possible. Salesforce docs:

Field to field comparison in WHERE clause of SOQL

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 doesn't 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

In your specific scenario, you will want to create a formula field of type Checkbox like:

LastModifiedDate >  CreatedDate

Then rewrite your query with your WHERE clause like:

SELECT count() FROM Case where Last_Modified_Greater_Than_Created__c = true
  • Now that checkbox formula fields are available, is there any reason not to use those instead of 'true' and 'false' strings? – Keith C Jun 18 '14 at 17:37
  • 2
    Not that I know of to be honest @KeithC. I didn't even think about that. Using a checkbox would be a better idea. – Jesse Altman Jun 18 '14 at 17:40
  • Updated the answer to use a checkbox formula field instead of Text. – Jesse Altman Jun 18 '14 at 17:45
  • 5
    You should mention that using formulas like this cause full table scans, which will result in aborted queries if they're too large. – sfdcfox Jun 18 '14 at 18:23

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.