Example: Querying a 2-D ARRAY Data Type and Table using ARRAY - 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 an ARRAY of type shot_ary with elements in scope reference [10:20][10:20] modified as a result of the operation. During evaluation, each element within the specified scope reference in shot1 is combined with the corresponding element of shot2 using the arithmetic function ARRAY_ADD.

SELECT ARRAY_ADD(shot1, shot2, NEW arrayVec(10, 20), 
   NEW arrayVec(10,20))
FROM seismic_data;

In the following query, a literal value of 9 is added to all elements within the specified scope reference of the shot1 array.

SELECT ARRAY_ADD(shot1, 9, NEW arrayVec(10,10), NEW arrayVec(20,20)) FROM seismic_data;

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

SELECT shot1.ARRAY_ADD(9, NEW arrayVec(10,10), NEW arrayVec(20,20)) FROM seismic_data;

The following query shows the use of a filtering condition while performing arithmetic operations on an n-D ARRAY. In this example, all elements within the scope reference [10:20][10:20] that have a negative value are multiplied by zero.

SELECT ARRAY_MUL(shot1, 0, NEW arrayVec(10,10), NEW arrayVec(20,20)) FROM seismic_data
WHERE ARRAY_COUNT_DISTINCT(ARRAY_LT(shot1, 0, NEW arrayVec(10,10), 
   NEW arrayVec(20,20)),0)>1;