IS_MEMBER_OF function

The IS_MEMBER_OF row function takes an atomic value and a set, and returns a Boolean indicating whether the atomic value occurs in the set.

The syntax of the IS_MEMBER_OF function is: 
IS_MEMBER_OF(<atomic-value>, <set>)
where:
  • atomic-value is an atomic value, such as 50 (for an integer set) or 'test' (for a string set). It can also be a single-assign attribute. atomic-value will be checked to see whether it occurs in set. The type of the atomic value must match the type of the set's elements.
  • set is a set in which its elements have the same set data type as atomic-value. For example, if atomic-value is a single-assign string attribute, then the elements of set must also be strings.
Examples of some results are as follows ({ } indicates an empty set): 
IS_MEMBER_OF(1, { }) = FALSE
IS_MEMBER_OF(1, { 1, 2, 3 }) = TRUE
IS_MEMBER_OF(1, { 2, 3, 4 }) = FALSE
IS_MEMBER_OF(NULL, { }) = NULL
IS_MEMBER_OF(NULL, { 1, 2, 3 }) = NULL
IS_MEMBER_OF(1, { 'a', 'b', 'c' }) yields a checking error because the atomic value and the set elements are not of the same data type

The IS_MEMBER_OF function is intended as a membership check function.

IS_MEMBER_OF examples

Example 1: In this example, the statement determines whether the number 82 (which is an integer) occurs in the Score set (which has integer elements): 
RETURN results AS
SELECT 
   WineID AS idRec,
   IS_MEMBER_OF(82, Score) AS memberAttrs
FROM WineState
WHERE WineID BETWEEN 22 AND 25
ORDER BY idRec
The result of this statement might be: 
idRec  memberAttrs
--------------
| 22 | false |
| 23 | true  |
| 24 | false |
| 25 | true  |
--------------

The results show that the number 82 occurs in the Score set of Records 23 and 25, but not in Records 22 and 24.

Example 2: This example is similar to Example 1, except that it uses the Ranking single-assign integer attribute as the first argument to the IS_MEMBER_OF function and the Score set (which has integer elements) as the second argument: 
RETURN results AS
SELECT 
   WineID AS idRec,
   IS_MEMBER_OF(Ranking, Score) AS memberAttrs
FROM WineState
ORDER BY idRec
Example 3: This example is similar to Example 2, except that it uses the IS_MEMBER_OF function in a WHERE clause: 
RETURN results AS
SELECT 
   WineID AS idRec,
   Price AS prices
FROM WineState
WHERE IS_MEMBER_OF(Ranking, Score) AND Price IS NOT NULL
ORDER BY idRec

Using the IN expression

You can use the IN expression as an alternative to the IS_MEMBER_OF function for membership tests. To illustrate this, Example 3 can be re-written as: 
RETURN results AS
SELECT 
   WineID AS idRec,
   Price AS prices
FROM WineState
WHERE Ranking IN Score AND Price IS NOT NULL
ORDER BY idRec

For details on the IN expression, see IN.