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.
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|
||Signed two’s complement binary number, most significant byte first.
For these values of n:
|Intel||Signed two’s complement binary number, least significant byte first.
For these values of n:
||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:
The - sign has the following hexadecimal representation:
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.
- 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.
|18||18, if operands have 18 or less digits.|
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.