Decimal/Numeric Data Types

This chapter describes the numeric data types, including: • BIGINT • BYTEINT • DECIMAL/NUMERIC • FLOAT/REAL/DOUBLE PRECISION • INTEGER • SMALLINT

Each numeric type can be combined with a CHECK constraint when the data type is defined or modified.

The chapter also describes several usage considerations for the system default DECIMAL size definition. The relevant topics are the following:

• ANSI-Teradata correspondences for naming numeric data types • Rounding and numeric/decimal data types

• Usage considerations for DECIMAL

• DECIMAL and the MaxDecimal DBS Control Flag • Behavior of SQL operations independent of MaxDecimal • Behavior for different values of MaxDecimal

• CAST and the DECIMAL data type

With respect to FLOAT data types, this chapter provides the following topic that covers some of the comparison and computation inaccuracies associated with floating point values:

• Floating Point Values are Inexact

ANSI/Teradata Synonyms for Numeric Types

The following table lists Teradata-ANSI synonyms for numeric data types.

ANSI Name Teradata Equivalent

NUMERIC DECIMAL

REAL FLOAT

BYTEINT Data Type

Purpose

Represents a signed binary integer value in the range -128 to 127.

Syntax

where:

ANSI Compliance

BYTEINT is a Teradata extension to the ANSI SQL-2003 standard.

Storage

BYTEINT is stored as one byte.

External Representation of BYTEINT

The following table lists the client representations for the BYTEINT data type.

Determining the application definitions and client data types is the responsibility of the application programmer.

Syntax element … Specifies …

attributes appropriate data type attributes, column storage attributes, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information. FF07D186

BYTEINT

attributes

Client CPU Architecture Client Representation

BYTEINT Data Type

Format

BYTEINT format is described in “Data Display Default Formats” on page 235.

Example

In the following table definition, column EdLev is a BYTEINT data type: CREATE TABLE Education

(Id CHAR(9)

,LastName CHAR(26)

,EdLev BYTEINT FORMAT 'Z9'

CHECK (EdLev BETWEEN 0 AND 22) NOT NULL); • UTS

• RISC

• Motorola 68000

• WE 32000

• Intel

One byte 8-bit signed 2’s complement binary number.

SMALLINT Data Type

Purpose

Represents a signed binary integer value in the range -32768 to 32767.

Syntax

where:

Storage

SMALLINT values are stored as two bytes, with the least significant byte first.

External Representation of SMALLINT

The following table lists client representations for the SMALLINT data type.

Determining the application definitions and client data types is the responsibility of the application programmer.

Syntax element … Specifies …

attributes appropriate data type attributes, column storage attributes, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

FF07D187 SMALLINT

attributes

Client CPU Architecture Client Representation

IBM mainframe Two byte 16-bit signed 2’s complement binary number, most significant byte first.

• UTS

• RISC

• Motorola 68000

• WE 32000

Two byte 16-bit signed 2’s complement binary number, most significant byte first.

Intel Two byte 16-bit signed 2’s complement binary number, least significant byte first.

SMALLINT Data Type

Format

For information on the default display format, see “Data Display Default Formats” on page 235.

Example

In the following table definition, column DeptNo is a SMALLINT data type: CREATE TABLE Departments

