Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 19 May 2004 @ 08:07:26 GMT


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


Subj:   Re: Rounding vs truncation
 
From:   Victor Sokovin

Rachel,

  Seems simple enough:  


  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.  


I think what you are seeing here is rounding, not truncation. But the rounding is done using the Teradata default rules, which might seem different from the standard rules used in business applications.

Just to add more examples here:

     select 10 N, cast(N as decimal(5,0))/4 P0, cast(N as decimal(5,1))/4 P1,
     cast(N as decimal(5,2))/4 P2 ;

      N P0 P1 P2
      10 2 2,5 2,50

     select 14 N, cast(N as decimal(5,0))/4 P0, cast(N as decimal(5,1))/4 P1,
     cast(N as decimal(5,2))/4 P2 ;

      N P0 P1 P2
      14 4 3,5 3,50

As you see 14/4 is rounded up to 4 but 10/4 is rounded down to 2. This is done according to the default rounding rules in Teradata as described in SQL Ref, Vol. 3, p.3-3. You can change the rules by modifying the DBS control record flag RoundHalfwayMagUp but as this would be a global change on the database it might lead to the upward bias in the results of other applications and I would investigate the change in detail before going for it.

The alternative would be to come up with some home made workaround for your particular case. I am sorry that the seemingly simple question does not lead to a very simple answer. I am myself very much interested in the rounding rules in TD and will be looking forward to seeing more feedback on them.


Regards,

Victor



     
  <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