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
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
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
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
WHERE Ranking IN Score AND Price IS NOT NULL
ORDER BY idRec

For details on the IN expression, see IN.