function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
AlexPHPAlexPHP 

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?

 

 

Best Answer chosen by Admin (Salesforce Developers) 
MickleMickle

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:

 

 

Select Id, custom__c FROM Lead WHERE custom__c = 2147483648.0

 

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:

 

 

Double big2 = Double.valueOf('300000000000000');

List<Lead> leads = [SELECT Id, custom__c from Lead WHERE custom__c = :big2];

Integer lsize = leads.size();
system.debug('big2: ' + big2 );
system.debug('lsize: ' + lsize );

 

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

MickleMickle

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:

 

 

Select Id, custom__c FROM Lead WHERE custom__c = 2147483648.0

 

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:

 

 

Double big2 = Double.valueOf('300000000000000');

List<Lead> leads = [SELECT Id, custom__c from Lead WHERE custom__c = :big2];

Integer lsize = leads.size();
system.debug('big2: ' + big2 );
system.debug('lsize: ' + lsize );

 

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.

 

 

 

 

This was selected as the best answer
AlexPHPAlexPHP

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.