DECIMAL/NUMERIC Data Types - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Purpose

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

Syntax



n
The precision (the maximum number of digits that can be stored).
The range is from 1 through 38.
m
The scale (the number of fractional digits).
The range is from 0 through n.
When values are not specified for n, m, then the default is DECIMAL(5, 0).
When a value is not specified for m, then the default is DECIMAL(n, 0).
attributes
Appropriate data type attributes, column storage attributes, or column constraint attributes.
See Core Data Type Attributes and Constraint Attributes for specific information.

ANSI Compliance

NUMERIC is in the ANSI SQL:2011 standard. DECIMAL is a Teradata synonym for NUMERIC.

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.

The following list shows the number of bytes used to store decimal values.

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

For example, DECIMAL(3,2) requires 2 bytes and the value -2 would be represented as -200 in two’s complement binary form.

The maximum value for DECIMAL(n,m) is a value consisting of n 9’s with the decimal point m digits from the right. The minimum value would be the negative of the maximum value.

Examples:

n m maximum minimum
3 2 9.99 -9.99
4 4 .9999 -.9999
9 1 99999999.9 -99999999.9

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.

Client CPU Architecture Client Representation
  • RISC
  • Motorola 68000
  • WE 32000
Signed two’s complement binary number, most significant byte first.
For these values of n:
  • 1 or 2, the number is 8-bit
  • 3 or 4, the number is 16-bit
  • 5 to 9, the number is 32-bit
  • 10 to 18, the number is 64-bit
  • 19 to 38, the number is 128-bit
Intel Signed two’s complement binary number, least significant byte first.
For these values of n:
  • 1 or 2, the number is 8-bit
  • 3 or 4, the number is 16-bit
  • 5 to 9, the number is 32-bit
  • 10 to 18, the number is 64-bit
  • 19 to 38, the number is 128-bit
  • 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.

The + sign has the following hexadecimal representation:
  • X'A'
  • X'C'
  • X'E'
  • X'F'
The - sign has the following hexadecimal representation:
  • 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.

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 field 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 mainframe-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 workstation-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 field in DBS Control to control the maximum number of digits in a DECIMAL result for an expression containing DECIMAL arguments.

There are four valid values for the MaxDecimal field.

IF the value of MaxDecimal is … THEN the maximum number of digits for a DECIMAL result of an expression is …
0 15, if the operands have 15 or less digits.

18, if one operand has between 16 and 18 digits and the other operand has 18 or less digits.

15
18 18, if operands have 18 or less digits.
38 38

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 Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.

Example: DECIMAL Data Type

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 Type Default Formats.

Example: Defining a Table Column as DECIMAL Type

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) );

Related Topics

FOR more information on … SEE …
changing the value of MaxDecimal Teradata Vantage™ - Database Utilities , B035-1102 .
Max-decimal-returned in DBCAREA Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417.
Maximum Decimal Precision in DBCAREA Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.
DECIMALDIGITS BTEQ command Basic Teradata® Query Reference, B035-2414.
rounding DECIMAL types Rounding.
the result of expressions containing DECIMAL arguments Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.
using CAST to convert to a DECIMAL type of 15 or fewer digits or 18 or fewer digits