Archives of the TeradataForum

Message Posted: Wed, 15 Sep 2004 @ 10:35:06 GMT

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

Naveen Ram Prasanna Kambhoji wrote:

          > SELECT 6*393.2600*1.57000*(10/60.00) Res
          >
          >  Result : 629.76656400000

     SELECT (10/60.00);

                       .17

          > SELECT 6*393.2600*1.57000*(CAST(10 AS DECIMAL(18,6))/60.00) Res
          >
          >  Result : 617.419434836400000

     SELECT (CAST(10 AS DECIMAL(18,6))/60.00);

                   .166667

          > SELECT 6*393.2600*1.57000*(CAST(10 AS FLOAT)/60.00) Res
          >
          >  Result : 617.418200000

     SELECT (CAST(10 AS FLOAT)/60.00);

       1.66666666666667E-001

But it works as expected in Teradata ;-)

It just a matter of different rules:

Check "Precision, Scale and Length" in BOL

vs.

Teradata SQL Reference, Functions and Operators
Chapter 2: Arithmetic Operators
Binary Arithmetic Result Data Types

In Teradata if there's a decimal datatype the result is rounded.

For multiplication the scales of both operands are added, for any other operation the larger scale is used, e.g.

     dec(3,5) / dec(4,7) -> dec(18,7)
     dec(3,5) * dec(4,7) -> dec(18,12)

          > SQL Server
          >
          > SELECT 6*393.2600*1.57000*(10/60.00)
          >
          >  Result : 617.415730327200000

MS:

     select 10/60.00

     (1 row(s) affected)

     -----------
     .166666

SS apparently doesn't round by default...

          > SELECT 6*393.2600*1.57000*((CAST(10 AS FLOAT))/60.00)
          >
          > Result : 617.41819999999996

MS:

     SELECT CAST(10 AS FLOAT)/60.00

     (1 row(s) affected)

     ----------------------
     0.16666666666666666

So which result do you want?

1:

     SELECT 6*393.2600*1.57000*0.166666;

                    617.415730327200000

But as you can't turn off rounding in Teradata it's difficult:

     SELECT 6*393.2600*1.57000*(cast((CAST(10 AS DECIMAL(18,7))/60.00) *
     1000000 as integer) / 1000000.000000);

2:

     SELECT 6*393.2600*1.57000*((CAST(10 AS FLOAT))/60.00);

                    6.17418200000000E 002

This is exactly the same as SS's result, but MS is too stupid to get rid of the floating point calculation error and displays more than the significant number of digits :-)

3:

Just remove the brackets ;-))

     SELECT 6*393.2600*1.57000*10/60.00 Res;

             617.418200000

     > SELECT 6*393.2600*1.57000*(CAST(10 AS DECIMAL(18,7))/60.00) Res - Gives
     > an error

     SELECT type(6*393.2600*1.57000*(CAST(10 AS DECIMAL(18,7))/60.00));

     DECIMAL(18,16)

18 is the maximum precsision and 617 just doesn't fit. And it wouldn't help, because SS doesn't round by default...

So which one do you think does the *right* calculation? Don't you have business rules how to correctly round values?

Dieter

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