Friday, July 8, 2011

Limits of SOQL -- SQLForce Solution

One aspect of SOQL that is a bit annoying is its restrictions of where column names can appear in a query. A query I have want to run a few times:

SELECT id FROM Account WHERE ShippingCountry != BillingCountry

Bummer....SOQL does not allow column names on the right side of an expression.

Recently a user reported a bug with SQLForce that is caused by the same SOQL limitation. He report that a SQLForce query like:

UPDATE Account SET ShippingCountry = BillingCountry WHERE ShippingCountry=null

set all ShippingCountry values to the string "BillingCountry." Why? Since SOQL does not have a native UPDATE statement, SQLForce simulates it using a SELECT followed by an Update() call. Since SQLForce knows that SOQL does not allow column names on the right, it assumes that the user forgot to quote a string in the UPDATE statement.

How can this problem be approached in a couple of lines of code. The answer for me is with the SQLForce Jython module. I'll post a typical solution tomorrow.

No comments: