Previous section   Next section

SELECT Statement

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
Where:
  • 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 DeepSee MDX, if you specify
    ROWS
    but do not specify
    COLUMNS
    , DeepSee automatically generates an ON clause for
    COLUMNS
    . 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:
  • 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 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, DeepSee 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.

Example

The 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,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
 
                             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

See Also

Previous section   Next section