Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Rounding vs truncation
From:   Michael Larkins

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>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019