SET(e) (IDS)

The SET data type is an unordered collection type that stores unique elements; duplicate element values are not valid. (For a collection type that supports duplicate values, see the description of MULTISET in MULTISET(e) (IDS).)

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) (IDS).) 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, 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 on the syntax of the SET collection type, see the IBM Informix: 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 IBM Informix: 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"}