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 DEFINE SCHEMA statement.
The external representation for an ARRAY data type is VARCHAR.
Example
Here 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 );
Example
Here is a sample definition for the PHONENUMBERS_ARY data type:
CREATE TYPE PHONENUMBERS_ARY AS CHAR(10) CHARACTER SET LATIN ARRAY [2];
Example
Here is a sample definition for the DECIMAL_ARY data type:
CREATE TYPE DECIMAL_ARY AS DECIMAL(5,2) ARRAY[2];
Example
Here is a sample definition for the INTEGER_ARY data type:
CREATE TYPE INTEGER_ARY AS INTEGER ARRAY[2];
Example
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
+ 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
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
+ 1 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
+ 1 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 Appendix B: “External Representation for UDTs" in SQL Data Types and Literals.