Ask Your Question

Salesforce origin, offset in scientific notation

asked 2018-05-15 09:51:59 -0500

peter_f gravatar image

updated 2018-06-04 05:23:32 -0500

When using a numeric filed as offset which is longer than 8 digits, decimal 13 to be exact, it seems that Salesforce is providing the offset in scientific notation.

During the initial execution of the query, using the init offset, the new offset returned by the SOQL gets stored in scientific notation, but this format is not usable as offset in the where condition for repeating queries anymore. The scientific notation causes an exception on Salesforce side during execution.

1526394852000 => 1.526394852000E12

Although the number is shown correctly when using the preview function as well as in the Salesforce developer console, it seems it gets delivered in scientific notation by SOQL without a chance to influence that.

Currently I'm not sure if that is a bug/issue and how to address this.

We were thinking of several workarounds and tried quite some stuff to overcome that issue using text fields, format() with alias on SOQL, using Streamsets functions (str:, math: these only seems to be accounted in input fields not in the query itself) all without success.

The only thing that worked was directly changing the source of the salesforce_lib to convert the offset back in the desired format.

If needed I can provide logfiles & configuration or file an issue if necessary.

Best regards Peter

edit retag flag offensive close merge delete


What do you see when executing a SOQL query in the Salesforce Developer Console or Developer Workbench? I'd be interested in seeing the source changes that made it work for you if you'd be interested in contributing them to the project!

metadaddy gravatar imagemetadaddy ( 2018-05-23 14:32:07 -0500 )edit

1 Answer

Sort by ยป oldest newest most voted

answered 2018-05-25 04:57:33 -0500

peter_f gravatar image

updated 2018-05-30 01:32:37 -0500

The results vary depending which tool you actually use to execute the query.

Salesforce workbench => scientific notation

Salesforce developer console => decimal value

Streamsets preview => decimal value

Streamsets pipeline/logfile/source offset => scientific notation

But in any case executing the soql with scientific notation leads to an error, whether you use streamsets, developer console or workbench. => Streamsets, workbench

This is the part I changed in to reconvert the offset into a decimal, it's more like a hack than a fix I guess, respecting the actual datatype of the offset column would make more sense or storing the offset in the correct format may be a better approach than reconvert in the prepare query part.

If this will/can not be compensated on Streamsets side and we have to wait until Salesforce changes this (very unlikely, only 40 votes of 400 to address this issue) it should be clearly pointed out in the Saleforce_lib connector documentation that numbers longer than 8 digits are not functional as offset.

Best regards Peter

edit flag offensive delete link more


Interesting - the fact that it shows up correctly in preview means that the original SOQL query might be giving us a 'good' value, but it's being lost in a conversion somewhere. I filed to investigate.

metadaddy gravatar imagemetadaddy ( 2018-05-25 10:02:41 -0500 )edit

Great news, thank you for addressing the issue!

peter_f gravatar imagepeter_f ( 2018-05-26 00:46:19 -0500 )edit

Is it possible or planed to use Streamset functions within the conditions of a SOQL query or at least around the offset? This would be a more generic and a little less hacky approach to prepare the offset prior execution. e.g.: ... WHERE BigNumber__c = ${math:covertSN(${offset})} ...

peter_f gravatar imagepeter_f ( 2018-05-28 03:00:32 -0500 )edit

Hi Peter - the Jira is probably a better place for this discussion:

metadaddy gravatar imagemetadaddy ( 2018-06-04 13:43:04 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-15 09:51:59 -0500

Seen: 465 times

Last updated: Jun 04 '18