Example: Querying a 2-D ARRAY Data Type and Table - Advanced SQL Engine - Teradata Database

Consider the following 2-D ARRAY data type and table:

CREATE TYPE shot_ary AS VARRAY(1:50)(1:50) OF INTEGER DEFAULT NULL;
CREATE TABLE seismic_data (
   id INTEGER,
   shot1 shot_ary,
   shot2 shot_ary);

The following query returns a 2-D ARRAY with an element type of INTEGER. The size of the output array is the same as that of the input array argument. During evaluation, the element in position [5][10] of the shot1 array is compared to a value of 5. If the element is greater than 5, the value for the corresponding element in the output array is set to a non-zero value, otherwise it is set to 0. All other elements in the output array are set to NULL.

SELECT ARRAY_GT(shot1, 5, NEW arrayVec(5,5), NEW arrayVec(10,10)) 
FROM seismic_data;

The following is the same query using method-style syntax:

SELECT shot1.ARRAY_GT(5, NEW arrayVec(5,5), NEW arrayVec(10,10)) 
FROM seismic_data;

In the following query, the relational function ARRAY_LT compares each element within the scope reference [3:5][8:10] of the 2-D ARRAY shot1 with a literal value of 0. If the element value is less than 0 the comparison function returns 0, otherwise it returns a non-zero value. The resulting array of shot_ary type is then multiplied by the shot1 array.

SELECT ARRAY_MUL(shot1, ARRAY_LT(shot1, 0, NEW arrayVec(3,8), NEW arrayVec(5,10)))
FROM seismic_data;