Archives of the TeradataForum
Message Posted: Tue, 18 May 2004 @ 22:33:28 GMT
The only way to obtain a decimal output is to use decimal going into the equation:
So, select 10/4 is the same as:
sel int_data/other_int_data from table_name;
must become 10/4.5 or using columns:
sel int_data/(other_int_data(dec(4,1)) from table_name;
then to round it:
sel (int_data/(other_int_data(dec(4,1)))(dec(5,0) from table_name;
or if you prefer:
sel cast( (int_data/cast(other_int_data as dec(4,1))) as dec(5,0)) from table_name;
Casting data to an integer truncates any and all decimal places without remorse. Casting to a decimal with fewer decimal places than the original data does the rounding.
Side note: by default Teradata is installed with accounting rules guiding the rounding process. In other words, when you have a number that is .5 exactly the rounding is biased. So, 4.5 rounds to 4 and 5.5 rounds to 6 (even numbers round down and odd numbers round up). There is a flag in the DBS Control record called ROUNDHALFWAYUP (or something along these lines). Its default setting is 'N' for no - do not always round up when it is half-way or .5.
Hope this helps and regards,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|