SET(e) data type

The SET data type is an unordered collection type that stores unique elements

Duplicate element values are not valid as explained in HCL OneDB™ Guide to SQL: Syntax. (For a collection type that supports duplicate values, see the description of MULTISET in MULTISET(e) data type.)

No more than 97 columns of the same table can be declared as SET data types. (The same restriction also applies to MULTISET and LIST collection types.)

The elements in a SET have no ordinal position. That is, no construct of a first, second, or third element in a SET exists. (For a collection type with ordinal positions for elements, see LIST(e) data type.) All elements in a SET have the same element type. To specify the element type, use this syntax: 
SET(element_type NOT NULL)
The element_type of a collection can be any of the following types:
  • A built-in type, except SERIAL, SERIAL8, BIGSERIAL, BYTE, and TEXT
  • A named or unnamed ROW type
  • Another collection type
  • An opaque type

You must specify the NOT NULL constraint for SET elements. No other constraints are valid for SET columns. For more information about the syntax of the SET collection type, see the HCL OneDB Guide to SQL: Syntax.

You can use SET anywhere that you use any other data type, unless otherwise indicated. For example:
  • After the IN predicate in the WHERE clause of a SELECT statement to search for matching SET values
  • As an argument to the CARDINALITY or mi_collection_card( ) function to determine the number of elements in a SET column

SET values are not valid as arguments to an aggregate function such as AVG, MAX, MIN, or SUM. For more information, see the Condition and Expression sections in the HCL OneDB Guide to SQL: Syntax.

The following examples declare two sets. The first statement declares a set of integers and the second declares a set of character elements. 
SET(INTEGER NOT NULL)
SET(CHAR(20) NOT NULL)
The following examples construct the same sets from value lists: 
SET{1, 5, 13}
SET{"Oakland", "Menlo Park", "Portland", "Lenexa"}
In the following example, a SET constructor function is part of a CREATE TABLE statement: 
CREATE TABLE tab 
(
   c CHAR(5), 
   s SET(INTEGER NOT NULL)
);
The following set values are equal: 
SET{"blue", "green", "yellow"}
SET{"yellow", "blue", "green"}