I get the error message “Bind variables allowed only in Apex [MALFORMED QUERY] code” when testing the SOQL statement called from Jitterbit

Question

We use Jitterbit to record requests from Salesforce, but we are having a problem. In the request condition statement, I compare two fields from the Salesforce table. When I go to check the request, it gives me the error "Binding variables allowed only in Apex code [MALFORMED QUERY]".

Here is an example request:

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 contained in the table Salesforce Price_Agreement_Item__c . How to create a SOQL statement that sets a select statement by comparing two fields in a table?

Any help is appreciated.

Thanks in advance.

Answer 1

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

Answer 2

SOQL does not currently support a direct field for comparing fields in WHERE clauses. From Field to compare fields in a WHERE clause in SOQL

Comparing fields with fields in a WHERE clause for SOQL

Article Code: 000187460

Description
I want to execute the query by comparing two fields of the same object in the WHERE clause, but I cannot use the field on the right side of the condition in the WHERE clause:

 List<user> users = [SELECT Id,name FROM User WHERE (FirstName != Lastname)]; 

The above query returns: "System.QueryException: unexpected token: 'Lastname'"

Resolution
Salesforce does not allow a direct field to compare fields in a SOQL query.

To do this, you can create a formula field that will compare the fields and return a value (for example, true or false) that you can use in WHERE.

So, for the above query, you can create a formula field for the user object with the return type Text, for example. NameCompare, with the formula IF (User.FirstName! = User.LastName, 'true', 'false')

Now our request will look like this:

 List<User> Users = [SELECT id, name FROM User where NameCompare= 'true']; 

The following idea was posted on the ideaexchange portal to allow comparison of fields between fields in SOQL:

https://success.salesforce.com/ideaView?id=08730000000BrHAAA0

You can create a formula field of type checkbox to return a boolean:

 Approved_Date__c > Last_Upload_Date__c 

Then rewrite the query with the WHERE clause, for example:

 SELECT Id FROM Price_Agreement_Item__c WHERE Approved_Date_Greater_Than_Last_Upload_Date__c = true 

Be careful how many lines are required to scan. It cannot use the index, so it will result in a full table scan. If the number of lines becomes too large, you may receive other errors.

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

Answer 3

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

For example, this:

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

Must be:

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

Answer 4

YES SAles Froce Suxks, the absence of binding variables means this:

cutsomTable__c.ACCOUNT_ID = Account.ACCOUNT_ID AND Account.Client_Cancelled__c ^ ERROR in row: 1: column: 2136 Bind variables are allowed only in Apex code: = end Tradelines to Pay

All the code I wrote to do this automatically in SOQL doesn't work! Tsk Tsk Sales Force. The best answer is to change the CRM until the sales team complies with current SQL standards.