(DeptNo SMALLINT FORMAT '999' BETWEEN 100 AND 900 ,ManagerName CHAR(26)

INTEGER Data Type

Purpose

Represents a signed, binary integer value from -2,147,483,648 to 2,147,483,647.

Syntax

where:

Storage

INTEGER values are stored as four bytes with the least significant byte first.

External Representation of INTEGER

The following table lists the client representations for the Teradata INTEGER data type. Determining the application definitions and client data types is the responsibility of the application programmer.

Syntax element … Specifies …

attributes appropriate data type attributes, column storage attributes, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

FF07D188 INTEGER

INT attributes

Client CPU Architecture Client Representation

IBM mainframe Four byte 32-bit signed 2’s complement binary number, most significant byte first.

• UTS

• RISC

• Motorola 68000

• WE 32000

Four byte 32-bit signed 2’s complement binary number, most significant byte first.

Intel Two byte 16-bit signed 2’s complement binary number, least significant byte first.

INTEGER Data Type

Format

For information on the default display format, see “Data Display Default Formats” on page 235.

Example

In the following table definition, column TelNo is an INTEGER data type: CREATE TABLE Contact

(Id CHAR(9)

,LastName CHAR(26) ,TelNo INTEGER);

BIGINT Data Type

Purpose

Represents a signed, binary integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Syntax

where:

Storage

BIGINT values are stored as eight bytes with the least significant byte first.

External Representation of BIGINT

The following table lists the client representations for the Teradata BIGINT data type. Determining the application definitions and client data types is the responsibility of the application programmer.

Syntax element … Specifies …

attributes appropriate data type attributes, column storage attributes, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

1101A393 attributes

BIGINT

Client CPU Architecture Client Representation

IBM mainframe Eight byte 64-bit signed 2’s complement binary number, most significant byte first.

• UTS

• RISC

• Motorola 68000

• WE 32000

Eight byte 64-bit signed 2’s complement binary number, most significant byte first.

Intel Eight byte 64-bit signed 2’s complement binary number, least significant byte first.

BIGINT Data Type

Format

For information on the default display format, see “Data Display Default Formats” on page 235.

Example

In the following table definition, column Total is a BIGINT data type: CREATE TABLE RelevantNumbers

(Id CHAR(9)

,LastSummary INTEGER ,Total BIGINT);

DECIMAL/NUMERIC Data Types

Purpose

Represents a decimal number of n digits, with m of those n digits to the right of the decimal point.

Syntax

where:

Storage

Decimal numbers are scaled by the power of ten equal to the number of fractional digits. The number is stored as a two’s complement binary number in 1, 2, 4, 8, or 16 bytes. The number of bytes used for a decimal value depends on the total number of digits in that value.

Syntax element … Specifies …

n the precision.

The range is from 1 through 38.

m the scale.

The range is from 0 through n.

WHEN values are not specified for … THEN the default is …

n, m DECIMAL(5, 0)

m DECIMAL(n, 0)

attributes appropriate data type attributes, column storage attributes, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Constraint Attributes” on page 20 for specific information.

1101A455 DECIMAL n ( ) DEC NUMERIC ,m attributes

DECIMAL/NUMERIC Data Types The following list shows the number of bytes used to store decimal values.

External Representation of DECIMAL/NUMERIC Numbers

The following table lists the client representations for the DECIMAL/NUMERIC data type. Determining the application definitions and client data types is the responsibility of the application programmer.

Number of Digits Number of Bytes

1 to 2 1 3 to 4 2 5 to 9 4 10 to 18 8 19 to 38 16 Client CPU

Architecture Client Representation

• RISC

• Motorola 68000

• WE 32000

Signed two’s complement binary number, most significant byte first.

For n = … The number is …

1 or 2 8-bit

3 or 4 16-bit

5 to 9 32-bit

10 to 18 64-bit

19 to 38 128-bit

Intel Signed two’s complement binary number, least significant byte first.

For n = … The number is …

1 or 2 8-bit

3 or 4 16-bit

5 to 9 32-bit

10 to 18 64-bit

DECIMAL/NUMERIC Data Types

Application Requirements for the Size of DECIMAL Types

Some applications require DECIMAL types to have 18 or fewer digits or possibly 15 or fewer digits.

Applications with such requirements may need to access DECIMAL columns that have more digits or use expressions that may produce DECIMAL results with more digits. To help with DECIMAL type size requirements, you can use the following:

• CAST function to convert to a DECIMAL type of 18 or fewer digits or 15 or fewer digits • MaxDecimal flag in DBS Control to set the maximum number of digits in a DECIMAL

result for an expression containing DECIMAL arguments

• Max-decimal-returned field in the DBCAREA data area to set the maximum precision for a DECIMAL data type result column for CLIv2 for channel-attached systems

• Maximum Decimal Precision field in the DBCAREA data area to set the maximum precision for a DECIMAL data type result column for CLIv2 for network-attached systems • DECIMALDIGITS BTEQ command to set the maximum precision for decimal values

associated with subsequent SQL requests in nonfield mode. The maximum decimal digits to return applies to all of the record modes (record, indicator, and multipart indicator), but does not apply to field mode. In field mode, you must perform a CAST or use the FORMAT phrase.

Size of DECIMAL Expression Result Type

You can set the MaxDecimal flag in DBS Control to control the maximum number of digits in a DECIMAL result for an expression containing DECIMAL arguments.

• IBM mainframe

• UTS

Twenty bytes (maximum) n-digit (where n represents the precision of the number and must be less than 38), signed, packed decimal numbers. The rightmost nibble represents the sign.

This sign … Has this hexadecimal representation …

+ X'A' X'C' X'E' X'F' - X'B' X'D'

The remaining nibbles represent the digits X'0' - X'9', left-padded with 0 digits when n is even, giving a total of (n+2)/2 bytes, or 20 bytes maximum.

Client CPU

DECIMAL/NUMERIC Data Types There are four valid values for the MaxDecimal flag.

The number of digits in a DECIMAL result for an expression containing DECIMAL

arguments depends on the value of the MaxDecimal in DBS Control and the number of digits in the DECIMAL arguments.

For example, suppose the value of MaxDecimal is 15. An arithmetic expression that adds a DECIMAL(15) argument and a DECIMAL(15) argument results in a DECIMAL(15). An arithmetic expression that adds a DECIMAL(15) argument and a DECIMAL(18) argument results in a DECIMAL(18).

For more information on the number of digits in a DECIMAL result for an expression containing DECIMAL arguments, see SQL Reference: Functions and Operators.

Example 1

Consider the number 256.78. Its type is DECIMAL (5,2) and its default format is expressed either as -(4).9(2) or the equivalent ----.99

The default DECIMAL or NUMERIC display formats are described in “Data Display Default Formats” on page 235.

Example 2

In the following table definition, column Salary is assigned the type DECIMAL. CREATE TABLE Salaries

(Id CHAR(9)

,Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' CHECK (Salary BETWEEN 1.00 AND 999000.00) );

Purpose

Represent values in sign/magnitude form ranging from 2.226 x 10-308 to 1.797 x 10308. FLOAT is synonymous with REAL and DOUBLE PRECISION.

Syntax

where:

Floating Point Value Storage and Manipulation

Eight bytes are used to hold a floating point value.

Floating point values are stored and manipulated internally in IEEE floating point format. Floating point values are stored with the least significant byte first, with one bit for the mantissa sign, 11 bits for the exponent, and 52 bits for the mantissa. The mantissa sign is in the most significant bit position; the exponent sign is a part of the exponent field (excess-1024 notation, in which (1024 - exponent) = sign).

Negative numbers differ from positive numbers of the same magnitude only in the sign bit. Teradata Database supports normalized, but not non-normalized, client floating point values. The range of IEEE floating point values may be wider than that supported by the client system. Therefore, values can be created and stored in Teradata Database that return error messages when converted for delivery to the client system.

External Representation of FLOAT/REAL/DOUBLE PRECISION

The following table lists the client representations for the Teradata FLOAT/REAL/DOUBLE PRECISION data type.

Syntax element … Specifies …

attributes appropriate data type attributes, column storage attributes, or column constraint attributes.

See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

FF07D189

FLOAT REAL

DOUBLE PRECISION

FLOAT/REAL/DOUBLE PRECISION Data Types

Determining the application definitions and client data types is the responsibility of the application programmer.

Client CPU

Architecture Client Representation

IBM mainframe

Eight byte 64-bit (double precision) floating point number, most significant byte first, with the attributes in the following table.

This many bits … Represent this attribute of the number …

1 Sign of the fraction.

7 Unsigned power of 16 exponent stored as actual plus X’40’.

56 Unsigned fraction.

UTS Eight byte 64-bit (double precision) floating point number, most significant byte first, with the attributes in the following table.

This many bits … Represent this attribute of the number …

1 Sign of the fraction.

7 Unsigned power of 16 exponent stored as actual plus X’40’.

56 Unsigned fraction.

• RISC

• Motorola 68000

• WE 32000

Eight byte 64-bit (double precision) floating point number, most significant byte first, with the attributes in the following table.

This many bits … Represent this attribute of the number …

1 Implied extra preceding bit. 1 Sign of the fraction.

11 Unsigned power of 2 exponent stored as actual plus X’3FFH’.

52 Unsigned fraction.

Intel Eight byte 64-bit (double precision) floating point number, least significant byte first with the attributes in the following table.

This many bits … Represent this attribute of the number …

1 Implied extra preceding bit. 1 Sign of the fraction.

11 Unsigned power of 16 exponent stored as actual plus X’3FFH’.

FLOAT/REAL/DOUBLE PRECISION Data Types

Example

In the following table definition, column SalaryFactor is a FLOAT data type: CREATE TABLE Salaries

(Id CHAR(9)

,SalaryFactor FLOAT BETWEEN .1 AND 1E1 );

Comparison and Computation Inaccuracies

Because floating point numbers are not stored as exact values, some inaccuracy is inherent and unavoidable when they are involved in comparisons and computations.

Here are some of the problems you might encounter:

• Identical computations in floating point arithmetic may produce slightly different results on different machines because internal precision differs from computer to computer, and from model to model in the same series of computer.

• Because floating point decimal values generally do not have exact binary representations, calculations involving floating point values can often produce results that are not what you might expect. For example, the common decimal number 0.1 is a repeating sequence in binary and does not have a precise binary representation. If you perform a calculation and then compare the results against some expected value, it is highly unlikely that you get exactly the result you intended.

• If you add or subtract floating point numbers that differ greatly in size, the contribution of the small numbers can effectively be lost. For example, 1E20 + 1.0 and 1E20 - 1.0 evaluate to 1E20.

• Operations involving floating point numbers are not always associative due to

approximation and rounding errors: ((A + B) + C) is not always equal to (A + (B + C)). Although not readily apparent, the non-associativity of floating point arithmetic can also affect aggregate operations: you can get different results each time you use an aggregate function on a given set of floating point data. When Teradata Database performs an aggregation, it accumulates individual terms from each AMP involved in the computation and evaluates the terms in order of arrival to produce the final result. Because the order of evaluation can produce slightly different results, and because the order in which individual AMPs finish their part of the work is unpredictable, the results of an aggregate function on the same data on the same system can vary.

• Conversion of DECIMAL and INTEGER values to FLOAT values might result in a loss of precision or produce a number that cannot be represented exactly.

• GROUP BY on a FLOAT type can produce inconsistent results. If you need exact results, do not use floating point types.

Example 1: Floating Point Arithmetic is Non-Associative

Consider a table where the same values are inserted into two floating point columns, but in a different order:

CREATE TABLE t1 (i INTEGER, a FLOAT, b FLOAT); INSERT t1 (1, 1000.55, 2000.7);

INSERT t1 (1, 2000.4, 2000.1); INSERT t1 (1, 2000.1, 2000.4); INSERT t1 (1, 2000.7, 1000.55);

Floating Point Values are Inexact The conditional expression in the following SELECT statement compares the sums of the values in the two columns:

SELECT i, SUM(a) as sum_a, SUM(b) as sum_b FROM t1

GROUP BY i

HAVING sum_a <> sum_b;

Because the values that the two SUM calculations uses are the same, the obvious result is that no rows are returned. However, the result is:

i sum_a sum_b

--- --- --- 1 7.00175000000000E 003 7.00175000000000E 003

What appears to be an invisible error has crept into the calculations. The following statement shows the error:

SELECT ABS(SUM(a) - SUM(b)) FROM t1; Here is the result:

Abs((Sum(a)-Sum(b))) ---

1.81898940354586E-012

Example 2: Comparing Floating Point Values

Calculations involving floating point values often produce results that are not what you expect. If you perform a floating point calculation and then compare the results against some expected value, it is unlikely that you get the intended result. Consider the results of “Example 1: Floating Point Arithmetic is Non-Associative” on page 100.

Instead of comparing the results of a floating point calculation, make sure that the result is greater or less than what is needed, with a given error. Here is an example of how to rewrite the statement in “Example 1: Floating Point Arithmetic is Non-Associative” and achieve the desired results:

SELECT i, SUM(a) as sum_a, SUM(b) as sum_b FROM t1

GROUP BY i

Rounding

Rounding DECIMAL/NUMERIC Data Types

If a value being inserted does not fit into a DECIMAL or NUMERIC column, the value is rounded. Teradata Database rounds using the digit to the right of the rounding digit, the last digit that fits into the DECIMAL/NUMERIC field.

Additional considerations come into play when the value of the digit to the right of the rounding digit is exactly five. One consideration is the value of the DBS Control Record RoundHalfwayMagUp flag.

When the DBS Control Record RoundHalfwayMagUp flag is set to FALSE, then rounding is performed as explained in the following table. This is the default.

The following table shows the results of inserting values into a DECIMAL(3,2) column.

In document Teradata Database. SQL Reference. Data Types and Literals (Page 83-105)

ANSI/Teradata Synonyms for Numeric Types

BYTEINT Data Type

Purpose

Syntax

ANSI Compliance

Storage

External Representation of BYTEINT

Format

Example

SMALLINT Data Type

Purpose

Syntax

Storage

External Representation of SMALLINT

Format

Example

INTEGER Data Type

Purpose

Syntax

Storage

External Representation of INTEGER

Format

Example

BIGINT Data Type

Purpose

Syntax

Storage

External Representation of BIGINT

Format

Example