Archives of the TeradataForum

Message Posted: Sat, 06 Aug 2005 @ 08:36:23 GMT

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

Hi,

When you write your UDF using Numerics/Decimals you need to know where your Decimal point should be because Teradata represents DECIMALS internally as INTEGERS with an IMPLIED decimal point.

Your example of 105.00000 would be inside Teradata represented as

     10500000   or 0xA037A0

Since you declare your type as Decimal(10,5) The Largest number you could store would be 99,999.99999 and removing the decimal point the number is 9,999,999,999 which is bigger than will fit in a 32-bit integer so it requires a 64-bit integer.

However Teradata doesn't support 64-bit integers so it will come into your function as a DECIMAL8 type which is really a 64-bit integer broken into 2 32-bit parts.

     typedef struct
     {
     unsigned long int low;
     long int high;
     } DECIMAL8, NUMERIC8;

The easiest way to deal with this inside your function is to convert it back to a 64-bit integer.

Here is how your Trunc function would be coded.

     Replace FUNCTION mytrunc
     (myval DECIMAL(10,5))
     RETURNS VARCHAR(10)
     SPECIFIC mytrunc
     LANGUAGE C
     NO SQL
     DETERMINISTIC
     PARAMETER STYLE TD_GENERAL
     EXTERNAL ;

     -----------------  mytrunc.c -------------------

     #define SQL_TEXT Latin_Text
     #include "sqltypes_td.h"
     #include 
     #include 

     #ifdef _WIN32
         typedef  __int64  my64int;
     #else
         typedef  long long  my64int;
     #endif

     void mytrunc(  DECIMAL8       *a,
                    VARCHAR_LATIN  *result,
                    char sqlstate[6])
     {
        my64int  valuea;
        my64int  left_parta;
        int      right_parta;

        memcpy(&valuea,a,sizeof(valuea));

        left_parta = valuea / ((int)(1e5));
        right_parta = (a->low) % ((int)(1e5));

        /* Return just the INTEGER portion ( the left_part ) */
        sprintf(result,"%d",left_parta);

        return;
     }

     ---------------- end include ----------------

             /* 1e5 is the Place where the decimal point goes because
                  it is declared as 10,5 adjust accordingly */
             /* The left_part is the portion the to left of the decimal */
             /* The right_part is the portion to the left of the decimal */

        I use a->low instead of valuea in the MOD because this will
        eliminate the issue with MOD of a negative number which isn't
        clearly defined.

        The left_part represent the part to the left of the decimal point.
        (The integer portion of the Decimal.)

        The right_part represent the part to the right of the decimal point.
        (The decimal portion of the Decimal.)

        I suppose you could use a FLOAT or DOUBLE inside the UDF but
        that might not exactly represent the DECIMAL.

            myfloat = valuea / (1e5);

        The MEMCPY is required because some platforms require alignment
        of 64-bit integers on 8 byte boundaries and the DECIMAL8 structure
        may be on a 4 byte boundary.

        For Decimals < 10 digits  you don't need the conversion from structure
        to 64-bit int because they fit in 32-bit ints just fine.

From the manual.

     FOR DECIMAL(n,m) or NUMERIC(n,m), where =85 Use one of these C types =85
     1 <= n <= 2 DECIMAL1 or NUMERIC1
     3 <= n <= 4 DECIMAL2 or NUMERIC2
     5 <= n <= 9 DECIMAL4 or NUMERIC4
     10 <= n <= 18 DECIMAL8 or NUMERIC8


     C Data Type Definition
     typedef signed char DECIMAL1;
     typedef short DECIMAL2;
     typedef long int DECIMAL4;
     typedef struct
     {
     unsigned long int low;
     long int high;
     } DECIMAL8;

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