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

Returns a set of tuples formed by the cross-product of the specified sets.
Returned Type
This function returns a set of tuples.
Syntax and Details
CROSSJOIN(set_expression1, set_expression2)
Where:
The function identifies all the members of each set and then generates a set of tuples that combine each member of the first set with each member of the second set.
Tip:
The keyword phrase NON EMPTY is particularly useful with this function. Note that you can use this keyword phrase immediately before any set expression.
Example
For example:
SELECT MEASURES.[%COUNT] ON 0, CROSSJOIN(diagd.MEMBERS, aged.[age group].MEMBERS) ON 1 FROM patients

                             Patient Count
 1 None->0 to 29                      3,839
 2 None->30 to 59                     3,615
 3 None->60+                            971
 4 asthma->0 to 29                      308
 5 asthma->30 to 59                     282
 6 asthma->60+                          113
 7 CHD->0 to 29                           1
 8 CHD->30 to 59                         93
 9 CHD->60+                             229
10 diabetes->0 to 29                     30
11 diabetes->30 to 59                   246
12 diabetes->60+                        228
13 osteoporosis->0 to 29                  *
14 osteoporosis->30 to 59                 *
15 osteoporosis->60+                    200
In contrast, suppose that we add the NON EMPTY keyword phrase:
SELECT MEASURES.[%COUNT] ON 0, NON EMPTY CROSSJOIN(diagd.MEMBERS, aged.[age group].MEMBERS) ON 1 FROM patients

                             Patient Count
 1 None->0 to 29                      3,839
 2 None->30 to 59                     3,615
 3 None->60+                            971
 4 asthma->0 to 29                      308
 5 asthma->30 to 59                     282
 6 asthma->60+                          113
 7 CHD->0 to 29                           1
 8 CHD->30 to 59                         93
 9 CHD->60+                             229
10 diabetes->0 to 29                     30
11 diabetes->30 to 59                   246
12 diabetes->60+                        228
13 osteoporosis->60+                    200
For another example:
SELECT CROSSJOIN([GenD].[H1].[Gender].Members,
{[Measures].[%COUNT],[Measures].[Avg Age]}) ON 0,[DiagD].[H1].[Diagnoses].Members ON 1 
FROM [Patients]
 
                  Patient Coun        Avg Age   Patient Coun        Avg Age
1 None                      419          35.61            412          32.36
2 asthma                     51          37.12             33          27.79
3 CHD                        17          70.47             19          62.42
4 diabetes                   25          63.96             21          57.67
5 osteoporosis               20          80.55              2          74.50
See Also