11
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

1 Answer 1

15

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
4
  • 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, 2014 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. Jun 18, 2014 at 17:40
  • Updated the answer to use a checkbox formula field instead of Text. Jun 18, 2014 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, 2014 at 18:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .