0

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?

1
  • 2
    It's off by a factor of 10, but your number looks similar to the 32-bit integer limit. If you're using the developer console, clicking the "query plan" button should give you additional details (Go to help -> preferences -> Enable Query Plan if that button isn't showing up for you).
    – Derek F
    Nov 3, 2021 at 18:33

1 Answer 1

4

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).

  • In the Query Editor of the developer console, you'll get
    • Unknown error parsing query (exeucting the query)
    • ERROR at Row1:Column:[number] For input string (Query Plan)
    • Bind variables only allowed in Apex code (Query Plan if you try to use a long literal like 2147483648L)
  • In Anonymous Apex (at least through the dev console), you'll get
    • Invalid Integer (if you try to use an integer literal like 2147483648)
    • Unexpected token (if you try to use a long literal)

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 Apex

I 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 on Asset (Currency(18,0), so not quite a Number(18,0)) as well as a custom field on Asset (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).

1
  • Fantastic!! Thank you! Adding .0 to the number worked perfectly for my use case! Unfortunately, it is a field from a managed package, so changing the type isn't an option.
    – Mplax
    Nov 3, 2021 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.