Signed Zone DECIMAL Conversion
Introduction
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 Teradata Database.
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.
Using FORMAT in CREATE TABLE
When the FORMAT phrase is used in the CREATE TABLE statement, as follows:
CREATE TABLE Test1 (Col1 DECIMAL(4) FORMAT '9999S');
then zoned input character strings can be loaded with standard INSERT statements, whether the data is defined:
INSERT INTO Test1 (Col1) VALUES ('123J');
or read from a client system data record via the USING modifier:
USING Ext1 (CHAR(4))
INSERT INTO Test1 (Col1)
VALUES (:Ext1);
The data record contains the string ‘123J’.
Subsequently, a simple select, such as:
SELECT Col1 FROM Test1;
returns:
Col1
----
123J
Using Another FORMAT in the SELECT Statement
To override an attached format, another FORMAT phrase is needed in the retrieval statement. Using the preceding table, one of the two following statements must be used to retrieve the numeric value:
SELECT Col1 (FORMAT '+9999') FROM Test1;
or
SELECT CAST (Col1 AS INTEGER) FROM Test1;
The result is as follows.
Col1
-----
-1231
If FORMAT is Not Attached to the Column
If the format is not attached to the column, the sign FORMAT phrase must be used each time signed zoned decimal data is loaded and each time the row value is to be retrieved in signed zoned decimal format.
For example, if a table is defined using a CREATE TABLE statement like this:
CREATE TABLE Test2 (Col2 DECIMAL(5));
then the sign FORMAT phrase must be included whenever signed zoned decimal strings are inserted.
This is true whether the definition is explicitly defined, as it is in Examples 1 and 2, or defined implicitly by being read from a client system data record as it is in Examples 3 and 4.
Example
INSERT INTO Test2 (Col2)
VALUES ('5678B' (DECIMAL(5), FORMAT '99999S'));
Example
INSERT INTO Test2 (Col2)
VALUES ('9012L' (DECIMAL(5), FORMAT '99999S'));
Example
USING Ext2 (CHAR(5))
INSERT INTO Test2 (Col2)
VALUES (:Ext2 (DECIMAL(5), FORMAT '99999S'));
Example
USING Ext2 (CHAR(5))
INSERT INTO Test2 (Col2)
VALUES (:Ext2 (DECIMAL(5), FORMAT '99999S'));
where Ext2 contains the strings ‘5678B’ and ‘9012L’.
Because Col2 does not have an attached FORMAT phrase, a simple SELECT, such as the following example, returns the results as seen immediately following.
SELECT Col2 FROM Test2;
Col2
-------
56782.
-90123.
A sign FORMAT phrase must be included in the SELECT statement in order to retrieve the values ‘5678B’ and ‘9012L’.
It is important to remember this rule when manipulating signed zoned decimal values, especially when using sophisticated facilities like subqueries.
Example
This example is based on the data from Example 4.
Consider a column created with a CHARACTER data type.
CREATE TABLE Test3 (Col3 CHAR(5));
The column is loaded by selecting, without a sign FORMAT phrase, values from an “unattached” column, as follows.
INSERT INTO Test3 (Col3)
SELECT Col2 FROM Test2 ;
The values that are inserted are the following:
Col3
-----
5678
-9012
The sign FORMAT phrase must be included in the query specification in order to insert the values ‘5678B’ and ‘9012L’.
Related Topics
For information on data types, data type formats, formatting characters, and the FORMAT phrase, see SQL Data Types and Literals.