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