DeepSee MDX Reference
DRILLTHROUGH Statement
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Displays the lowest-level data associated with the first cell of results of a given SELECT statement.
Syntax and Details
DRILLTHROUGH optionalmaxrows select_statement
Or:
DRILLTHROUGH optionalmaxrows select_statement RETURN fieldname1,
fieldname2, ...
Or:
DRILLTHROUGH optionalmaxrows select_statement RETURN fieldname1,
... %ORDER BY fieldname3, ...
Or:
DRILLTHROUGH optionalmaxrows select_statement %LISTING [listingname]
Where:
The RETURN clause specifies the fields to display. The %ORDER BY clause is an InterSystems extension to MDX. If included, this clause specifies how to sort the displayed records.
The %LISTING clause is another InterSystems extension to MDX. This clause specifies the listing to use. If you specify this clause, DeepSee displays the fields as given in that listing.
If you do not specify the RETURN clause or the %LISTING clause, the query uses the default listing defined in the DeepSee cube.
Internally, DeepSee builds and uses an SQL query.
Important:
If the SELECT statement returns more than one cell of data, the listing shows only the fields associated with the first cell.
Additional Options for RETURN and ORDER BY
In the RETURN and %ORDER BY clauses, note the following points:
For example:
... RETURN %ID,%EXTERNAL(Field1) F1,'$'||source.Sales Sales
The first line of any listing is a heading that indicates the field names or their aliases. Below the heading, the listing has a column of data below each heading. In this case, the columns would be as follows:
Example
The first example does not use RETURN, so it uses the default listing as defined in the cube:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients
 
   #  PatientID  Age        Gender     TestScore  HomeCity   DoctorGrou
   1: SUBJ_10161 0          F          76         3          I
   2: SUBJ_10330 0          F                     3          II
   3: SUBJ_10554 0          F          68         3          II
   4: SUBJ_10555 0          F          78         3          II
   5: SUBJ_10686 0          F          91         3          I
...
The next example uses the MAXROWS argument:
DRILLTHROUGH MAXROWS 3 SELECT homed.Magnolia ON 1 FROM patients
 
   #  PatientID  Age        Gender     TestScore  HomeCity   DoctorGrou
   1: SUBJ_10161 0          F          76         3          I
   2: SUBJ_10330 0          F                     3          II
   3: SUBJ_10554 0          F          68         3          II
The next example uses the RETURN clause:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients RETURN Gender, HomeCity->PostalCode 
   #  Gender         PostalCode
   1: F              34577
   2: F              34577
   3: F              34577
   4: F              34577
   5: F              34577
...
The next example also uses the %ORDER BY clause:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients RETURN PatientID, Age, Gender %ORDER BY Age
 
   #  PatientID      Age            Gender
   1: SUBJ_101616    0              F
   2: SUBJ_102705    0              M
   3: SUBJ_103210    0              M
   4: SUBJ_103300    0              F
   5: SUBJ_103972    0              M
...
The last example uses the %ORDER BY clause with two field names to specify the order:
DRILLTHROUGH SELECT homed.Magnolia ON 1 FROM patients RETURN PatientID, Age, Gender %ORDER BY Gender, Age
 
   #  PatientID      Age            Gender
   1: SUBJ_101616    0              F
   2: SUBJ_103300    0              F
   3: SUBJ_105548    0              F
   4: SUBJ_105556    0              F
   5: SUBJ_106865    0              F
...
In this case, the records are sorted first by gender. Within gender, they are sorted by age.