Teradata decimal precision vs. Oracle number precision
We have got a bit of strange scenario at customer site, data is being loaded from Oracle to Teradata – Oracle Number(38,20) to Teradata Decimal (38,20). Somehow during loading process (through Informatica using TPT load) values get loaded with additional precision digits which are not present on source.
Oracle (source) -> 18.1355857519158
Teradata (target) -> 18.13558575191579791408
I've done a character count on Oracle value which suggest that there aren't any hidden precision digits, just not being visible due to formatting. But somehow value gets these additional digits once loaded on Teradata.
Interesting bit is that when target data type is changed to Number(38,20) (new to TD14), these precision digits no longer appears in the value. I did a little bit of digging in understanding the difference between Decimal and Number data type but have not been able to establish the precise reason for these additional precision digits.
Also, another challenge is that ERWin (v. 9.5.02) is being used as a modelling tool which doesn’t have Number available in list of data types. Closest to it is Numeric data type but that gets translated to Decimal on deployment. So it’s something which will have to be maintained manually if the only solution of getting rid of additional digits is to stick with Number data type.