15.00 - ODELETE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

ODELETE

Purpose  

Removes all elements from array_expr.

Syntax  

System function syntax:

Method-style syntax:

where:

 

Syntax element…

Specifies…

TD_SYSFNLIB

the name of the database where the method is located.

array_expr

an ARRAY expression, which is one of the following:

  • A reference to an ARRAY column
  • An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195.
  • A UDF expression
  • A UDM expression
  • ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Usage Notes  

    ODELETE takes an array expression as an argument and removes all populated elements from the array value. ODELETE returns a new modified copy of the array value as the result.

    If array_expr is NULL, an error is returned.

    The ODELETE method is compatible with the Oracle DELETE method for one-dimensional ARRAY types. However, the empty set of parentheses required by Teradata syntax is a deviation from Oracle syntax.

    Result Type

    ODELETE returns a new modified copy of the array argument.

    Example  

    Consider the following one-dimensional ARRAY data type and table.

    CREATE TYPE phonenumbers AS VARRAY(20) OF CHAR(10);
     
    CREATE TABLE employee_info (eno INTEGER, phonelist phonenumbers);

    The table is populated with the following values:

    /* The first 2 elements are populated; the rest are uninitialized. */
    INSERT INTO employee_info VALUES (1, 
       phonenumbers('1112223333', '6195551234'));
     
    /* Empty ARRAY instance */
    INSERT INTO employee_info VALUES (2, 
       phonenumbers());
     
    /* Update empty ARRAY instance such that element 3 is set to a value;
       Then elements 1 and 2 are set to NULL, the rest are uninitialized */
     
    UPDATE employee_info
    SET phonelist[3] = '8584850000'
    WHERE id = 2;

    The following query removes all populated elements from the phonelist array, leaving all of the elements in an uninitialized state.

    SELECT eno, phonelist.ODELETE()
    FROM employee_info;

    The following is the result of the query.

    ENO         phonelist.ODELETE()
    ---         -------------------
     1           ()
     2           ()

    The following is the same query using function-style syntax.

    SELECT eno, ODELETE(phonelist) 
    FROM employee_info;

    Example  

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

    CREATE TYPE shot_ary AS VARRAY(1:50)(1:50) OF INTEGER;
     
    CREATE TABLE seismic_table (
       id INTEGER,
       shots shot_ary);

    The table is populated with the following values:

    /* The first 2 elements are populated; the rest are uninitialized. */
    INSERT INTO seismic_table VALUES (1, shot_ary(11, 12));
     
    /* Empty ARRAY instance */
    INSERT INTO seismic_table VALUES (2, shot_ary());
     
    /* Update empty ARRAY instance such that element [1][3] is set to a value; Then elements [1][1] and [1][2] are set to NULL, the rest are uninitialized */
     
    UPDATE seismic_table
    SET shots[1][3] = 1133
    WHERE id = 2;

    The following query removes all populated elements from the shots array, leaving all elements of the array in an uninitialized state.

    SELECT id, shots.ODELETE() 
    FROM seismic_table;

    The following is the result of the query.

    ID          shots.ODELETE()
    --          ---------------
     1           ()
     2           ()