DeepSee MDX Reference
UNION
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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)
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