SQLLink4 Decimal issue, SQLLink4 adding decimal length Options

[Gary]

I have a SQL Query in SQLLink4 returning numbers with two decimal points, however, SQLLink4 is storing the numbers with nine decimal points.

I have tried using several different Filter queries to link the data with only two decimals showing but nothing is then linked into DataPipe.

Is there a Filter Statement that will work to return to two decimals?

I have attached an image of the initial SQLLink4 query showing both the result from the query and the stored values.
Thanks.

Attached File(s)

 SQLLink4_Query_decimal_issue.jpg ( 56.62K ) Number of downloads: 3

 

[Scott McDowell]

Hello Gary,

SQLLink 4 uses a SQL CE back-end to store query results so that we can run additional queries against the original results. This makes SQLLink 4 very versatile but limits the data types available. To ensure that users are able to output query results in their desired format, our developers exposed decimal number formatting options, which can be configured individually for each query. Settings for the representation of decimal numbers are found in the Query Properties dialog.

[Gary]

Well there you go. There's always something new to learn.
I'd missed that one.

Thanks.

[Scott McDowell]

No problem, Gary. It's a relatively new feature so it's possible the last time you used SQLLink the option wasn't there. We also added the side-by-side comparison of the data when you test your query so that it's apparent that SQLLink will change certain data values due to the SQL CE back-end.

[newton]

I don't mean to hijack;

What if you don't want any decimal, but to round up say 89.8 to 90 no decimal places at all.

I have made the changes to the decimal location to all 0's but it makes no difference.

Do I have to send that query someplace else like excel and pull it back in?

Thanks

[Scott McDowell]

I don't mean to hijack;

What if you don't want any decimal, but to round up say 89.8 to 90 no decimal places at all.

I have made the changes to the decimal location to all 0's but it makes no difference.

Do I have to send that query someplace else like excel and pull it back in?

Thanks

Hello Newton,

If you want to round the value, you'll want to use something like the SQL Round() function (the actual function syntax may varry based on the SQL server you're using). This would be part of your SQL statement rather than a configuration setting in SQLLink as the SQLLink setting is intended only for formatting after the result has been returned from the database.