15.00 - OEXISTS - Teradata Database

OEXISTS

Purpose  

Returns an integer value indicating whether the element specified by the index in array_expr contains a value or is in an uninitialized state.

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
index_value	an unsigned integer value.
array_bound	an array instance of the predefined array type ArrayVec with a comma-separated list of integer values to define the bounds. For details, see “ARRAY Scope Reference” on page 198.

ANSI Compliance

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

Usage Notes  

OEXISTS takes an array expression as an argument and checks the element specified by the index to see if it contains a value or is in an uninitialized state. OEXISTS returns 1 if the specified element contains a non-NULL or NULL. If the element is in an uninitialized state, the method returns 0.

If the value of index_value or array_bound is out of bounds for array_expr, OEXISTS returns 0. If either index_value or array_bound is NULL, a 0 value is returned. If array_expr is NULL, then an error stating that the function does not exists is returned.

If array_expr refers to an empty array with all elements in an uninitialized state, then OEXISTS returns 0. The OEXISTS method with an integer index_value argument is compatible with the Oracle EXISTS method for one-dimensional ARRAY types.

Result Type and Value

OEXISTS returns an integer value of 1 or 0.

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 the 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 checks to see whether element 2 of the phonelist array contains a value.

SELECT eno, phonelist.OEXISTS(2) 

FROM employee_info;

The following is the result of the query.

ENO         phonelist.OEXISTS(2)

---         --------------------

  1           1           (element 2 contains a non-NULL value)

  2           1           (element 2 contains a NULL)

The following query checks to see whether element 3 of the phonelist array contains a value.

SELECT eno, phonelist.OEXISTS(3) 

FROM employee_info;

The following is the result of the query.

ENO         phonelist.OEXISTS(3)

---         --------------------

  1           0           (element 3 is in an uninitialized state)

  2           1           (element 3 contains a non-NULL value)

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

SELECT eno, OEXISTS(phonelist, 3) 

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 the 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 checks to see whether element [1][2] of the shots array contains a value.

SELECT id, shots.OEXISTS(NEW arrayVec(1, 2)) 

FROM seismic_table;

The following is the result of the query.

ID          shots.OEXISTS(new arrayVec(1, 2))

--          ---------------------------------

 1           1           (element [1][2] contains a non-NULL value)

 2           1           (element [1][2] contains a NULL)

The following query checks to see whether element [1][3] of the shots array contains a value.

SELECT id, shots.OEXISTS(NEW arrayVec(1, 3)) 

FROM seismic_table;

The following is the result of the query.

ID          shots.OEXISTS(NEW arrayVec(1, 3))

--          ---------------------------------

 1           0           (element [1][3] is in an uninitialized state)

 2           1           (element [1][3] contains a non-NULL value)