Executes a query and returns the results. This section describes the basic syntax.
Syntax and Details
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 specifyROWS, you must also specifyCOLUMNS. In DeepSee MDX, if you specifyROWSbut do not specifyCOLUMNS, DeepSee automatically generates an ON clause forCOLUMNS. This clause uses the count measure.cube_name 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, DeepSee finds the intersection of the member used for the column and the member (if any used) for the row:
set_expression is an expression that evaluates to a set.
axis_name_or_number isCOLUMNS(or 0, which is equivalent) orROWS(or 1, which is equivalent).
If one member is a measure and the other is not a measure, DeepSee finds the value of that measure for that member. For example, if one member is theAve Agemeasure, and the other member is the34577ZIP code, then the corresponding data cell contains the average age of patients whose home ZIP code is 34577.
If neither member is a measure, DeepSee uses the default measure, which is usually%COUNT. For example, if one member is the genderF, and the other member is the34577ZIP code, then the corresponding data cell contains the count of all female patients whose home ZIP code is 34577.
ExampleThe following simple example shows patient counts by ZIP code.
SELECT MEASURES.[%COUNT] ON 0, homed.zip.MEMBERS ON 1 FROM patients Patient Count 1 32006 2,272 2 32007 1,111 3 34577 3,399 4 36711 1,069 5 38928 2,149In the following example, thepatients2cube does not include theHome Ziplevel. Instead, this cube has a relationship calledHome Citythat points to another cube, calledcities. The query uses this relationship:
SELECT MEASURES.[%COUNT] ON 0, city.cityd.city.members ON 1 FROM patients2 Patient Count 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