Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.4 / Analytics / InterSystems MDX Reference / MDX Functions / UNION
Previous section   Next section

UNION

Returns a set that consists of the elements of the two given sets, optionally eliminating duplicates.

Returned Type

This function returns a set.

Syntax and Details

UNION(set_expression1, set_expression2, ALL)
Or:
UNION(set_expression1, set_expression2)
  • set_expression1 and set_expression2 are expressions that evaluate to sets.
  • The optional keyword ALL, if included, specifies that all duplicates should be retained. By default, if the returned set includes any duplicate elements, only the first of those is included.

Example

Consider the following query which defines two named sets:
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat}' 
SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2) ON 1 FROM patients
                             Patient Count
1 eggs                                  451
2 soy                                   462
3 wheat                                 479
4 dairy products                        463
5 pollen                                447
This query shows all the members that are in set1 and set2.
In contrast, consider the following variation, which uses the ALL keyword to keep duplicates:
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat}' 
SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2,ALL) ON 1 FROM patients
                             Patient Count
1 eggs                                  451
2 soy                                   462
3 wheat                                 479
4 dairy products                        463
5 pollen                                447
6 soy                                   462
7 wheat                                 479
Finally, you can of course use more interesting sets as arguments. For example:
WITH SET set1 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg allergy count])' 
SET set2 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg age])' 
SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2) ON 1 FROM patients                   
          Patient Count
1 Juniper                             1,197
2 Spruce                              1,135
3 Centerville                         1,155
4 Redwood                             1,144
5 Magnolia                            1,156
6 Cedar Falls                         1,188
7 Elm Heights                         1,266
8 Pine                                1,139

See Also

Previous section   Next section