FIELD - FastExport

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 above 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 above 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 above 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 SQL 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)

For more information on the DECIMAL data type, see SQL 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:

The element type is one of the following DateTime data types.

For more information on the PERIOD data type, see SQL 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.

Table 34 provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

DATE

Convert to:

CHAR(10)

Format:
Example:

yyyy/mm/dd
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 (n = 0):
Example:

hh:mm:ss
11:37:58

Format: (n = 4):
Example:

hh:mm:ss.ssss
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):
Example:

yyyy‑mm‑dd hh:mm:ss
1998‑09‑04 11:37:58

Format (n = 4):
Example:

yyyy‑mm‑dd hh:mm:ss.ssss
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):
Example:

hh:mm:ss{±}hh:mm
11:37:58‑08:00

Format (n = 4):
Example:

hh:mm:ss.ssss {±} hh:mm
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 (n = 0):
Example

yyyy‑mm‑dd hh:mm:ss{±}hh:mm
1998‑09‑24 11:37:58+07:00

Format (n = 4):
Example:

yyyy‑mm‑dd hh:mm:ss.ssss{±}hh:mm
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 (n = 2):
Example:

yy
98

Format (n = 4):
Example:

yyyy
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):
Example:

yy‑mm
98‑12

Format (n = 4):
Example:

yyyy‑mm
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):
Example:

mm
12

Format (n = 4):
Example:

mmmm
0012

INTERVAL DAY

INTERVAL DAY (n)

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

Convert to:

CHAR(n)

Format (n = 2):
Example:

dd
31

Format (n = 4):
Example:

dddd
0031

INTERVAL DAY TO HOUR

INTERVAL DAY (n) TO HOUR

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

Convert to:

CHAR(n + 3)

Format (n = 2):
Example:

dd hh
31 12

Format (n = 4):
Example:

dddd hh
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):
Example:

dd hh:mm
31 12:59

Format (n = 4):
Example:

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)

Where

Convert to:

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

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

dd hh:mm:ss
31 12:59:59

Format (n = 4, m = 4):
Example:

dddd hh:mm:ss.ssss
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):
Example:

hh
12

Format (n = 4):
Example:

hhhh
0012

INTERVAL HOUR TO MINUTE

INTERVAL HOUR (n) TO MINUTE

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

Convert to:

CHAR(n + 3)

Format (n = 2):
Example:

hh:mm
12:59

Format (n = 4):
Example:

hhhh:mm
0012:59

INTERVAL HOUR TO SECOND

INTERVAL HOUR (n) TO SECOND

INTERVAL HOUR TO SECOND (m)

INTERVAL HOUR (n) TO SECOND (m)

Where

Convert to:

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

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

hh:mm:ss
12:59:59

Format (n = 4, m = 4):
Example:

hhhh:mm:ss.ssss
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):
Example:

mm
59

Format (n = 4):
Example:

mmmm
0059

INTERVAL MINUTE TO SECOND

INTERVAL MINUTE (n) TO SECOND

INTERVAL MINUTE TO SECOND (m)

INTERVAL MINUTE (n) TO SECOND (m)

Where

Convert to:

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

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

mm:ss
59:59

Format (n = 4, m = 4):
Example:

mmmm:ss.ssss
0059:59.1234

INTERVAL SECOND

INTERVAL SECOND (n)

INTERVAL SECOND (n,m)

Where

Convert to:

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

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

ss
59

Format (n = 4, m = 4):
Example:

ssss.ssss
0059.1234