IS_EMPTY and IS_NOT_EMPTY functions

The IS_EMPTY and IS_NOT_EMPTY functions determine whether a set is or is not empty. The IS EMPTY and IS NOT EMPTY functions provide alternative syntaxes for these functions.

Note: The IS NULL and IS NOT NULL operations are not supported on sets.

Sample data for the examples

The sample data used to illustrate these functions consists of a Body multi-assign string attribute and five records:

Rec ID     Body attribute
---------------------------
| 16 | { Silky, Tannins } |
| 17 | { }                |
| 18 | { Silky, Tannins } |
| 19 | { Fresh, Robust }  |
| 20 | { }                |
| 21 | { }                |
| 22 | { Firm, Robust }   |
---------------------------

Three of the records have no Body assignment (and therefore are empty sets), while the other three records have two Body assignments.

Note that these functions are used in WHERE clauses in the examples. However, they can be used anywhere that an arbitrary expression can be used, such as in SELECT and HAVING clauses.

IS_EMPTY function

The IS_EMPTY function takes a set and returns TRUE if that set is empty. The syntax of the IS_EMPTY function is:

IS_EMPTY(<set>)

where set is a set of any set data type (such as mdex:string-set or mdex:long-set). For example, set can be a multi-assign double attribute.

Examples of two results are as follows (note that { } indicates an empty set):

IS_EMPTY({ }) = TRUE
IS_EMPTY({ 1 }) = FALSE

In this example, the Body attribute is checked for emptiness:

RETURN results AS
SELECT 
   WineID AS idRec,
   Body AS bodyAttr
WHERE (WineID BETWEEN 16 AND 22) AND (IS_EMPTY(Body))
ORDER BY idRec

The result of this statement would be:

idRec
------
| 17 |
| 20 |
| 21 |
------

In the result, only Records 17, 20, and 21 are returned because they have an empty Body set.

IS EMPTY function

The IS EMPTY function provides an alternative syntax to IS_EMPTY and also returns TRUE if that set is empty.

The syntax of the IS EMPTY function is:

<set> IS EMPTY

where set is a set of any set data type, such as a multi-assign double attribute.

The previous IS_EMPTY example can be re-written as follows:

RETURN results AS
SELECT 
   WineID AS idRec,
   Body AS bodyAttr
WHERE (WineID BETWEEN 16 AND 22) AND (Body IS EMPTY)
ORDER BY idRec

The results of this example would the same as the previous IS_EMPTY example.

IS_NOT_EMPTY function

The IS_NOT_EMPTY function takes a set and returns TRUE if that set is not empty. The syntax of the IS_NOT_EMPTY function is:

IS_NOT_EMPTY(<set>)

where set is a set of any set data type. For example, set can be a multi-assign geocode attribute.

Examples of two results are as follows ({ } indicates an empty set):

IS_NOT_EMPTY({ }) = FALSE
IS_NOT_EMPTY({ 1 }) = TRUE

In this example, the Body attribute is checked for non-emptiness:

RETURN results AS
SELECT 
   WineID AS idRec,
   Body AS bodyAttr
WHERE (WineID BETWEEN 16 AND 22) AND (IS_NOT_EMPTY(Body))
ORDER BY idRec

The result of this statement might be:

bodyAttr              idRec
----------------------------
| { Silky, Tannins }  | 16 |
| { Silky, Tannins }  | 18 |
| { Fresh, Robust }   | 19 |
| { Firm, Robust }    | 22 |
----------------------------

In the result, Records 16, 18, 19, and 22 are returned because they have non-empty Body sets. However, Records 17, 20, and 21 are not returned because there is no Body assignment for those records (and therefore those sets would be empty).

IS NOT EMPTY function

The IS NOT EMPTY function provides an alternative syntax to IS_NOT_EMPTY and also returns TRUE if that set is not empty.

The syntax of the IS NOT EMPTY function is:

<set> IS NOT EMPTY

where set is a set of any set data type, such as a multi-assign string attribute.

The previous IS_NOT_EMPTY example can be re-written as follows:

RETURN results AS
SELECT 
   WineID AS idRec,
   Body AS bodyAttr
WHERE (WineID BETWEEN 16 AND 22) AND (Body IS NOT EMPTY)
ORDER BY idRec

The results of this example would the same as the previous IS_NOT_EMPTY example.