Represents a decimal number of n digits, with m of those n digits to the right of the decimal point.
Syntax
{ DECIMAL | DEC | NUMERIC } [ ( n [, m ] ) ] [ attributes [...] ]
- n
- The precision (the maximum number of digits that can be stored).
- m
- The scale (the number of fractional digits).
- attributes
- Appropriate data type attributes, column storage attributes, or column constraint attributes.
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 |
|---|---|
|
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. |
| 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 |