Ask Your Question
1

Salesforce origin, offset in scientific notation

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

peter_f gravatar image

updated 2018-05-23 05:32:39 -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

Comments

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
1

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

peter_f gravatar image

updated 2018-05-25 05:05:35 -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 ForceSource.java 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.

Best regards Peter

edit flag offensive delete link more

Comments

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 https://issues.streamsets.com/browse/SDC-9078 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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 41 times

Last updated: yesterday