ODELETE - Teradata Database

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           ()