Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.4 / Analytics / InterSystems MDX Reference / Expression Types / Set Expressions
Previous section   Next section

Set Expressions

This section describes how to create and use set expressions in InterSystems MDX.

Details

The general syntax for a set expression is as follows:
{expression1, expression2, ...}
This list can include any number of items. In InterSystems MDX, if the list includes only one item, you can omit the curly braces.
In this list, expression1, expression2, and so on can have any of the following forms:
  • A range of members, specified as follows:
    member1:member2
    
    This expression returns a set that consists of the two given members and all members between them, given the order of members in the level that contains them. The members must belong to the same level.
    For example:
    birthd.year.1960:birthd.year.1980
    
    For member2, you can omit the dimension, hierarchy, and level identifiers. For example:
    birthd.year.1960:1980
    
  • An expression that uses an MDX function that returns a set, for example:
    homed.zip.MEMBERS
    
    Many MDX functions return sets, including MEMBERS, NONEMPTYCROSSJOIN, ORDER, and others.
    Note that %TERMLIST can return a set.
  • Another set expression.
  • The name of a named set. See the following section.
  • (Note that in other implementations of MDX, for each tuple in a set, you must use the dimensions in the same order as in the other tuples in the set. For example, if the first tuple uses dimension A in its first list item, all the other tuples must do so as well. InterSystems MDX does not have this restriction. Similarly, in other implementations of MDX, a set cannot include a combination of tuples and other types of set elements. InterSystems MDX does not have this restriction either.)
  • A reference to a pivot variable that contains a set expression. To refer to a pivot variable, use the following syntax:
    $VARIABLE.variablename
    
    Where variablename is the logical variable name. Do not enclose this expression with square brackets. This syntax is not case-sensitive; nor is the pivot variable name.
    For information on defining pivot variables, see “Defining and Using Pivot Variables” in Using the Analyzer.
You can precede any set expression with the keyword phrase NON EMPTY, for example:
NON EMPTY {birthd.year.1960:1980}
NON EMPTY birthd.year.1960:1980
NON EMPTY {homed.zip.MEMBERS}
NON EMPTY homed.zip.MEMBERS
The NON EMPTY keyword phrase suppresses empty elements of the set; the set is evaluated and then empty elements are removed. This keyword is particularly useful with CROSSJOIN and in scenarios where a filter can potentially cause elements to be null.

Named Sets

A named set consists of two elements: a set name and a set expression. You can define named sets in two ways:
  • Within the WITH clause of a query. The set name is available within the rest of the query, but is not available in other queries.
  • Within the CREATE SET statement. The set name is available within the rest of the session (for example, within the rest of the session in the MDX shell).

Uses

You can use set expressions in the following ways:
  • As a set argument of many MDX functions. Note that for some functions, the set must consist only of members. For other functions, the set must consist only of members or tuples. This book notes these requirements where needed.
  • As an axis in the SELECT statement.
  • As the definition of a named set, as described in the previous subsection.
Previous section   Next section