 |
 |
Archives of the TeradataForum
Message Posted: Thu, 16 May 2002 @ 16:27:25 GMT
| 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
| |
