The transform group functionality for one-dimensional and multidimensional ARRAY/VARRAY UDTs is generated automatically by Teradata Database.
- 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 | ||
|
The array tosql should have the following syntax.
where:
The array transformed fromsql has the following format. --(I).9(F) where:
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:
|
≤ 4 |
| SMALLINT | ±n
where:
|
≤ 6 |
| INTEGER | ±n
where:
|
≤ 11 |
| BIGINT | ±n
where:
|
≤ 20 |
|
The array transformed tosql must have one of the following formats.
where:
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.
|
≤ 22 for these types.
|
|
≤ 40 for these types.
|
|
| 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) |
|
| TIME(n) WITH TIME ZONE |
HH:MI:SS.S(F)Z |
|
| 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)) |
|
| PERIOD(TIME(n) WITH TIME ZONE) |
(HH:MI:SS.S(F)Z, HH:MI:SS.S(F)Z) |
|
| PERIOD(TIMESTAMP(n) |
(YYYY-MM-DDBHH:MI:SS.S(F), YYYY-MM-DDBHH:MI:SS.S(F)) |
|
| PERIOD(TIMESTAMP(n) WITH TIME ZONE) |
(YYYY-MM-DDBHH:MI:SS.S(F)Z, YYYY-MM-DDBHH:MI:SS.S(F)Z) |
|
- 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.