Examples | ARRAY COMPARISON Function | VantageCloud Lake - Example: Querying 1-D ARRAY Data Type and Table - Teradata Vantage

Consider the following 1-D ARRAY data type and table.

CREATE TYPE item_price AS DECIMAL(7,2) ARRAY[10];
CREATE TABLE inventory (itemkind INTEGER,
                        regular_price item_price,
                        sale_price item_price);

Assume the following element values for the sale_price and regular_price arrays:

sale_price[1:10] =  50, 100,  200,  90,  250, 550, 200, 200,  50,  75
regular_price[1:10] = 100, 300, 230, 110, 500, 550, 200, 400, 100, 150

The following query returns a 1-D ARRAY with scope reference [1:10] of BYTEINT element type. During evaluation, each element within the specified scope, [5:10], in the regular_price array is compared to the corresponding element of the sale_price array using the relational function ARRAY_GT. The resulting values in the output array are 0 or a non-zero number for the elements within the scope reference, and NULL for all other elements of the array.

SELECT ARRAY_GT(regular_price, sale_price, 5,10)
FROM inventory;

The output from the query is a 1-D ARRAY with the following values:

output_array[1:10] = [ NULL, NULL, NULL, NULL, 1, 0, 0, 1, 1, 1]

The following query returns a 1-D ARRAY with scope reference [1:10] of type item_price. During evaluation, each element within the specified scope [5:10] in the regular_price array is compared to the corresponding element of the sale_price array using the relational function ARRAY_GT. The resulting ARRAY of element type BYTEINT value (0 or non-zero number) is multiplied by the corresponding element value of the regular_price array.

SELECT ARRAY_MUL(regular_price, ARRAY_GT(sale_price, regular_price, 5, 10))
FROM inventory;

The output of the query is a 1-D ARRAY with the following values:

output_array[1:10] = [ NULL, NULL, NULL, NULL, 500, 0, 0, 400, 100, 150]

In the following query, the relational function ARRAY_LT compares each element within the scope of the 1-D ARRAY sale_price with a literal value of 100. If the element value is less than 100, the comparison function returns a nonzero value. Otherwise the function returns 0 if the condition is not satisfied or NULL if the element is not initialized. A 1-D ARRAY of item_price ARRAY type is returned with the corresponding values for each element of the input 1-D ARRAY.

SELECT ARRAY_LT(sale_price,100)
FROM inventory;

The output of the query is a 1-D ARRAY with the following values:

output_array[1:10] = [ 1, 0 , 0, 1, 0, 0, 0, 0, 1, 1]