Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 16 May 2002 @ 16:27:25 GMT


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


Subj:   Re: Rounding in Teradata
 
From:   Geoffrey Rommel

  Why does Teradata round numbers the way it does? Is there a "real world" application of this type of rounding?  


  For those that are confused by this line of questioning, I am trying to round decimal fields from hundredths to tenths and for the odd tenths place values they are rounding up and the even tenths place values are rounding down. For example, .15 rounds to .2, .25 rounds to .2, .35 rounds to .4, .45 rounds to .4, etc.  



Well, you've opened a real can of worms here. Please bear with a slightly long explanation.

I assume that you are casting your decimal(n,2) numbers to decimal(n,1). As the example below shows, this will give exactly the results you mentioned, but you can get around it by doing your own rounding. I'm sorry to say that if you stored the numbers as FLOAT, the rounding would be even less predictable.

Now, why does this happen? I'm guessing here, but I believe that "decimal" numbers are actually stored as binary integers with implied decimal points. Thus, your "decimal(8,2)" fields are actually stored as fullword integers ("long int" in C), and Teradata applies the decimal points as needed during calculations. Thus, casting from decimal(8,2) to decimal(8,1) actually requires a calculation to get rid of the hundredths place; for instance, 3.45 would actually be an int 345, which has to be converted to either int 34 or int 35. Apparently Teradata chose to "round" to the even value every time. This approach is often recommended by mathematicians because in this way the "rounding down" tends to cancel out the "rounding up".

Nevertheless, it seems confusing, and I found no explicit mention of this rule in the manuals.


--wgr


     create table znums
     (arb_key integer,
     decno   decimal(8,2) )
     unique  primary index(arb_key);
     ins znums(1, .15);
     ins znums(2, .25);
     ins znums(3, .35);
     ins znums(4, .45);
     ins znums(5, .55);
     ins znums(6, .65);

     sel arb_key, decno, (decno + 0.05) / 1,
        decno(decimal(8,1))
      from znums
      order by 1;

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

      arb_key    decno  ((decno+0.05)/1)    decno
     --------  -------  ----------------  -------
            1      .15               .20       .2
            2      .25               .30       .2
            3      .35               .40       .4
            4      .45               .50       .4
            5      .55               .60       .6
            6      .65               .70       .6


     
  <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: 27 Dec 2016