All Forums Database
socola 4 posts Joined 03/15
17 Mar 2015
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 ?
 

dnoeth 4628 posts Joined 11/04
17 Mar 2015

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.

Dieter

socola 4 posts Joined 03/15
18 Mar 2015

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
 

You must sign in to leave a comment.