Transform Input/Output Strings for ARRAY/VARRAY UDTs - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

The transform group functionality for one-dimensional and multidimensional ARRAY/VARRAY UDTs is generated automatically by Teradata Database.

Teradata Database uses the same string format for one-dimensional and multidimensional ARRAY/VARRAY UDTs for:
  • The tosql transform input parameter
  • The fromsql transform output value

The format of the transformed output in a VARCHAR string is a string of each array element value, referred to as the transformed value string, separated by a comma and delimited by parentheses as indicated below. Assuming the array has n elements:

(<element1>,<element2>, … <elementn>)

where:

Syntax element … Specifies the …
<element1> first element in the array.
<element2> second element in the array.
<elementn> n th element in the array.

Call this string the transformed value string. The format for each element of the array according to its SQL data type is listed in the following table.

Data Type Format Size (bytes)
CHARACTER(n) CHARACTER SET server_character_set string

While the string itself is a character string of length k, the value for k is the length of the characters and is encoded in the declared server character set.

k + 2
VARCHAR(n CHARACTER SET server_character_set
  • NUMERIC(n,m)
  • DECIMAL(n,m)
  • NUMBER(n) (exact type)

    For this type, m = 0.

The array tosql should have the following syntax.
  • ±n
  • ±.n
  • ±n.n
where:
  • ± is an optional sign.

    The default is +.

  • n is any valid integer number.

The array transformed fromsql has the following format.

--(I).9(F)
where:
  • I = n-m
  • F = m

When m = 0 for NUMBER(n) and NUMBER(n,m), the array transformed fromsql must have the following format, indicating that the decimal point is not shown.

--(I)
n+2
BYTEINT ±n
where:
  • ± is an optional sign.

    The default is +.

  • n is any valid integer number between -128 and 127.
4
SMALLINT ±n
where:
  • ± is an optional sign.

    The default is +.

  • n is any valid integer number between -32768 and 32767.
6
INTEGER ±n
where:
  • ± is an optional sign.

    The default is +.

  • n is any valid integer number between

    -2147483648 and 2147483647.

11
BIGINT ±n
where:
  • ± is an optional sign.

    The default is +.

  • n is any valid integer number between

    -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

20
  • REAL
  • FLOAT
  • DOUBLE PRECISION
The array transformed tosql must have one of the following formats.
  • ± nm
  • ± n.E±m
  • ± .nm
  • ± n .nm
where:
  • ± is an optional sign.

    The default is +.

  • n is any valid integer number representing the whole and, optionally, fractional component of the mantissa.

    For the FLOAT, REAL, and DOUBLE PRECISION types the number of digits cannot exceed 15, excluding leading zeros in the whole component of the mantissa.

    For the NUMBER, NUMBER(*), and NUMBER(*, m) types, the number of digits cannot exceed 40, excluding leading zeros in the whole component of the mantissa.

  • E is a symbol indicating that the digits that follow in m are the exponent of the number.
  • m is any valid integer number representing the exponent for the number.

    The number of digits that m contains cannot exceed 3, including leading zeros.

For the REAL, FLOAT, and DOUBLE PRECISION types the transformed fromsql has the following format.

-9.99999999999999E-999

For the NUMBER, NUMBER(*), and NUMBER(*, m) types the transformed fromsql has the following format.

FN9
The the definition of the FN9 format is as follows.
  • If the data fits in 64 characters, then display it as is in character format, discarding any leading zeros.
  • If the data does not fit in 64 characters, use the exponent notation to display the data.

    The format for exponent notation is FNE.

22

for these types.

  • REAL
  • FLOAT
  • DOUBLE PRECISION
  • NUMBER
  • NUMBER(*)
  • NUMBER(*, m)
≤ 40

for these types.

  • NUMBER
  • NUMBER(*)
  • NUMBER(*, m)
BYTE(n) X(2n)

This is a string of hexadecimal digits of length 2n.

2n
VARBYTE(n) X(2k)

This is a string of hexadecimal digits of length 2k, where k is the number of bytes in the string.

2k
DATE
YYYY-MM-DD
10
TIME(n)
HH:MI:SS.S(F)
  • If n=0, size = 8
  • If n>0, size = 9+n
TIME(n) WITH TIME ZONE
HH:MI:SS.S(F)Z
  • If n=0, size = 14
  • If n>0, size = 15+n
TIMESTAMP(n)
YYYY-MM-DDBHH:MI:SS.S(F)
If n=0, size = 19
If n>0, size = 20+n
TIMESTAMP(n) WITH TIME ZONE
YYYY-MM-DDBHH:MI:SS.S(F)Z
If n=0, size = 25
If n>0, size = 26+n
INTERVAL SECOND(n, m) -s(n) if no fractional precision is defined If no fractional precision defined,

size = n + 1

-s(n).s(m) if a fractional precision is defined as m If a fractional precision is defined, size = n + m + 2
INTERVAL MINUTE(n)
-m(n)
n + 1
INTERVAL MINUTE(n) TO SECOND(m) -m(n):ss if no fractional precision is defined. If no fractional precision defined,

size = n + 4

-m(n):ss.s(m) if a fractional precision is defined as m. If a fractional precision is defined as m, size = n + m + 5
INTERVAL HOUR(n)
-h(n)
n + 1
INTERVAL HOUR(n) TO MINUTE
-h(n):mm
n + 4
INTERVAL HOUR(n) TO SECOND(m) -h(n):mm:ss if no fractional precision is defined. If no fractional precision defined,

size = n + 7

-h(n) :mm:ss.s(m) if a fractional precision is defined as m. If a fractional precision is defined as m, size = n + m + 8
INTERVAL DAY(n)
-d(n)
n + 1
INTERVAL DAY(n) TO SECOND(m) -d(n) hh:mm:ss if no fractional precision is defined. If no fractional precision defined, size = n + 10
-d(n) hh:mm:ss.s(m) if a fractional precision is defined as m. If a fractional precision is defined as m,

size = n+m+11

INTERVAL DAY(n) TO MINUTE
-d(n) hh:mm
n + 7
INTERVAL DAY(n) TO HOUR
-d(n) hh
n + 4
INTERVAL MONTH(n)
-m(n)
n + 1
INTERVAL YEAR(n)
-y(n)
n + 1
INTERVAL YEAR(n) TO MONTH
-y(n)-mm
n + 4
UDT (distinct form) Same as the underlying predefined data type.  
UDT (structured form) Assume that the structured UDT has k attributes. Its format is as follows.
(attribute1, attribute2, …, attributek)

where the attributes are separated by a comma character, are in their defined order, and are delimited by parentheses.

If an array element is a nested structured UDT, it is returned in row order, depth first. A nested attribute must be delimited by parenthesis characters.

For example, assume that attribute2 is a UDT with two attributes, attribute2_1 and attribute2_2. Its format is as follows.

(attribute1,(attribute2_1, attribute2_2), … , attributek)
 
PERIOD(DATE)
(YYYY-MM-DD, YYYY-MM-DD)
24
PERIOD(TIME(n))
(HH:MI:SS.S(F), HH:MI:SS.S(F))
  • If n=0, size=20
  • If n>0, size=2n+22
PERIOD(TIME(n) WITH TIME ZONE)
(HH:MI:SS.S(F)Z, HH:MI:SS.S(F)Z)
  • If n=0, size=32
  • If n>0, size=2n+34
PERIOD(TIMESTAMP(n)
(YYYY-MM-DDBHH:MI:SS.S(F), YYYY-MM-DDBHH:MI:SS.S(F))
  • If n=0, size=42
  • If n>0, size=2n+44
PERIOD(TIMESTAMP(n) WITH TIME ZONE)
(YYYY-MM-DDBHH:MI:SS.S(F)Z, YYYY-MM-DDBHH:MI:SS.S(F)Z)
  • If n=0, size=54
  • If n>0, size=2n+56
Note the following about the information in the preceding table:
  • There is no support for transforms for the following data elements for either one-dimensional or multidimensional ARRAY/VARRAY data types. You cannot use these element types to create ARRAY/VARRAY data types:
    • BLOB
    • CLOB
    • Geospatial
  • If the element type is a primitive data type other than CHARACTER or VARCHAR, or is a PERIOD or UDT with no CHARACTER or VARCHAR attributes, the definition of the array constructor transformed value string is VARCHAR(64000) CHARACTER SET LATIN.

    If the size of the transformed value string is larger than 64K when the ARRAY/VARRAY type is being created, the request aborts and Teradata Database returns an error to the requestor.

  • If the element type is CHARACTER or VARCHAR CHARACTER SET LATIN, or a UDT with CHARACTER or VARCHAR attributes whose server character set is exclusively Teradata Latin, the definition of the array transformed value string is VARCHAR(64000) CHARACTER SET LATIN.

    If the element type is CHARACTER or VARCHAR whose server character set is not Teradata LATIN, or if it is a UDT with any CHARACTER or VARCHAR attribute is not Teradata LATIN, the definition of the array transformed value string is VARCHAR(32000) CHARACTER SET UNICODE.

    If the maximum size of the transformed value string is larger than the defined length when the ARRAY/VARRAY type is being created, the request aborts and Teradata Database returns an error to the requestor.

  • If the element type is CHARACTER or VARCHAR or a UDT with CHARACTER or VARCHAR attributes, and its array elements contain many embedded apostrophe characters, you must specify an extra quote to distinguish the embedded apostrophe characters.

    This limits the total size of the transform string that can be output when selecting the array column because Teradata Database counts the embedded quote characters against the size of the transform string.

    If your array elements contain any embedded apostrophe characters, Teradata Database outputs those characters when the ArrayTransformsOff flag is set to N, using the fromsql transform.

    In the worst case, which is a string with all apostrophe characters embedded, this reduces the size of the maximum transform string by half. Therefore, if the total size of the transform string that could be generated for an ARRAY/VARRAY type exceeds the maximum row size, Teradata Database aborts the CREATE TYPE request for that ARRAY/VARRAY type and returns an error to the requestor.

  • Teradata Database does not display uninitialized elements.
  • If an element value is initialized, all of the elements preceding it must have also been initialized either to a value or to null.
  • You must indicate a null element using a null literal, which could be 'NULL', 'Null', 'null'. The case is insensitive.

    If the defined server character set is something other than Teradata LATIN, the null element is indicated by the corresponding encoding of a NULL literal in the defined server character set. This also applies for a structured UDT null and for a structured UDT with a null attribute.

  • Overflow avoidance:

    The size of an array transformed value string needs to be within the size limit of a VARCHAR, which is 64K for the Teradata LATIN server character set. The limit is different if the server character set is something other than Teradata LATIN. See VARCHAR Data Type for specific details of the size limit of VARCHAR data with a server character set other than Teradata LATIN.

    If the size of an array transformed value string is greater than the size limit for a VARCHAR type when the ARRAY/VARRAY type is being created, the request aborts and Teradata Database returns an error to the requestor.

  • Teradata Database ignores any space, new line, or tab character, either before or after the comma character, or before the last apostrophe character or after the first apostrophe character.