[SalesForce] Getting ‘Bind variables only allowed in Apex code’ when attempting this query

SELECT Id, Last_stage_changed_date__c, CreatedDate 
FROM Opportunity 
WHERE Last_stage_changed_date__c < CreatedDate

Last_stage_changed_date__c is of the Date type. I feel like I'm doing something dumb but I can't spot it.

Getting the error:

Bind variables only allowed in Apex code

Best Answer

You cannot compare one field to another in SOQL, regardless of context. If you want to compare a custom date field to CreatedDate, you will need a separate checkbox formula field to do the comparison for you, then you can filter on this field being true.

Take a look at the documentation on fieldExpression Syntax (emphasis mine):

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

Related Solutions

[SalesForce] Bind variables only allowed in apex code

SOQL won't let you compare fields within a query. You'll need to make a checkbox formula field on Phase_Entity__c.

[SalesForce] Query to Compare Two Date Fields

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

Best Answer

Related Solutions

[SalesForce] Bind variables only allowed in apex code

[SalesForce] Query to Compare Two Date Fields

Related Topic