EQL represents multi-assign attributes from collections as sets.
A set consists of a group of elements, typically derived from the values of a multi-assign attribute. EQL sets are intended to behave like mathematical sets: the order of the elements within a set is not specified (and, in general, not observable). An empty set is a set that contains no elements.
All elements in a set must be of the same data type. If the elements in the set come from two multi-assign attributes (for example, by using the INTERSECTION
row function), then those two multi-assign attributes must be of the same data type. Sets may not contain duplicate values and sets may not contain other sets.
SELECT
with a multi-assign attribute will return the vales of that attribute in a set.SET
function.UNION
function will return a set that is a union of two input sets. Note that these set expressions require at least one set on which to operate.All of these methods are described in this section.
Note that sets are not persistent from one EQL query to another.
Set data types
mdex:boolean-set
for multi-assign Boolean attributesmdex:dateTime-set
for multi-assign dateTime attributesmdex:double-set
for multi-assign double attributesmdex:duration-set
for multi-assign duration attributesmdex:geocode-set
for multi-assign geocode attributesmdex:long-set
for multi-assign 32-bit integer and 64-bit long attributesmdex:string-set
for multi-assign string attributesmdex:time-set
for multi-assign time attributes
{3, 4.0, 'five'}
is invalid because it contains an integer, a double, and a string.Sets and NULL
Sets may not contain NULL values. In addition, sets may not be NULL, but they may be empty. These requirements apply to both multi-assign collection attributes and other expressions of set type.
If a collection record has no assignments for a multi-assign attribute, then in an EQL query, that attribute's value for that record is the empty set.
The results of an EQL statement (whether DEFINE
or RETURN
) may contain sets. This means, for instance, that you can define an entity (view) that provides all of the values of a multi-assign attribute to queries that use that entity.
Note that the IS NULL
and IS NOT NULL
operations are not supported on sets. Instead, use the IS_EMPTY
and IS_NOT_EMPTY
functions to determine whether a set is empty. Likewise, the IS_EMPTY
and IS_NOT_EMPTY
functions cannot be used on atomic values (such as on a single-assign attribute).
Set equality
Set equality is the same as mathematical set equality: two sets are equal if and only if they contain exactly the same elements, no more, no less. The order of the elements in the set is immaterial. Two empty sets are equal.
Set equality and inequality are defined only on two sets of the same type. For example, you cannot compare an mdex:long-set
and an mdex:geocode-set
for equality; doing so will result in an EQL type error.
You can use the =
(equal) and <>
(not equal) operators to test for equality between sets. Note that the <
(less than) and >
(greater than) operators are not defined for sets.
Sets, functions, and operators
This chapter documents the aggregation and row functions that are used with sets.
ARB
on sets looks at all of the rows (both empty sets and non-empty sets) in the group and selects the set value from one of the rows. For details on this function, see ARB.COUNT
counts all non-NULL sets (that is, all the sets in the group, including the empty ones). For details, see COUNT function.COUNT_APPROX
also counts all non-NULL sets. For details, see COUNT_APPROX.COUNTDISTINCT
counts all of the sets, including the empty ones. For details, see COUNTDISTINCT function.APPROXCOUNTDISTINCT
also counts all of the sets, including the empty ones. For details, see APPROXCOUNTDISTINCT function.HAS_REFINEMENTS
whether a particular attribute has non-implicit refinements in the current navigation state. For details, see HAS_REFINEMENTS.As mentioned above, you can use the =
(equal) and <>
(not equal) operators to test for equality between sets. The other operators (such as the * multiplication operator) cannot be used on sets.