Executes a query and returns the results. This section describes the basic syntax.
SELECT set_expression ON 0, set_expression ON 1 FROM cube_name
The ON clause is an optional axis specification. It has the following form:
set_expression ON axis_name_or_number
You can specify zero, one, or two ON clauses. If you specify two ON clauses, you can specify them in either order.
In other implementations of MDX, if you specify ROWS
, you must also specify COLUMNS
. In InterSystems MDX, if you specify ROWS
but do not specify COLUMNS
, the system automatically generates an ON clause for COLUMNS
. This clause uses the count measure.
is the name of the cube to use. The set expressions must make sense within that cube.
If you omit the ON clause, MDX returns the count of records in the cube. If this is a compound cube, this is the sum of the counts of all cubes combined in that compound cube.
For any given cell of the results, to determine the value to use, the system finds the intersection
of the member used for the column and the member (if any used) for the row:
If one member is a measure and the other is not a measure, the system finds the value of that measure for that member. For example, if one member is the Ave Age
measure, and the other member is the 34577
ZIP code, then the corresponding data cell contains the average age of patients whose home ZIP code is 34577.
If neither member is a measure, the system uses the default measure, which is usually %COUNT
. For example, if one member is the gender F
, and the other member is the 34577
ZIP code, then the corresponding data cell contains the count of all female patients whose home ZIP code is 34577.
The following simple example shows patient counts by ZIP code.
SELECT MEASURES.[%COUNT] ON 0, homed.zip.MEMBERS ON 1 FROM patients
1 32006 2,272
2 32007 1,111
3 34577 3,399
4 36711 1,069
5 38928 2,149
In the following example, the patients2
cube does not include the Home Zip
level. Instead, this cube has a relationship called Home City
that points to another cube, called cities
. The query uses this relationship:
SELECT MEASURES.[%COUNT] ON 0, city.cityd.city.members ON 1 FROM patients2
1 Cedar Falls 1,097
2 Centerville 1,136
3 Cypress 1,124
4 Elm Heights 1,089
5 Juniper 1,133
6 Magnolia 1,063
7 Pine 1,124
8 Redwood 1,083
9 Spruce 1,151