Archives of the TeradataForum

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

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

Rachel,

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>>