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

Examines two sets and returns a set that consists of the elements of the first set, except for any elements that are also in the second set. This function optionally eliminates duplicates in that set.
Returned Type
This function returns a set.
Syntax and Details
EXCEPT(set_expression1, set_expression2, ALL)
EXCEPT(set_expression1, set_expression2)
If set_expression2 includes elements that are not in set_expression1, those elements are ignored. The returned set includes only elements from set_expression1.
Example
Consider the following query which defines two named sets:
WITH SET set1 AS '{allerd.eggs,allerd.eggs,allerd.soy,allerd.wheat}' 
SET set2 AS '{allerd.[diary products],allerd.pollen,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, EXCEPT(set1,set2) ON 1 FROM patients

                             Patient Count
1 eggs                                  451
2 soy                                   462
This query shows the members of set1 that are not also in set2. Notice that the member allerd.eggs is listed twice within set1, but is shown only once in the result.
In contrast, the following variation uses the ALL keyword:
WITH SET set1 AS '{allerd.eggs,allerd.eggs,allerd.soy,allerd.wheat}' 
SET set2 AS '{allerd.[diary products],allerd.pollen,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, EXCEPT(set1,set2,ALL) ON 1 FROM patients

                             Patient Count
1 eggs                                  451
2 eggs                                  451
3 soy                                   462
See Also