I am receiving the error "Bind variables only allowed in Apex Code [MALFORMED QUERY]" when testing a SOQL statement called from Jitterbit

Question

We are using Jitterbit to query records from Salesforce, but we are running into an issue. In the query condition statement I am comparing two fields from the Salesforce table. When I go to test the query, it gives me the error "Bind variables only allowed in Apex Code [MALFORMED QUERY]".

Here is an example of the query:

SELECT Id FROM Price_Agreement_Item__c WHERE Approved_Date__c > Last_Upload_Date__c

The fields Approved_Date__c and Last_Upload_Date__c are both contained in Salesforce table Price_Agreement_Item__c. How does one create a SOQL statement that conditions the select statement comparing two fields in the table?

Any help is appreciated.

Thank you in advance.

Answer 1

SOQL doesn't currently support direct field to field comparison in WHERE clauses. From Field to field comparison in WHERE clause of SOQL

Field to field comparison in WHERE clause of SOQL

Knowledge Article Number: 000187460

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

You could create a formula field of type Checkbox to return a boolean:

Approved_Date__c > Last_Upload_Date__c

Then rewrite your query with your WHERE clause like:

SELECT Id  
FROM Price_Agreement_Item__c 
WHERE Approved_Date_Greater_Than_Last_Upload_Date__c = true

Be careful about how many rows this will need to scan over. It can't use an index, so will result in a full table scan. If the number of rows gets too large you could get other errors.

Incidentally, the Salesforce Stack Exchange is a great place to ask Salesforce specific questions. This answer references a question from there answered by Jesse Altman with comments by Keith C and sfdcfox.

Answer 2

In addition to what Daniel Ballinger said, the Soap API also does not allow the 'IN' clause in SOQL queries. It's stupid, but then again, that's 95% of Salesforce.

Answer 3

Another way this can happen is if you copy your query to the command line and run it without escaping the quotes around any string constants.

E.g., this:

curl -X GET https://x.salesforce.com/services/data/v31.0/query?q=SELECT%20COUNT\(\)%20FROM%20YourObj%20WHERE%20field%20!=%20'Good'

Should be this:

curl -X GET https://x.salesforce.com/services/data/v31.0/query?q=SELECT%20COUNT\(\)%20FROM%20YourObj%20WHERE%20field%20!=%20%27Good%27