U sage Notes | FIELD - Usage Notes

The following table describes the things to consider when using the FIELD command.

FIELD Command Usage Notes
Topic	Usage Notes
Command Placement and Frequency	A FIELD command must be preceded by a LAYOUT command. One or more FIELD commands, or a combination of FIELD command and FILLER command, define the composition of the input data record to supply values for the USING modifier of the SELECT statement.
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 FastExport layout definition for the sample SOURCE_TABLE table: .FIELD EMP_ID * INTEGER; .FIELD EMP_NO * BYTEINT; .FIELD COL003 * VARCHAR(47); .FIELD COL004 * VARCHAR(17); .FIELD COL005 * VARCHAR(25); In the previous 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 samples of data 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 the previous 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 are two samples of data for the COL004 column: Sample data 1: (-123.45,888.10) Sample data 2: (+123.45,-888.10) In the previous 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 are 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 look like 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 DECIMAL Data Types	The following input length and field descriptions apply for the DECIMAL data type specifications which make in the datadesc parameter. DECIMAL (x) and DECIMAL (x,y) Length: 1, 2, 4, 8, or 16 bytes (network); packed decimal (mainframe) Description: 128-bit double precision floating point For more information on the DECIMAL data type, see Teradata Vantage™ - Data Types and Literals, B035-1143.
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: BEGIN (the beginning element) END (the ending element) The element type is one of the following DateTime data types. 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.
Using ANSI/SQL DateTime Data Types	When the DATEFORM command is used to specify ANSIDATE as the DATE data type, FastExport 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 FastExport FIELD command. The following table provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

The following are descriptions of the ANSI/SQL Date Time Specifications.

DATE

Convert to: CHAR(10)

Format: yyy/mm/dd

Example: 1998/01/01

TIME

TIME (n)

Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(8 +n + (1 if n> 0, otherwise 0))

Format: hh:mm:ss

Example: 11:37:58

Format: hh:mm:ss.ssss

Example: 11:37:58.1234

TIMESTAMP

TIMESTAMP (n)

Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(19 + n + (1 if n > 0, otherwise 0))

Format (n=0): yyy-mmm-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

Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(14 + n + (1 if n > 0, otherwise 0))

Format (n = 0): hh:mm:ss{±}hh:mm

Example: 11:37:58-08:00

Format: hh:mm:ss.ssss{±}hh:mm

Example: 11:37:58.1234-08:00

TIMESTAMP WITH TIME ZONE

TIMESTAMP (n) WITH TIME ZONE

Where n is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(25 + n+ (1 if n > 0, otherwise 0))

Format: 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

INTERVAL YEAR

INTERVAL YEAR (n)

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n)

Format: yy

Example: 98

Format (n = 4): yyyy

Example: 1998

INTERVAL YEAR TO MONTH

INTERVAL YEAR (n) TO MONTH

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n+ 3)

Format (n= 2): yy-mm

Example: 98-12

Format (n= 4): yyyy-mm

Example: 1998-12

INTERVAL MONTH

INTERVAL MONTH (n)

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n)

Format (n = 2): mm

Example: 12

Format (n = 4): mmmm

Example: 0012

INTERVAL DAY

INTERVAL DAY (n)

Where n is the number of digits, 1 through 4. (Default = 2.)

Conver to: CHAR(n)

Format (n = 2): dd

Example: 31

Format (n = 4): dddd

Example: 0031

INTERVAL DAY TO HOUR

INTERVAL DAY (n) TO HOUR

Wheren is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n + 3)

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

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n + 6)

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)

Where:

n is the number of digits, 1 through 4. (Default = 2.)
m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(n + 9 + m + (1 if m > 0, 0 otherwise))

Format (n = 2, m = 0): dd hh:mm:ss

Example: 31 12:59:59

Format (n = 4, m = 4): dddd hh:mm:ss.ssss

Example: 0031 12:59:59:59.1234

INTERVAL HOUR

INTERVAL HOUR (n)

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n)

Format (n = 2): hh

Example: 12

Format (n = 4): hhhh

Example: 0012

INTERVAL HOUR TO MINUTE

INTERVAL HOUR (n) TO MINUTE

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to:

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)

Where:

n is the number of digits, 1 through 4. (Default = 2.)
m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(n + 6 + m + (1 if m > 0, 0 otherwise))

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)

Where n is the number of digits, 1 through 4. (Default = 2.)

Convert to: CHAR(n)

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)

Where:

n is the number of digits, 1 through 4. (Default = 2.)
m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(n + 3 + m + (1 if m > 0, 0 otherwise))

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)

Where:

n is the number of digits, 1 through 4. (Default = 2.)
m is the number of digits after the decimal point, 0 through 6. (Default = 6.)

Convert to: CHAR(n + m + (1 if m > 0, 0 otherwise))

Format (n = 2, m = 0): ss

Example: 59

Format (n = 4, m = 4): ssss.ssss

Example: 0059.1234

U sage Notes | FIELD - Usage Notes - FastExport

Teradata® FastExport Reference - 20.00