DeepSee MDX Reference
EXCEPT

 Server:docs2 Instance:LATEST User:UnknownUser

 [ Home ]  >  [ InterSystems DeepSee ]  >  [ DeepSee MDX Reference ]  >  [ MDX Functions ]  >  [ EXCEPT ] Go to: Returned Type Syntax and Details Example See Also 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)`
• The optional keyword ALL, if included, specifies that all duplicates should be retained. By default, if the first set includes any duplicate elements, only the first of those is included.
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```