17.10 - DECIMAL / NUMERIC - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

C Data Type Definition

typedef signed char DECIMAL1;
typedef short       DECIMAL2;
typedef int         DECIMAL4;

typedef signed char NUMERIC1;
typedef short       NUMERIC2;
typedef int         NUMERIC4;

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

typedef struct
{
    unsigned int int1;
    unsigned int int2;
    unsigned int int3;
    int          int4;
} DECIMAL16, NUMERIC16;

Usage

The size of the SQL type determines which C type to use.

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

The size of the SQL type also determines the range of values for a DECIMAL input argument or return argument. For example, if a CREATE FUNCTION statement defines a DECIMAL(1,0) input argument, the range of values for the argument is -9 to 9. A value outside the valid range of values produces a numeric overflow error. For details on DECIMAL types, see Teradata Vantage™ - Data Types and Literals, B035-1143.

For a DECIMAL type that can be represented as variable length, the best practice is to specify the DECIMAL so that it can handle the largest value that the C code is willing to deal with.

This example uses DECIMAL in a UDF definition and C function declaration.

SQL Function Definition Equivalent C Function Declaration
CREATE FUNCTION F1 (
  A DECIMAL(5,0) )
RETURNS DECIMAL(5,0)
 ...;
void f1( DECIMAL4 *a,
         DECIMAL4 *result,
   ... )
{  ... }