Example: Calling UNNEST - Teradata Vantage

This example calls UNNEST, passing the optional key value argument and using the optional WITH ORDINALITY clause, using a column of 1-D ARRAY type myarray. The example returns 20 rows, one for each value stored in the myarray array for each row of the tt2 table. The result has these three columns:

CREATE TYPE myarray AS INTEGER ARRAY[10];
CREATE TABLE tt2(
   pkey INTEGER,
   int_key INTEGER,
   vc_key VARCHAR(20),
   myarr myarray);
INS INTO tt2 VALUES(0, 0, 'item 0', NEW myarray(10, 20, 30, 40, 50));
INS INTO tt2 VALUES(1, 1, 'item 1', NEW myarray(11, 21, 31, 41, 51));
INS INTO tt2 VALUES(2, 2, 'item 2', NEW myarray(12, 22, 32, 42, 52));
INS INTO tt2 VALUES(3, 3, 'item 3', NEW myarray(NULL, 23, 33, 43, NULL));
INS INTO tt2 VALUES(4, 4, 'item 4', NULL);
SELECT out_key, tf.pos, tf.val
FROM tt2,
     TABLE (UNNEST(tt2.int_key, tt2.myarr) WITH ORDINALITY) AS tf(out_key, val, pos) WHERE tt2.int_key = tf.out_key ORDER BY 1,2;

*** Query completed. 20 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.
    out_key          pos          val
-----------  -----------  -----------
          0            1           10
          0            2           20
          0            3           30
          0            4           40
          0            5           50
          1            1           11
          1            2           21
          1            3           31
          1            4           41
          1            5           51
          2            1           12
          2            2           22
          2            3           32
          2            4           42
          2            5           52
          3            1            ?
          3            2           23
          3            3           33
          3            4           43
          3            5            ?