UNION function

The UNION row function takes two sets of the same data type and returns a set that is the union of both input sets.

The syntax of the UNION function is:

UNION(<set1>, <set2>)

where:

set1 is a set of any set data type (such as mdex:string-set). For example, set1 can be a multi-assign string attribute.
set2 is a set of the same set data type as set1. For example, if set1 is a multi-assign string attribute, then set2 must also be a set of strings (such as another multi-assign string attribute).

If an attempt is made to union two sets of different set data types, an error message is returned similar to this example:

The function "UNION" is not defined for the argument type(s) mdex:string-set, mdex:double-set

In this error case, UNION was used with a multi-assign string attribute (mdex:string-set) and a multi-assign double attribute (mdex:double-set) as inputs.

UNION example

In this example, both Body and Flavors are multi-assign string attributes and WineID is the primary key of the records:

RETURN results AS
SELECT 
   WineID AS idRec,
   UNION(Body, Flavors) AS unionAttrs
FROM WineState
WHERE WineID BETWEEN 5 AND 9
ORDER BY idRec

The result of this statement might be:

idRec                unionAttrs
-----------------------------------------------------
| 5 | { Blackberry, Earthy, Silky, Tannins, Toast } |
| 6 | { Berry, Plum, Robust, Zesty }                |
| 7 | { Cherry, Pepper, Prune, Silky, Tannins }     |
| 8 | { Cherry, Oak, Raspberry, Robust }            |
| 9 | { Earthy, Fruit, Strawberry, Silky, Tannins } |
-----------------------------------------------------

To take one set as an example, Record 5 has "Silky" and "Tannins" for its two Body assignments and "Blackberry", "Earthy", and "Toast" for its three Flavors assignments. The resulting set is a union of all five attribute values.