Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-17-2015
07:56 AM

03-17-2015
07:56 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-17-2015
10:32 AM

03-17-2015
10:32 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-18-2015
01:17 AM

03-18-2015
01:17 AM

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