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
CASTing 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
CASTing the value into the
INTEGERfamily of data types.
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.