WITH Clause - DeepSee MDX Reference - Caché & Ensemble 2018.1

docs.intersystems.com

DeepSee MDX Reference

WITH Clause

[Home] [Back]

InterSystems: The power behind what matters

InterSystems DeepSee > DeepSee MDX Reference > MDX Statements and Clauses > WITH Clause

Search:

Defines one or more calculated members, named sets, or parameters for use in the SELECT statement.

Syntax and Details

WITH with_details1 with_details2 ... select_statement

Where:

select_statement is a statement that uses SELECT
with_details1, with_details2, and so on can have one of the following syntaxes:
```
MEMBER calc_mem_definition
```
Or:
```
SET named_set_definition
```
Or:
```
%PARM named_parameter_definition
```
You can mix these subclauses in a single WITH clause.

Tip:
Notice that there is no comma to separate the WITH subclauses from each other. Nor is there a comma between the WITH clause and the SELECT statement.

The following sections provide the details for the MEMBER, SET, and %PARM subclauses.

WITH MEMBER

In a WITH clause, MEMBER defines a calculated member for use in the query. The MEMBER subclause has the following syntax:

MEMBER calc_mem_details, FORMAT_STRING='format_details', SOLVE_ORDER=integer

Where calc_mem_details is as follows:

cube_name.[dimension_name].[new_member_name] AS 'value_expression'

And:

cube_name is the name of a cube.
dimension_name is the name of a dimension.
new_member_name is the name of a member; the member may or may not be already defined in the cube. If it is, the definition given here takes precedence.
value_expression is an MDX expression that defines the calculated member, typically in terms of references to other members.

For example:
```
MEASURES.[test score]/MEASURES.[%COUNT]
```
In any context where you use this calculated member, DeepSee first evaluates the Test Score and %COUNT measures in that context and then performs the division.

For another example:
```
%OR({colord.red,colord.blue,colord.yellow})
```
This new member refers to all the records of the fact table that correspond to the red, yellow, or blue members of the colord dimension.

For other variations, see “Defining Calculated Members” in Defining DeepSee Models.
FORMAT_STRING='format_details' is an optional clause that specifies how to display the values. This clause is applicable only for numeric values. See FORMAT_STRING Clause.
SOLVE_ORDER=integer is an optional clause that specifies the relative order in which to evaluate this calculated member. This clause is relevant only if the query contains calculated members on both axes. See SOLVE_ORDER Clause.

The first example shows a calculated member defined within the WITH clause:

WITH MEMBER MEASURES.avgage AS 'MEASURES.age/MEASURES.%COUNT' 
SELECT MEASURES.avgage ON 0, diagd.MEMBERS ON 1 FROM patients
                                          
1 None                                33.24
2 asthma                              34.79
3 CHD                                 67.49
4 diabetes                            57.24
5 osteoporosis                        79.46

DeepSee first evaluates the Age and %COUNT measures and then performs the division for the avgage measure.

WITH SET

In a WITH clause, SET defines a named set for use in the query. The SET subclause has the following syntax:

SET set_name AS 'set_expression'

set_name is an unquoted string that names the set.
set_expression is an expression that refers to a set.

The following example shows a named set defined within the WITH clause:

WITH SET testset AS '{homed.city.members}' SELECT MEASURES.[%COUNT] ON 0, testset ON 1 FROM patients
                             Patient Count
1 Cedar Falls                         1,045
2 Centerville                         1,069
3 Cypress                             1,150
4 Elm Heights                         1,104
5 Juniper                             1,155
6 Magnolia                            1,111
7 Pine                                1,138
8 Redwood                             1,111
9 Spruce                              1,117

WITH %PARM

In a WITH clause, %PARM defines a named parameter for use in the query. The %PARM subclause has the following syntax:

%PARM parameter_name AS 'value:default_value'

Or:

%PARM parameter_name AS 'value:default_value,caption:label'

parameter_name is the name of the parameter.
default_value is the default value of the parameter.
label is the caption to use when prompting for a value of this parameter.

When you run a query within the MDX shell, the shell prompts you for values of any named parameters.

Then, to refer to the parameter within the query itself, use @parameter_name

For example:

>>WITH %PARM c as 'value:Pine' select homed.[city].@c ON 0 FROM patients 
Please supply parameter value(s) for this query:
C [Pine]:
 
                                      Pine
                                      1,073
---------------------------------------------------------------------------
Elapsed time:       2.136337s
>>WITH %PARM c as 'value:Pine' select homed.[city].@c ON 0 FROM patients
 
Please supply parameter value(s) for this query:
C [Pine]:Magnolia
 
                                  Magnolia
                                      1,113
---------------------------------------------------------------------------
Elapsed time:       2.627897s
>>WITH %PARM c as 'value:Pine,caption:city' select homed.[city].@c ON 0 FROM patients
 
Please supply parameter value(s) for this query:
city [Pine]:
 
                                      Pine
                                      1,073
---------------------------------------------------------------------------
Elapsed time:       2.235228s
>>WITH %PARM c AS 'value:5,caption:count' SELECT TOPCOUNT(birthd.decade.MEMBERS, @c) ON 1 FROM patients
 
Please supply parameter value(s) for this query:
count [5]:3
 
 
1 1970s                               1,593
2 1960s                               1,505
3 2000s                               1,442
---------------------------------------------------------------------------
Elapsed time:       1.207581s