LIMIT command (using LEVELREL) |
A LIMIT command that uses only dimension values that are at the same level as the current level of the hierarchical dimension or dimension surrogate when setting status or assigning values to a valueset.
Syntax
LIMIT {dimension | valueset} [concat-component] limit-type-
LEVELREL level-relation-clause [IFNONE label]
The syntax of level-relation-clause varies depending on the dimensionality of the level relation:
When the level relation is multidimensional you must use the following syntax:
RELATION level-relation-name [QUALIFY relation-dimension-name [inclusive-val-args...]...]
(You can also use this syntax when the level relation is one-dimensional.)
When the level relation is one-dimensional, you can use the following simplified syntax:
level-relation-name [valueset2]
Arguments
The name of the dimension or dimension surrogate for which you are setting status.
The name of the valueset for which you are assigning values.
The name of the component of the concat dimension whose values are used to determine the limit. (See the main entry for LIMIT command for complete description of this argument.)
A keyword that specifies how Oracle OLAP should modify the current status list. (See the main entry for LIMIT command for a list and descriptions of these keywords.)
Sets the status of a hierarchical dimension to all of the values of the hierarchical dimension that are at the same level as the current value of the dimension; or, that limits a hierarchical dimension to those values of the hierarchical dimension that are at the same level as the current value of the dimension and that are also in a specified valueset.
Specifies that Oracle OLAP performs the limit based on the values of the relation specified by level-relation-name. This keyword must be specified if level-relation-name is a multidimensional relation.
Specifies the name of a level relation for the hierarchical dimension you want to limit. A level relation is a relation between a hierarchical dimension and another dimension (sometimes called the level dimension) that has the names of the levels of the hierarchy as values. A level relation is dimensioned by the hierarchical dimension and has the values of the level dimension. For example, assume that there is hierarchical TEXT dimension named time, a level dimension for time named tlevels, and a level relation named time.tlevels that is dimensioned by time. Assume also that the time dimension has a unique value for months and years and the tlevels dimension has two values Month and Year. In this case, for each month value (for example, Feb 97), the time.tlevels relation has a value of Month; and, for each year value (for example, 1997), the time.tlevels relation has a value of Year.
Identifies the values by which Oracle OLAP performs the limit.
relation-dimension-name is the name of a dimension of the relation specified by level-relation-name.
inclusive-val-args specifies the values of relation-dimension-name to use when determining the values of level-relation-name by which to perform the limit. You can specify any inclusive valuelist argument as described in the syntax of the inclusive-val-args argument for the valuelist clause for LIMIT command (using values). When you omit this argument, Oracle OLAP uses the current status of the dimension when performing the limit.
Specifies the name of a valueset object is dimensioned by the level dimension for the hierarchical dimension that you want to limit. Assume that there are the objects described in the description of the level-relation parameter. Additionally, assume that you have defined a valueset named bestsalesyear that is dimensioned by tlevels and, for each value, contains only the values of time that pertain to the year with the best sales year (for example, 1998). In this case, for Month, bestsalesyear would have a list of all of the months in 1998 (that is, Jan98 through Dec98 and for Year would have only one value (1998).
Specifies that program execution should branch to label when the requested status has null status or is based on a related dimension that turns out to have null status (that is, to have no values). (See the main entry for LIMIT command for complete description of this phrase.)
Examples
Limiting to a Single Time Period of a Hierarchical Time Dimension
Assume that you have defined a hierarchical text dimension named time, a level dimension named timelevels that has Month and Year as values, and a relation named timelevelsrel that is dimensioned by time and that has timelevels as a related dimension (that is, for each value of the time dimension, timelevelsre contains a value of either Month or Year) When you want to limit the values of time that are already in status to only those values that are at the same level as Jan99, you can issue the following statement:
LIMIT time TO LEVELREL timelevelsrel