 |
 |
Archives of the TeradataForum
Message Posted: Wed, 19 May 2004 @ 13:54:15 GMT
| Subj: | | Re: Rounding vs truncation |
| |
| From: | | Geoffrey Rommel |
| | How do I get an integer result from the division of two integers or currency/decimal divided by an integer that is ROUNDED vs. truncated?
i.e., select 10/4 results in 2 - I want the 2.5 to round to 3. | |
Well, you've opened a real can of worms here. Division of two integers truncates, so in order to get rounding you have to cast at least one
expression to a non-integer type *before* dividing. Decimal division is a hybrid written by Teradata to follow their own rules, including (by
default) rounding to the even number. Consider these results.
create table round_test
(int01 integer,
dec01 decimal(9,2) )
unique primary index(int01);
*** Table has been created.
*** Total elapsed time was 1 second.
ins round_test (2, 2.0);
ins round_test (6, 6.0);
ins round_test (10, 10.0);
[etc.]
select int01, dec01,
int01 / 4 truncated,
(dec01 / 4.0) (decimal(9,0)) "rounded?"
from round_test
order by 1;
*** Query completed. 7 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
int01 dec01 truncated rounded?
----------- ----------- ----------- -----------
2 2.00 0 0.
6 6.00 1 2.
10 10.00 2 2.
14 14.00 3 4.
18 18.00 4 4.
22 22.00 5 6.
26 26.00 6 6.
Above, we convert our integer to decimal, divide by 4.0, and then cast the result to decimal(9,0). The last cast forces rounding. As you
can see, the rounding goes to the even number. If you want to round up, as usually taught in grade school, you must change a DBS Control setting
or do your own calculation, thus:
select int01, dec01,
(((dec01 / 4.0) + 0.5) (integer)) / 1 "rounded!"
from round_test
order by 1;
*** Query completed. 7 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
int01 dec01 rounded!
----------- ----------- -----------
2 2.00 1
6 6.00 2
10 10.00 3
14 14.00 4
18 18.00 5
22 22.00 6
26 26.00 7
| |
