WHERE Clause (MDX)
Syntax and Details
select_statement WHERE set_expression
Where:
-
select_statement is a statement that uses SELECT.
-
set_expression is an expression that returns a set of members or tuples.
Instead of set_expression, you can use a measure search expression; see the example to see the behavior of WHERE in this case.
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.
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.
