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:
TheIS NULL
and IS NOT NULL
operations are not supported on sets.Sample data for the examples
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
clauses in the examples. However, they can be used anywhere that an arbitrary expression can be used, such as in WHERE
SELECT
and HAVING
clauses.
IS_EMPTY function
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.{ }
indicates an empty set):
IS_EMPTY({ }) = TRUE IS_EMPTY({ 1 }) = FALSE
RETURN results AS SELECT WineID AS idRec, Body AS bodyAttr FROM WineState WHERE (WineID BETWEEN 16 AND 22) AND (IS_EMPTY(Body)) ORDER BY idRec
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.
IS EMPTY
function is:
<set> IS EMPTY
where set is a set of any set data type, such as a multi-assign double attribute.IS_EMPTY
example can be re-written as follows:
RETURN results AS SELECT WineID AS idRec, Body AS bodyAttr FROM WineState 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
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.{ }
indicates an empty set):
IS_NOT_EMPTY({ }) = FALSE IS_NOT_EMPTY({ 1 }) = TRUE
RETURN results AS SELECT WineID AS idRec, Body AS bodyAttr FROM WineState WHERE (WineID BETWEEN 16 AND 22) AND (IS_NOT_EMPTY(Body)) ORDER BY idRec
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.
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.IS_NOT_EMPTY
example can be re-written as follows:
RETURN results AS SELECT WineID AS idRec, Body AS bodyAttr FROM WineState 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.