Decimal not what it seems?

Database
N/A

Decimal not what it seems?

Hi. I'm trying to compute an IBAN number given an internal account number and the branch's sort code. This involves operations on large integer values.

However, internal implementation still seems to be based on double precision floating point numbers, along with their limitations.

Bug demonstrated by the following example:

select
cast('100000000000000000000000' as decimal(38,0))
, cast('100000000000000000000001' as decimal(38,0))

The results are 99999999999999992000000 and 100000000000000010000000, respectively, which reveals the effect of the underlying machine epsilon.

Is there no way to perform computations on big decimals while retaining precision?
2 REPLIES
N/A

Re: Decimal not what it seems?

Dont blame Teradata, blame you and Bill! (of Microsoft)

If you do:

select
100000000000000000000000 (decimal(38,0))
, 100000000000000000000001 (decimal(38,0))
;

in Bteq, the answer comes back correctly.

In SQL Assistant, the number is converted to float on the way in.
If you put the number in quotes, the character is converted to float before the cast, so again you lose some significance.

Re: Decimal not what it seems?

In SQL Assisstant, check option "Display full accuracy for BigINt and Decimal(16) or above (See Help)".