15.00 - OEXISTS - 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)
Last Update
2018-09-24

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)