Example: Calling UNNEST

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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:

  • out_key is the optional key value passed to UNNEST. In this example, it corresponds to the value of the int_key column in the tt2 table, so it identifies the array values in the results that come from each row of the tt2 table.
  • pos is the position of an element within the array. It is included in the results because the example uses the WITH ORDINALITY clause.
  • val is the value of the element in the array.
    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            ?