DECIMAL(x) and DECIMAL(x,y)
Length: 1, 2, 4, 8 or 16 bytes for network; packed decimal for mainframe
Description: 128-bit double precision, floating point
Period Data Type Specifications
- BEGIN (the beginning element)
- END (the ending element)
- PERIOD(DATE)
- PERIOD(TIME[(n)])
- PERIOD(TIME[(n)] WITH TIME ZONE)
- PERIOD(TIMESTAMP[(n)])
- PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
For more information on the PERIOD data type, see Teradata Vantage™ - Data Types and Literals, B035-1143.
NULLIF Performance
Using a large number of NULLIF clauses can cause a significant increase in the CPU usage on the system where Teradata TPump is running. This rise in CPU usage may increase the time the job takes to run.
- FILLER commands in the LAYOUT
- Input position gaps or overlaps
- Concatenated fields
- DROP clauses
To avoid an increase in CPU usage on the system running Teradata TPump, transfer the processing of NULLIF expressions to the database.
Example 1
Instead of specifying the following:
... .FIELD fc * CHAR(5) NULLIF fc = 'empty'; .FIELD fi * INTEGER NULLIF fi = 0; ... .DML LABEL ins; INSERT INTO tbl1 VALUES(...,:fc,:fi,...);
Use this instead:
... .FIELD fc * CHAR(5); .FIELD fi * INTEGER; ... .DML LABEL ins; INSERT INTO tbl1 VALUES(...,NULLIF(:fc,'empty'),NULLIF(:fi,0),...);
Example 2
In more complex situations, as in the following example:
... .FIELD fs * CHAR(1) ; .FIELD fc * CHAR(5) NULLIF (fs <> 'M') AND (fs <> 'F'); .FIELD fi * INTEGER NULLIF fi < 0; ... .DML LABEL ins; INSERT INTO tbl2 VALUES(...,:fs,:fc,:fi,...);
Use this instead:
... .FIELD fs * CHAR(1) ; .FIELD fc * CHAR(5); .FIELD fi * INTEGER; ... .DML LABEL ins; INSERT INTO tbl2 VALUES(...,:fs, CASE WHEN (:fs = 'M') OR (:fs = 'F') THEN :fc ELSE NULL END, CASE WHEN (:fi >= 0) THEN :fi ELSE NULL END,...);
Using ANSI/SQL DataTime Data Types
When the DATEFORM command is used to specify ANSIDATE as the DATE data type, each DATE field is internally converted to a CHAR(10) field. All ANSI/SQL DateTime TIME, TIMESTAMP, and INTERVAL data types must be converted to fixed-length CHAR data types to specify column and field names in a Teradata TPump FIELD command.
The following table shows how to use ANSI/SQL DateTime specifications.
Data Type | Variable Definition | Conversion Example |
---|---|---|
TIME TIME (n) |
n = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(8 +n + (1 if n> 0, otherwise 0)) Format (n = 0): hh:mm:ss Example: 11:37:58 Format (n = 4): hh:mm:ss.ssss Example: 11:37:58.1234 |
TIMESTAMP TIMESTAMP (n) |
n = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(19 + n + (1 if n > 0, otherwise 0)) yyyy-mm-dd hh:mm:ss 1998-09-04 11:37:58 yyyy-mm-dd hh:mm:ss.ssss 1998-09-04 11:37:58.1234 |
TIME WITH TIME ZONE TIME (n) WITH TIME ZONE |
n = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(14 + n + (1 if n > 0, otherwise 0)) hh:mm:ss{±}hh:mm 11:37:58-08:00 hh:mm:ss.ssss{±}hh:mm 11:37:58.1234-08:00 |
TIMESTAMP WITH TIME ZONE TIMESTAMP (n) WITH TIME ZONE |
n = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(25 + n+ (1 if n > 0, otherwise 0)) yyyy-mm-dd hh:mm:ss{±}hh:mm 1998-09-24 11:37:58+07:00 yyyy-mm-dd hh:mm:ss.ssss{±}hh:mm 1998-09-24 11:37:58.1234+07:00 |
INTERVAL YEAR INTERVAL YEAR (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) yy 98 yyyy 1998 |
INTERVAL YEAR TO MONTH INTERVAL YEAR (n) TO MONTH |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n+ 3) yy-mm 98-12 yyyy-mm 1998-12 |
INTERVAL MONTH INTERVAL MONTH (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n) mm 12 mmmm 0012 |
INTERVAL DAY INTERVAL DAY (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n) dd 31 dddd 0031 |
INTERVAL DAY TO HOUR INTERVAL DAY (n) TO HOUR |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 3) dd hh 31 12 dddd hh 0031 12 |
INTERVAL DAY TO MINUTE INTERVAL DAY (n) TO MINUTE |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 6) dd hh:mm 31 12:59 dddd hh:mm 0031 12:59 |
INTERVAL DAY TO SECOND INTERVAL DAY (n) TO SECOND INTERVAL DAY TO SECOND (m) INTERVAL DAY (n) TO SECOND (m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 9 + m + (1 if m > 0, 0 otherwise)) dd hh:mm:ss 31 12:59:59 dddd hh:mm:ss 0031 12:59:59:59.1234 |
INTERVAL HOUR INTERVAL HOUR (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n) hh 12 hhhh0012 |
INTERVAL HOUR TO MINUTE INTERVAL HOUR (n) TO MINUTE |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n + 3) hh:mm 12:59 hhhh:mm 0012:59 |
INTERVAL HOUR TO SECOND INTERVAL HOUR (n) TO SECOND INTERVAL HOUR TO SECOND (m) INTERVAL HOUR (n) TO SECOND (m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 6 + m + (1 if m > 0, 0 otherwise)) hh:mm:ss 12:59:59 hhhh:mm:ss.ssss 0012:59:59.1234 |
INTERVAL MINUTE INTERVAL MINUTE (n) |
n = number of digits Valid values: 1-4 Default = 2 |
CHAR(n) mm 59 mnnm 0059 |
INTERVAL MINUTE TO SECOND INTERVAL MINUTE (n) TO SECOND INTERVAL MINUTE TO SECOND (m) INTERVAL MINUTE (n) TO SECOND (m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + 3 + m + (1 if m > 0, 0 otherwise)) mm:ss 59:59 mm:ss.ssss 0059:59.1234 |
INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n,m) |
n = number of digits Valid values: 1-4 Default = 2 m = number of digits after decimal point Valid values: 0-6 Default = 6 |
CHAR(n + m + (1 if m > 0, 0 otherwise)) ss 59 ssss.ssss 0059.1234 |