Teradata decimal calculation behaviour

Database
Fan

Teradata decimal calculation behaviour

I have the following 2 select statements, which produce 2 different results although they should return the same result

1) sel 1045259.3800 / 3155500.0000 * 3155500.0000

returns 1.045.417,15000000

2) sel 3155500.0000 * 1045259.3800 / 3155500.0000

returns 1.045.259,38000000

I think the 1st statement Teradata conducts the division first, and round up to 0,3333 and cut off all of the other decimal digit after that. In the second statement it conducts the multiply first and thus not lost/gain any different.

Interest thing is in Oracle and even in my SQL both statement return the same result. How could we have also the same behaviour in Teradata ?

2 REPLIES
Junior Contributor

Re: Teradata decimal calculation behaviour

This is documened behaviour, Teradata rounds after every step when DECIMALs are involved.

So the basic rule is "first multiply, then divide".

Or you cast the first operand to a NUMBER.

Fan

Re: Teradata decimal calculation behaviour

Hi Dieter,

Thanks for the prompt answer.

If we first multiply, there will be case that the numeric overflow may happen, so in some case division first could help. Unfortunately, it give the different result due to the rounding up/down behaviour. Casting maybe the workaround solution, but can we change such behaviour of Teradata via kind of DBS.Control flag ?

Br,

Socola