I am looking for a solution to the following problems that I am encountering using 12.0 SP9 with Float and Decimal data types.
I need to store engineering data that can contain very large and very small numbers. In testing a property using the Float and Decimal types I find that both have problems storing the full precision that SQL Server supports.
For Float, large numbers work as expected from -1.79E+308 to +1.79E+308. However small numbers do not. The smallest number that I can store in a Float property from the Aras item's form is 1E-20. I can not store any more digits of precision (for example, 1.000000000000001E-20), either entering the value in scientific or decimal notation.
Using MS SQL Server Management Studio I can manually edit the item to store smaller numbers in the database (down to the minimum of 2.23E-308). However these are rounded to 0 in Aras.
As an aside, despite the documentation and training saying that you need to specify Precision and Scale for floats, neither of these appear to have any effect on how the Float column is specified in SQL Server - it always uses the default 8 byte float size.
For Decimal there are problems with precision, rounding and large numbers. Here Precision and Scale really do matter, as they should. For Precision of 38 and Scale of 0, I should be able to store values up to 10^38-1 = 99999999999999999999999999999999999999, and I can do this directly in SQL. However Aras gives a Conversion Overflow error in search grids and the item form if this is stored in the database. The largest number that Aras can handle is 79228162514264330000000000000. This happens to be 2^96 rounded to 16 digits of precision. If Scale > 0 is specified the largest number possible is reduced.
Aras only seems to be able to store 16 or sometimes 17 precise digits in the Decimal type with Precision set to 38, and the rounding behavior is inconsistent. For example entering 11111111111111111 rounds up (!) to 11111111111111112 when the item is saved. 1111111111111111111 also rounds up to 1111111111111111200, but 1111111111111111100 rounds down to 1111111111111111000.
My best guess is that Aras is performing internal conversions that result in the rounding and overflow problems, since using SQL Server directly works as expected.
Is this a known bug? Is there any way to work around this behavior? Do later service packs fix these issues?