Skip to main content

WHERE Clause (MDX)

Applies a filter to a SELECT statement; describes how to slice the results of a SELECT statement.

Syntax and Details

select_statement WHERE set_expression

Where:

Because the system automatically converts types where appropriate, you can also use a single member expression or tuple expression in place of the set expression.

Important:

Each set element is used as a separate slicer axis, and the results of all the slicer axes (of all %FILTER clauses) are aggregated together. This is the process of axis folding (a filter is considered to be a query axis). Axis folding means that if a given source record has a non-null result for each slicer axis, that record is counted multiple times.

In axis folding, values are combined according to the aggregation method for that measure, as specified in the cube definition. (In the examples here, %COUNT is added.)

For more details, see Axis Folding in How the Business Intelligence Query Engine Works.

Example

Compare the following two example SELECT statements, one with a WHERE clause and one without a WHERE clause.

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM patients 
                             Patient Count
1 Cedar Falls                         1,039
2 Centerville                         1,107
3 Cypress                             1,096
4 Elm Heights                         1,093
5 Juniper                             1,150
6 Magnolia                            1,092
7 Pine                                1,157
8 Redwood                             1,125
9 Spruce                              1,141

The previous query shows the count of patients in each city. In contrast, consider the following query, which shows the count of male patients in each city:

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM patients WHERE gend.male
 
                             Patient Count
1 Cedar Falls                           509
2 Centerville                           569
3 Cypress                               517
4 Elm Heights                           531
5 Juniper                               574
6 Magnolia                              527
7 Pine                                  569
8 Redwood                               553
9 Spruce                                557

To demonstrate the effect of multiple items in the WHERE clause, first consider the following query:

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM patients WHERE colord.green
 
                             Patient Count
1 Cedar Falls                           137
2 Centerville                           129
3 Cypress                               150
4 Elm Heights                           128
5 Juniper                               126
6 Magnolia                              143
7 Pine                                  155
8 Redwood                               148
9 Spruce                                147

Now consider the following query, which uses both gend.male and colord.green as set elements in the WHERE clause:

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM patients WHERE {gend.male,colord.green}
 
                             Patient Count
1 Cedar Falls                           646
2 Centerville                           698
3 Cypress                               667
4 Elm Heights                           659
5 Juniper                               700
6 Magnolia                              670
7 Pine                                  724
8 Redwood                               701
9 Spruce                                704

By comparing the results for Cedar Falls, for example, you can see that this query adds the results for male patients and the results for patients whose favorite color is green. If you instead wanted to see the results for male patients whose favorite color is green, you would use either a CROSSJOIN or a tuple expression in the WHERE clause, as follows:

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM patients 
WHERE CROSSJOIN(gend.male,colord.green)
 
                             Patient Count
1 Cedar Falls                            56
2 Centerville                            65
3 Cypress                                80
4 Elm Heights                            59
5 Juniper                                73
6 Magnolia                               74
7 Pine                                   82
8 Redwood                                70
9 Spruce                                 74

The following example uses a tuple expression in the WHERE clause:

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM patients WHERE (gend.male,aged.60)
                             Patient Count
1 Cedar Falls                             3
2 Centerville                             9
3 Cypress                                 7
4 Elm Heights                             1
5 Juniper                                 8
6 Magnolia                                2
7 Pine                                    5
8 Redwood                                 6
9 Spruce                                  3

You can also use the WHERE clause as a way to display a specific measure:

SELECT gend.gender.MEMBERS ON 0 FROM patients WHERE MEASURES.[avg test score]
                              Female                 Male
All Patients                    74.78                74.46

Notice that the measure name is not shown, however.

If you use the WHERE clause with a measure search expression, the clause uses only rows of the fact table that do meet the given criteria. (A measure search expression is an InterSystems extension to MDX that considers the measure values in the fact table itself.)

SELECT MEASURES.[%COUNT] ON 0 FROM patients WHERE %SEARCH.&[[MEASURES].[age]<10]
                             Patient Count
                                      1,370

See Also

See the %FILTER clause.

FeedbackOpens in a new tab