Teradata SQL can convert input data that is in signed zone (external) DECIMAL format to a NUMERIC data type, thus allowing numeric operations to be performed on row values. The column in which the signed zone decimal data is to be stored may be any numeric data type.
A FORMAT phrase incorporating the S sign character filters the data as it passes in and out of Vantage.
The rightmost character of the input data string is assumed to contain the zone (overpunch) bit.
The following table shows the characters representing zone-numeric combinations.
| Last Character (Input String) | Numeric Conversion | Last Character (Input String) | Numeric Conversion | Last Character (Input String) | Numeric Conversion |
|---|---|---|---|---|---|
| { A B C D E F G H I |
n … 0 n … 1 n … 2 n … 3 n … 4 n … 5 n … 6 n … 7 n … 8 n … 9 |
} J K L M N O P Q R |
-n … 0 -n … 1 -n … 2 -n … 3 -n … 4 -n … 5 -n … 6 -n … 7 -n … 8 -n … 9 |
0 1 2 3 4 5 6 7 8 9 |
n … 0 n … 1 n … 2 n … 3 n … 4 n … 5 n … 6 n … 7 n … 8 n … 9 |
The sign FORMAT phrase can be included in a CREATE TABLE or ALTER TABLE statement when the column is defined, or in the INSERT statement when the data is loaded. The chosen method depends on how the stored value is to be used.
When a sign FORMAT phrase is specified at column creation time, it is considered attached to the column because it translates data at the column level; that is, both when the data is loaded and when it is retrieved.