An aggregate function for use with ARRAY data types that allows arrays to be created as part of the SELECT list of a query.
the name of the database where the function is located.
an expression that evaluates to an ARRAY element type.
an expression that evaluates to a Teradata data type that can be compared.
that the results are to be ordered in ascending sort order.
If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.
The default order is ASC.
that the results are to be ordered in descending sort order.
If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.
one of the following:
ARRAY_AGG for a one-dimensional (1-D) ARRAY data type is ANSI SQL:2011 compliant. However, ARRAY_AGG includes an additional parameter containing an ARRAY expression of the target ARRAY type, and this is a Teradata extension to the ANSI standard. This was done to eliminate ambiguity since you can define multiple ARRAY types that have the same element data type.
ARRAY_AGG for a multidimensional (n-D) ARRAY data type is a Teradata extension to the ANSI SQL:2011 standard.
ARRAY_AGG accepts two arguments. The first argument is an expression evaluating to a data type that is currently being used as the element value of a defined ARRAY data type. The second argument is an expression evaluating to a defined ARRAY data type whose element type matches that of the first argument.
The data type information from the array_expression which resolves to an ARRAY data type is used to set the return type of ARRAY_AGG.
Result Value and Rules
ARRAY_AGG returns an ARRAY type value whose elements are the aggregated set of element expression values that were passed in as the first parameter. For an n-D ARRAY result value, the value_expression values that are aggregated into the array are entered in row-major order.
If you specify the ORDER BY clause in the first argument, then the values input to the resultant array are ordered by the column values specified in this clause. The following rules apply to the ORDER BY clause of ARRAY_AGG:
Because you can pass any array_expression, the ARRAY type value that you pass may or may not contain any initialized elements. However, ARRAY_AGG ignores any initialized elements in the array_expression. The array_expression is used only for providing the data type of the result value.
Recommendation: Pass a blank instance of the desired ARRAY type value for this parameter, such as NEW myArray().
If the array_expression argument passed to ARRAY_AGG is a NULL literal, then an error is returned. If the array_expression argument can be resolved to an ARRAY data type, then the function executes normally, even if the data contains a NULL.
If the value_expression argument for the current row being processed evaluates to NULL, then that expression will not be eliminated from the list of values being aggregated, but will be used to set the appropriate ARRAY element value just as would be done with a non-NULL element. This follows the ANSI SQL:2011 standard and is noted in the standard as a difference between ARRAY_AGG and other aggregate functions.
ARRAY_AGG is created with a CLASS AGGREGATE value of 64000, meaning that the intermediate aggregate storage will be allocated at that size. Since the intermediate aggregate storage is a fixed-length value, it may be possible in cases of processing a very large input set that this buffer may overflow. All aggregate UDFs have this limitation.
This example takes data from a source table which records a phone number associated with an employee and inserts it into a new table which records phone numbers in an ARRAY type. In many cases, an employee may have multiple phone numbers, such as office phone, mobile phone, home phone, etc. A better way to represent this type of data is with a 1-D ARRAY type which records multiple phone numbers. This reduces the number of rows in the table.
Note: the same type of behavior can be illustrated with an n-D ARRAY type. The only difference is that the storage of elements is done in row-major order.
The following statement creates a 1-D ARRAY type that can hold up to 100 phone number values:
CREATE TYPE emp_phone_array AS VARCHAR(14) ARRAY;
The following source table contains one row per employee phone number.
CREATE SET TABLE employee
SELECT * FROM employee;
emp_id emp_name emp_phone
-------- ----------- -------------
1 Beth (619) 619-6190
1 Beth (619) 620-6200
1 Beth (619) 720-7200
2 Greg (858) 858-8580
2 Greg (858) 859-8590
2 Greg (858) 860-8600
3 Louise (421) 421-4210
3 Louise (421) 422-4220
3 Louise (421) 423-4230
The following target table contains one row per employee and stores all phone numbers associated with an employee in an ARRAY type.
CREATE SET TABLE employeePhoneInfo
INSERT INTO employeePhoneInfo
SELECT emp_id, emp_name,
ARRAY_AGG(emp_phone, NEW emp_phone_array())
FROM employee GROUP BY emp_id,emp_name
WHERE emp_id < 100;
SELECT * FROM employeePhoneInfo;
emp_id emp_name emp_phone
--------- ----------- ---------
1 Beth ( (619) 619-6190, (619) 620-6200, (619) 720-7200 )
2 Greg ( (858) 858-8580, (858) 859-8590, (858) 860-8600 )
3 Louise ( (421) 421-4210, (421) 422-4220, (421) 423-4230 )