+ Start a Discussion
Tom DJTom DJ 

Exception when converting number field value to string that can be used in a dynamic query.

 

I need to convert a number field of a queried record to a string and then use that
string in a dynamic SOQL query.   If the number has a large number of digits, the
converted-to string contains an exponent (e.g.  1.321135422343E10).   If I then
do a dynamic query for records that contain that number I get an exception.

I need a way to convert the internal number to a string that never returns
an exponent in the resulting string.

Example SOQL query:
  select Name,Id,OwnerId  from Lead WHERE (mynumberfield__c = 6.9935297349E10)
 
Resulting exception:
    System.QueryException: unexpected token: 'E10'
   
Here is a simplified example othe code that converts a record field value
to a string and then uses that converted string to do a dynamic SOQL query:

 

  String fieldname = 'mynumberfield__c';
  sObject dRecord = [select :fieldname FROM someObject limit 1];
  Map<String, Schema.SObjectField> schemafieldmap = SSDupeCatcherutility.getSchema(objectType);        
  Schema.sObjectField sof = schemafieldmap.get(fieldname);       
  Schema.Describefieldresult dfr =   sof.getDescribe();
  Object thevalue = foundrecord.get(fieldname);
  String convertedstring = '';
  
  // convert field value to a string
  if (fieldtype == Schema.DisplayType.Double)
  {
    Decimal converted = ((Decimal) thevalue);
    convertedstring = String.valueOf(converted);
  } 
  else if (fieldtype == Schema.DisplayType.Double) 
     ..
     ..
     
  // build query that uses the converted field 
  String querystring =  'select Name,Id,OwnerId  from ' + objectType + ' WHERE (' + fieldname + ' = ' + convertedstring + ')';

 

 

Any ideas on how to convert the record value to a string that can be used in a dynamic SOQL query?

 

Any help would be greatly appreciated.

 

 

Thanks,

 

TomDJ

kritinkritin

I think you should to use format method.

:manvery-happy:

JitendraJitendra
I also faced same kind of issue. When records were exported using dataloader, it was automatically changing format of number fields. So for this situation, we created Formula field containing text version of same number field. This will make sure that format will not change and can be used in Apex or exported dataloader file.