The following table describes the things to consider when using the FIELD command.
| Topic | Usage Notes |
|---|---|
| Changing the Data Type is Not Allowed | The datadesc parameter cannot be used to change the data type from character to decimal or period when redefining an input record field in fieldname1. Doing so causes the Teradata MultiLoad job to abort with an error message. |
| Intermixing Commands | Intermixing one or more FIELD commands with TABLE and FILLER commands is allowed. All of these commands must follow a LAYOUT command. |
| NULLIF Performance | Use the
following:... .FIELD fc * CHAR(5); .FIELD fi * INTEGER; ... .DML LABEL ins; INSERT INTO tbl1 VALUES (...,NULLIF(:fc,'empty'), NULLIF(:fi,0),...); And, in the more complex situation use: ... .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,...);or, use: ... .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,...); |
| Parameter Evaluation Sequence | If both NULLIF and DROP LEADING/TRAILING BLANKS/NULLS are specified in the same FIELD command, Teradata MultiLoad evaluates the DROP clause after the NULLIF clause. For example, if the input for field1 is 'x' in the following FIELD command, the NULLIF expression would evaluate to false because the leading blanks are not dropped before the NULLIF evaluation: .FIELD FIELD1 * CHAR (5) NULLIF FIELD1 = 'x' DROP LEADING BLANKS; |
| Specifying Decimal Data Types | The following is the input length and field description for the decimal data type specifications which can be made in the datadesc parameter: DECIMAL(x) and DECIMAL(x,y)
For more information on the DECIMAL data type, see Teradata Vantage™ - Data Types and Literals, B035-1143. |
| Geospatial Types | Teradata MultiLoad does not support geospatial data represented by LOBs. maximum 64000 |
| Specifying ARRAY Data Types | A column that is defined as an ARRAY data type in a Teradata table must be specified as a VARCHAR data type in the FIELD command. The external representation for an ARRAY data type is VARCHAR. The following is a sample Teradata table definition that includes a one-dimensional ARRAY data type for the COL003 column: CREATE SET TABLE SOURCE_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
EMP_ID INTEGER,
EMP_NO BYTEINT,
COL003 SYSUDTLIB.PHONENUMBERS_ARY,
COL004 SYSUDTLIB.DECIMAL_ARY,
COL005 SYSUDTLIB.INTEGER_ARY)
UNIQUE PRIMARY INDEX ( EMP_ID );
The following is a sample definition for the PHONENUMBERS_ARY data type: CREATE TYPE PHONENUMBERS_ARY AS CHAR(10) CHARACTER SET LATIN ARRAY [2]; The following is a sample definition for the DECIMAL_ARY data type: CREATE TYPE DECIMAL_ARY AS DECIMAL(5,2) ARRAY[2]; The following is a sample definition for the INTEGER_ARY data type: CREATE TYPE INTEGER_ARY AS INTEGER ARRAY[2]; The following is a sample Teradata MultiLoad FIELD definition for the sample SOURCE_TABLE: .FIELD EMP_ID * INTEGER; .FIELD EMP_NO * BYTEINT; .FIELD COL003 * VARCHAR(47); .FIELD COL004 * VARCHAR(17); .FIELD COL005 * VARCHAR(25); In this example, the COL003 column is defined as VARCHAR(47), because it's the maximum representation for the COL003 column in the table. The following is the calculation for the maximum representation for the COL003 column: 1 byte for the left parenthesis +1 byte for the single quote +10 to 20 bytes for the first element +1 byte for the single quote +1 byte for the comma +1 byte for the single quote +10 to 20 bytes for the second element +1 byte for the single quote +1 byte for the right parenthesis ---- 47 bytes The following are two sample data sets for the COL003 column:Sample data 1: ('3105551234','3105551234') Sample data 2: ('''''''''''''''''''''','''''''''''''''''''''') Sample data 1 contains 2 elements of phone numbers. Sample data 2 contains 2 elements of all single quote characters. In this example, the COL004 column is defined as VARCHAR(17), because it's the maximum representation for the COL004 column in the table. The following is the calculation for the maximum representation for the COL004 column: 1 byte for the left parenthesis +1 to 7 bytes for the first element +1 byte for the comma +1 to 7 bytes for the second element +1 byte for the right parenthesis ---- 17 bytes The following is two samples of data for the COL004 column: Sample data 1: (-123.45,888.10) Sample data 2: (+123.45,-888.10) In the this example, the COL005 column is defined as VARCHAR(25), because it's the maximum representation for the COL005 column in the table. The following is the calculation for the maximum representation for the COL005 column: 1 byte for the left parenthesis +1 to 11 bytes for the first element + 1 byte for the comma + 1 to 11 bytes for the first element + 1 byte for the right parenthesis ---- 25 bytes The following is two samples of data for the COL005 column: Sample data 1: (-2147483648,+2147483647) Sample data 2: (0,0) Use the Teradata SQL “HELP TYPE” command to find out the maximum length for the ARRAY data type. For example, the information for the sample PHONENUMBERS_ARY, DECIMAL_ARY, and INTEGER_ARY ARRAY data types can be as follows: help type PHONENUMBERS_ARY;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
Name PHONENUMBERS_ARY
Internal Type A1
External Type CV
Max Length 47
Array(Y/N) Y
Dimensions 1
Element Type CF
UDT Name ?
Array Scope [1:2]
Total Digits ?
Fractional Digits ?
Contains Lob N
Ordering F
Ordering Category M
Ordering Routine LOCAL
Cast N
Transform Y
Method Y
Char Type 1
HELP TYPE DECIMAL_ARY;
*** Help information returned. One row.
*** Total elapsed time was 1 second.
Name DECIMAL_ARY
Internal Type A1
External Type CV
Max Length 17
Decimal Total Digits ?
Decimal Fractional Digits ?
Contains Lob N
Ordering F
Ordering Category M
Ordering Routine LOCAL
Cast N
Transform Y
Method Y
Char Type 1
Array(Y/N) Y
Dimensions 1
Element Type D
UDT Name ?
Array Scope [1:2]
HELP TYPE INTEGER_ARY;
*** Help information returned. One row.
*** Total elapsed time was 1 second.Name INTEGER_ARY
Internal Type A1
External Type CV
Max Length 25
Decimal Total Digits ?
Decimal Fractional Digits ?
Contains Lob N
Ordering F
Ordering Category M
Ordering Routine LOCAL
Cast N
Transform Y
Method Y
Char Type 1
Array(Y/N) Y
Dimensions 1
Element Type I
UDT Name ?
Array Scope [1:2]
As indicated in the returned information from the HELP TYPE command, the maximum length for the sample PHONENUMBERS_ARY ARRAY data type is 47 bytes. The maximum length for the sample DECIMAL_ARY ARRAY data type is 17 bytes. The maximum length for the sample INTEGER_ARY ARRAY data type is 25 bytes. For more information about the external representations for the ARRAY data type, see Teradata Vantage™ - Data Types and Literals, B035-1143. |
| Specifying Graphic Data Types | Following is the input length and field
description for the graphic data type specifications can be made in the
datadesc parameter: GRAPHIC(n)
VARGRAPHIC(n)
LONG VARGRAPHIC
LONG VARGRAPHIC also implies VARGRAPHIC
(16000). Range is 0 to 16000 in a 32,000-byte field. For both VARGRAPHIC
and LONG VARGRAPHIC, m, a value occupying the first 2 bytes of the input
data, is the length of the input in bytes, not characters. Each multibyte
character set character is 2 bytes.
|
| Specifying Period Data Types | A period is an anchored duration. It represents a set of contiguous time granules within that duration. A period is implemented using a Period data type. Each period consists of two elements:
The element type is one of the following DateTime data types. They are DATE, TIME, and TIMESTAMP. The five PERIOD data types include:
For more information on the PERIOD data type, see Teradata Vantage™ - Data Types and Literals, B035-1143. |
| Specifying Relevant Fields | Because all fields specified by FIELD commands are sent to the database, only specify those fields that are relevant to the Teradata MultiLoad task. |
| startpos Specification | The specified starting position:
Therefore, the first data position of the input record is position 1. For more information about the CONTINUEIF and INDICATORS specifications, see the LAYOUT command description. |
| Using ANSI/SQL DateTime Data Types | When the DATEFORM command is used to specify ANSIDATE as the DATE data type, Teradata MultiLoad internally converts each DATE field 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/field names in a Teradata MultiLoad FIELD command. The following table in this section (ANSI/SQL DateTime Specifications) provides the conversion specifications and format examples for each ANSI/SQL DateTime specification. |
The following table describes Date/Time information to consider when using the FIELD command.
For the latest conversion specifications and format examples for each ANSI/SQL DateTime specification, see the INTERVAL type description in Teradata Vantage™ - Data Types and Literals, B035-1143.
| Data Type | Variable Definition | Conversion Example |
|---|---|---|
| INTERVAL YEAR INTERVAL YEAR (n) |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): yy Example: 98 Format: (n = 4): yyyy Example: 1998 |
| INTERVAL YEAR TO MONTH INTERVAL YEAR (n) TO MONTH |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n
+ 4) Format (n = 2): yy-mm Example: 98-12 Format: (n = 4): yyyy-mm Example: 1998-12 |
| INTERVAL MONTH INTERVAL MONTH (n) |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): mm Example: 12 Format: (n = 4): mmmm Example: 0012 |
| INTERVAL DAY INTERVAL DAY (n) |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): dd Example: 31 Format: (n = 4): dddd Example: 0031 |
| INTERVAL DAY TO HOUR INTERVAL DAY (n) TO HOUR |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n
+ 4) Format (n = 2): dd hh Example: 31 12 Format: (n = 4): dddd hh Example: 0031 12 |
| INTERVAL DAY TO MINUTE INTERVAL DAY (n) TO MINUTE |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n
+ 7) Format (n = 2): dd hh:mm Example: 31 12:59 Format: (n = 4): dddd hh:mm Example: 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
+ 10+ m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): hh:mm:ss Example: 12:59:59 Format: (n = 4, m = 4): hhhh:mm:ss.ssss Example: 0012:59:59.1234 |
| INTERVAL HOUR INTERVAL HOUR (n) |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format: (n = 2): hh Example: 12| Format: (n = 4): hhhh Example: 0012 |
| INTERVAL HOUR TO MINUTE INTERVAL HOUR (n) TO MINUTE |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n
+ 4) Format: (n = 2): hh:mm Example: 12:59 Format: (n = 4): hhhh:mm Example: 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 the decimal point Valid values: 0-6 Default = 6 |
CHAR(n
+ 7+ m + (1 if m > 0, otherwise 0)) Format: (n = 2, m = 0): hh:mm:ss Example: 12:59:59 Format: (n = 4, m = 4): hhhh:mm:ss.ssss Example: 0012:59:59.1234 |
| INTERVAL MINUTE INTERVAL MINUTE (n) |
n =
number of digits Valid values: 1-4 Default = 2 |
CHAR(n+1) Format (n = 2): mm Example: 59 Format: (n = 4): mmmm Example: 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
+ 4 + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): mm:ss Example: 59:59 Format: (n = 4, m = 4): mmmm:ss.ssss Example: 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
+ 1 + m + (1 if m > 0, otherwise 0)) Format (n = 2, m = 0): ss Example: 59 Format: (n = 4, m = 4): ssss.ssss Example: 0059.1234 |
| 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)) Format (n = 0): yyyy-mm-dd hh:mm:ss Example: 1998-09-04 11:37:58 Format (n = 4): yyyy-mm-dd hh:mm:ss.ssss Example: 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)) Format (n = 0): hh:mm:ss{±}hh:mm Example: 11:37:58-08:00 Format (n = 4): hh:mm:ss.ssss{±}hh:mm Example: 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)) Format (n = 0): yyyy-mm-dd hh:mm:ss{±}hh:mm Example: 1998-09-24 11:37:58+07:00 Format (n = 4): yyyy-mm-dd hh:mm:ss.ssss{±}hh:mm Example: 1998-09-24 11:37:58.1234+07:00 |