Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 19 May 2004 @ 13:54:15 GMT


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


Subj:   Re: Rounding vs truncation
 
From:   Geoffrey Rommel

  How do I get an integer result from the division of two integers or currency/decimal divided by an integer that is ROUNDED vs. truncated? i.e., select 10/4 results in 2 - I want the 2.5 to round to 3.  


Well, you've opened a real can of worms here. Division of two integers truncates, so in order to get rounding you have to cast at least one expression to a non-integer type *before* dividing. Decimal division is a hybrid written by Teradata to follow their own rules, including (by default) rounding to the even number. Consider these results.

     create table round_test
     (int01   integer,
      dec01   decimal(9,2) )
     unique primary index(int01);

      *** Table has been created.
      *** Total elapsed time was 1 second.

     ins round_test (2, 2.0);
     ins round_test (6, 6.0);
     ins round_test (10, 10.0);
     [etc.]

     select int01, dec01,
         int01 / 4  truncated,
         (dec01 / 4.0) (decimal(9,0))  "rounded?"
      from round_test
      order by 1;

      *** Query completed. 7 rows found. 4 columns returned.
      *** Total elapsed time was 1 second.

           int01        dec01    truncated     rounded?
     -----------  -----------  -----------  -----------
               2         2.00            0           0.
               6         6.00            1           2.
              10        10.00            2           2.
              14        14.00            3           4.
              18        18.00            4           4.
              22        22.00            5           6.
              26        26.00            6           6.

Above, we convert our integer to decimal, divide by 4.0, and then cast the result to decimal(9,0). The last cast forces rounding. As you can see, the rounding goes to the even number. If you want to round up, as usually taught in grade school, you must change a DBS Control setting or do your own calculation, thus:

     select int01, dec01,
         (((dec01 / 4.0) + 0.5) (integer)) / 1  "rounded!"
      from round_test
      order by 1;

      *** Query completed. 7 rows found. 3 columns returned.
      *** Total elapsed time was 1 second.

           int01        dec01     rounded!
     -----------  -----------  -----------
               2         2.00            1
               6         6.00            2
              10        10.00            3
              14        14.00            4
              18        18.00            5
              22        22.00            6
              26        26.00            7


     
  <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