Archives of the TeradataForum

Message Posted: Tue, 18 May 2004 @ 22:33:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Rachel:

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,

Michael Larkins
Certified Teradata Master

	<Prev	Next>		<<First	<Prev	Next>	Last>>