## Message Posted: Thu, 16 May 2002 @ 16:27:25 GMT

 < Last>>

 Subj: Re: Rounding in Teradata From: Geoffrey Rommel

 Why does Teradata round numbers the way it does? Is there a "real world" application of this type of rounding?

 For those that are confused by this line of questioning, I am trying to round decimal fields from hundredths to tenths and for the odd tenths place values they are rounding up and the even tenths place values are rounding down. For example, .15 rounds to .2, .25 rounds to .2, .35 rounds to .4, .45 rounds to .4, etc.

Well, you've opened a real can of worms here. Please bear with a slightly long explanation.

I assume that you are casting your decimal(n,2) numbers to decimal(n,1). As the example below shows, this will give exactly the results you mentioned, but you can get around it by doing your own rounding. I'm sorry to say that if you stored the numbers as FLOAT, the rounding would be even less predictable.

Now, why does this happen? I'm guessing here, but I believe that "decimal" numbers are actually stored as binary integers with implied decimal points. Thus, your "decimal(8,2)" fields are actually stored as fullword integers ("long int" in C), and Teradata applies the decimal points as needed during calculations. Thus, casting from decimal(8,2) to decimal(8,1) actually requires a calculation to get rid of the hundredths place; for instance, 3.45 would actually be an int 345, which has to be converted to either int 34 or int 35. Apparently Teradata chose to "round" to the even value every time. This approach is often recommended by mathematicians because in this way the "rounding down" tends to cancel out the "rounding up".

Nevertheless, it seems confusing, and I found no explicit mention of this rule in the manuals.

--wgr

```     create table znums
(arb_key integer,
decno   decimal(8,2) )
unique  primary index(arb_key);
ins znums(1, .15);
ins znums(2, .25);
ins znums(3, .35);
ins znums(4, .45);
ins znums(5, .55);
ins znums(6, .65);

sel arb_key, decno, (decno + 0.05) / 1,
decno(decimal(8,1))
from znums
order by 1;

*** Query completed. 6 rows found. 4 columns returned.
*** Total elapsed time was 1 second.

arb_key    decno  ((decno+0.05)/1)    decno
--------  -------  ----------------  -------
1      .15               .20       .2
2      .25               .30       .2
3      .35               .40       .4
4      .45               .50       .4
5      .55               .60       .6
6      .65               .70       .6
```

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2002 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback