Ability to Customize Decimal Scale In Numeric Fields

It would also be beneficial for us to define smaller scale numeric values for more efficient data storage.  Having it always default to a scale of 38 is unnecessary.

Actually this feature is sort of present in TX when synchronizing a source but it is undocumented and unsupported. It involves updating the projectRepository db manually, hence unsupported.

TX was kind enough to help us out in the past with this when we encountered some very funky databases that did not even respect ODBC standards :).

I guess Product Management and developpers should perhaps see how they can turn this into a supported documented feature?

With regards to controlling the precision of numerical data types, there's also a possible issue when ODBC / AnySource source has many numericals and you need them all. If the source itself is too generous with its numeric precision but isn't bound to the limits of MS SQL, you could run into an error when trying to create the stage db. Many numeric fields with maximum precision can exceed the maximum table row size supported in SQL server. One such annoying source we ran into for example is Progress.

Therefore, both for other sources than SQL and for sizes of stage & DW, controlling precision as well would be a very handy feature.

FYI I do think people are mixing up precision and scale here.

Precision is the number of digits which is always 38 for TX. Scale is the number of digits right of the comma or decimal point. So you can create a number 22,16 just by setting 16 in the custom numeric field.

But if you only need a 8,2 number TX effectively creates a 38,2 and wastes valuable bytes.

A workaround for this is to create a view in the stage and use that as basis for your data movement to the stage. In the view you would have to explicitly write for every numeric field:

CAST(YourField AS decimal(8,2)) AS YourField

It is a lot of work though ;-).

If you right click on the image that Jake posted, you should be able to open it in a new tab and will be able to see the problem.

The issue encountered is that when you perform mathematical operations with max sized numeric/decimal fields, SQL automatically reduces the number of decimal places to 6.

To prevent SQL from truncating the decimals at 6 places, the data type precision has to be reduced.

This behaviour makes using numeric fields useless if divisions are applied and > 6 decimal places are required.

I strongly support having the ability to specify precision as well as scale for numeric fields.

Using float as a stopgap runs into issues as well (apart from potential inaccuracy): https://stackoverflow.com/questions/33322778/avg-of-float-inconsistency <-- see designated answer. We have seen real effects of parallel float-based calculations being different between query runs, so that is not a solution either.

Could you give us a status update on this issue? 

So I'm guessing that "DH12" was version 18.10 (released autumn 2018), which would make DH13 = 19.2 (March 2019).

So is DH14 = 19.6 (just recently released)? 

Did this feature make it into 19.6? I can't see it in the release notes. 

If not, is there any update on when this feature might be available? 

We had to push this feature to a later release. I will get back with an expected release date.

Do you have any new when this feature will be implemented?

Thanks, Natalia

This feature was added in version 20.5.1