Archives of the TeradataForum

Message Posted: Sat, 24 Jun 2006 @ 10:09:54 GMT

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

Here is an example of adding a number split into two parts. The basic principle is that of long addition, subtraction and multiplication can be done using similar principles. Division is a little more complex, but still possible.

     create table fixedpointarithmetic (
         id integer,
         msd_1 decimal (2,0),    /* Most significant digits of first number */
         lsd_1 decimal (2,0),    /* least significant digits of first number */
         msd_2 decimal (2,0),    /* Most significant digits of second number */
         lsd_2 decimal (2,0)     /* least significant digits of second number */
     );


     delete from fixedpointarithmetic all;
     insert into fixedpointarithmetic values (0,  0, 10,  0, 20);
     insert into fixedpointarithmetic values (1,  0, 70,  0, 50);
     insert into fixedpointarithmetic values (2,  1, 70,  2, 50);


     select id, msd_1, lsd_1, msd_2, lsd_2,
          (msd_1 + msd_2 ) + (lsd_1 + lsd_2) / 100 (decimal (2,0)) as msd_sum,
          (lsd_1 + lsd_2 ) mod 100 (decimal (2,0)) (decimal (2,0)) as lsd_sum
     from fixedpointarithmetic
     order by 1;

For simplicity I've kept the fields to two digits. You can expand it to as many digits you like by changing the two constants (i.e. changing 100 to 1000000 would give you 6 digits).

Here's how it works: the lsd values are the least significant digits (in this case the tens and units). Msd is the most significant digits (in this case the thousands and hundreds).

To add simply add the two least significant numbers and discard any carry ( mod 100). To determine the most significant digits, add the msd's together with any carry from the least significant digits (lsd_1 + lsd_2) / 100.

The above query produces the following result:

     id  msd_1  lsd_1  msd_2  lsd_2  msd_sum  lsd_sum
      0      0     10      0     20        0       30
      1      0     70      0     50        1       20
      2      1     70      2     50        4       20

Translated:

     0010 + 0020 = 0030
     0070 + 0050 = 0120
     0170 + 0250 = 0420

Subtraction and multiplication can be achieved using a similar line of thinking. I'll let you know my rate if you want examples :-)

All the best

Glenn Mc

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