Dedicated to design and performance of databases and audio systems.
A question that was posed to me over a year ago has come back around: What is the function in Teradata to round numeric values?
The short answer is that there isn't one, per se. It is performed by CAST
ing a value to desired precision via the DECIMAL(n,m)
data type. However, there is a twist--in Teradata a DBS Control Setting determines how rounding occurs. It is the RoundHalfwayMagUp value which controls values that end with the digit 5--do they round up or down? Mathematically, we used to always round ending values of 5 up. This is not necessarily the case here.
I have used machines with RoundHalfwayMagUp set to FALSE
. This means that ending-significant-digit-5 values will round up or down depending on whether the rounding digit is even or odd. If odd, the value increases; an even value and it remains status quo.
An example: We want to round money values with cents to whole dollars. Values with zero to forty-nine cents will always round down; fifty-one to ninety-nine cents always round up. But, values with exactly fifty cents can go either way. Values with an even dollar amount will round down, and an odd dollar amount will round up.
POC: our volatile table (remember, anyone with a Teradata ID can create a volatile table):
create volatile table foo, no log ( id integer not null, val decimal(9,2) not null) unique primary index (id) on commit preserve rows ;
We load in our test data:
insert into foo (1,1.00);
insert into foo (2,0.99);
insert into foo (3,1.99);
insert into foo (4,0.49);
insert into foo (5,0.50);
insert into foo (6,1.50);
And, query it accordingly:
select
id,
cast(val as integer) as truncated_value,
cast(val as decimal(9)) as rounded_value,
cast(val + .001 as decimal(9)) as new_rounded_value
from foo
order by 1
;
Which results in
ID truncated_value rounded value new_rounded_value
1 1 1 1
2 0 1 1
3 1 2 2
4 0 0 0
5 0 0 1
6 1 2 2
Observations:
CAST
ing the value into the INTEGER
family of data types.CAST
to DECIMAL
. It takes the $0.50 value in record 5 and converts it to $0.501, and then rounds, thereby producing $1 instead of $0.I hope this helps with your future coding.
Cheers
-Brad