Skip to main content
Previous sectionNext section

%NOT

Enables you to exclude a single member of a given level. This function is an InterSystems extension to MDX.

Returned Type

This function returns a member.

Syntax and Details

member_expression.%NOT

Where:

  • member_expression is a member identifier. (Note that you cannot use a general member expression.)

This function enables you to exclude the given member.

Example

Often it is necessary for the WHERE clause to exclude a single member. For example, first consider the following query, which uses EXCEPT:

SELECT aged.[age bucket].MEMBERS ON 1 FROM patients WHERE EXCEPT(aged.[age group].MEMBERS,aged.[age group].[0 to 29])
 
1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                              166
5 40 to 49                              139
6 50 to 59                              106
7 60 to 69                               86
8 70 to 79                               62
9 80+                                    41
Copy code to clipboard

You can use the %NOT function to rewrite the previous query as follows:

SELECT aged.[age bucket].MEMBERS ON 1 FROM patients WHERE aged.[age group].[0 to 29].%NOT
 
 1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                              166
5 40 to 49                              139
6 50 to 59                              106
7 60 to 69                               86
8 70 to 79                               62
9 80+                                    41
Copy code to clipboard

If you use this function on the column or row axis, you can see that it returns a member:

SELECT aged.[age group].[0 to 29].%NOT ON 1 FROM patients
 
Not 0 to 29                             600
Copy code to clipboard

As you can see the name of the member is NOT followed by the name of the excluded member.

The %NOT function provides several advantages:

  • DeepSee does not need to materialize all the members of the level.

  • The negation occurs in an earlier part of the processing for greater efficiency.

  • %NOT returns a single member which can be combined (internally) with other filters to form simple tuple expressions.

See Also