Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

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.