Example 5 – Specifying ARRAY Data Types - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
17.00
Published
August 31, 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
zae1544831938751.ditamap
dita:ditavalPath
tvt1507315030722.ditaval
dita:id
B035-2445
lifecycle
previous
Product Category
Teradata Tools and Utilities

Here is a sample Teradata PT schema definition for the sample SOURCE_TABLE table:

   DEFINE SCHEMA EMPLOYEE_SCHEMA
   DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
   (
      EMP_ID   INTEGER,
      EMP_NO   BYTEINT,
      COL003   VARCHAR(47),
      COL004   VARCHAR(17),
      COL005   VARCHAR(25)
   );

In the above example, the COL003 column is defined as VARCHAR(47), because it is the maximum representation for the COL003 column in the table.

Here is the calculation for the maximum representation for the COL003 column:

1 byte for the left parenthesis

+ I 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

Here are 2 sample 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 is the maximum representation for the COL004 column in the table.

Here 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

+ byte for the right parenthesis

---------

17 bytes

Here are 2 sample 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 is the maximum representation for the COL005 column in the table.

Here 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

+ byte for the comma

+ 1 to 11 bytes for the first element

+ 1 byte for the right parenthesis

---------

25 bytes

Here are 2 sample data for the COL005 column:

Sample data 1: (-2147483648,+2147483647)
Sample data 2: (0,0)

Use the Teradata SQL HELP TYPE statement to determine the maximum length for the ARRAY data type, as given by the value returned by MaxLength.

For more information about the external representations for the ARRAY data type, see “External Representation for UDTs" in Teradata Vantage™ - Data Types and Literals, B035-1143.