You need to sign in to do that
Don't have an account?
AlexPHP
Malformed Query when querying against a large 10+ digit double
When I query for a large double such as:
Select Id, Name From MyObject__c Where Custom_Double_Field__c = 2000000000
It works.
But when I query for a larger number...
Select Id, Name From MyObject__c Where Custom_Double_Field__c = 3000000000
I get a MALFORMED QUERY error:
Query failed: MALFORMED_QUERY: where Custom_Double_Field__c = 3000000000 Error at Row:1:Column:65 For input string: "3000000000"
I'm not sure what the exact maximum number that it will take before it fails to work. How can I fix this? Do I need to cast the larger number in my SOQL?
First, let me prefice this by saying I'm very new to programming, so take this with a grain of salt. That being said, I think I have figured out the problem, and found a solution.
It appears Salesforce passes a 32 bit integer into the query, unless you explicitily tell it to use a double. Because of this, the maximum number you can query is limited by the maximum size of a 32 bit integer 2,147,483,647.
By adding a decimal value, you force Salesforce to run the query as a double. However, even as a novice, I can see where this could generate problems.
To query this using the DataLoader, I've found this works:
You can apply this same logic using APEX code as well, forcing Salesforce to use a double by including the decimal place. However, another solution I found works, executed in the Anonymous Code in the System Log is as follows:
Just an FYI I found when researching this:
When the user sets the precision in custom fields in the Salesforce.com application, it displays the precision set by the user, even if the user enters a more precise value than defined for those fields. However, when you set the precision in custom fields using the API, no rounding occurs when the user retrieves the number field.
All Answers
First, let me prefice this by saying I'm very new to programming, so take this with a grain of salt. That being said, I think I have figured out the problem, and found a solution.
It appears Salesforce passes a 32 bit integer into the query, unless you explicitily tell it to use a double. Because of this, the maximum number you can query is limited by the maximum size of a 32 bit integer 2,147,483,647.
By adding a decimal value, you force Salesforce to run the query as a double. However, even as a novice, I can see where this could generate problems.
To query this using the DataLoader, I've found this works:
You can apply this same logic using APEX code as well, forcing Salesforce to use a double by including the decimal place. However, another solution I found works, executed in the Anonymous Code in the System Log is as follows:
Just an FYI I found when researching this:
When the user sets the precision in custom fields in the Salesforce.com application, it displays the precision set by the user, even if the user enters a more precise value than defined for those fields. However, when you set the precision in custom fields using the API, no rounding occurs when the user retrieves the number field.
Thanks, Mickle!
I remember doing that before, but I forgot how it was done. Adding ".0" should work for my case.
Thanks for the analysis and additional information regarding how Salesforce handles precision.