I am trying to query a record where an external ID field is equal to a specific value. The external ID is set up as a Number data type to allow for 18 characters with 0 decimal places.
When I go to query for specific records (I'm using the Developer Console Query Editor), I get an "Unknown error parsing query" when the number I am querying against exceeds a certain size. But that size is well below the 18 character limit.
My query is very simple, like the below:
Select Id, External_Id__c From Account Where External_Id__c = 21474836000
It has been inconsistent, where sometimes the query will work with a given value and then the next time it will give an error, but the place where it stops working seems to be around this 21474836000 number. Unfortunately, the values I am querying for are closer to 47800000000000.
Is this a SOQL limitation I just didn't know about or am I missing something? How can I query for external IDs beyond this limitation?
Best Answer
Some simple testing makes me think that this is an integer limit issue.
A signed, 32-bit integer has a max value of 2,147,483,647. You should be fine if you're trying to filter using an integer literal in the signed 32-bit int range, but as soon as you go beyond that, you'll get an error (and the error you get changes based on how/where the query is executed).
2147483648L
)2147483648
)Binding a long literal (
WHERE Field = :2147483648L
) in Anonymous Apex works (or, at the very least, doesn't cause an error).Using a decimal literal (
WHERE Field = :2147483648.0
) works in the Query Editor as well as Anonymous ApexI don't really have a good setup to test this very rigorously, but it does appear that both a long literal and decimal literal should work with the equality operator. I used the
Price
field onAsset
(Currency(18,0), so not quite a Number(18,0)) as well as a custom field onAsset
(which is defined as a Number(4,0)) in my short testing, and got the same records using a regular integer (Field = 1
), binded long literal (Field = :1L
), and decimal literal (Field = 1.0
)Using a text field for your external Id in this case would probably have been the preferable approach (at least from the SFDC point of view).