![]() Previous |
![]() Next |
A LIMIT command with a related-dimension limit clause that uses the values of a different related dimension to assign values to a valueset or to set the status of a dimension or a dimension surrogate.
Syntax
LIMIT {dimension | valueset} limit-type related-dim-clause [IFNONE label]
where the syntax of related-dim-clause varies depending on the type of object being specified:
When you want to specify a relation, the syntax is:
RELATION relation-name [QUALIFY relation-dimension-name [inclusive-val-args...]...]
When you want to specify a dimension that is related to the dimension being limited, the syntax is:
related-dimension-name [related-dimension-valuelist]
(You can also use this simplified syntax when the object is a a one-dimensional relation.)
Parameters
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; or a LIMIT function.
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.)
Specifies that Oracle OLAP performs the limit based on the values of the relation specified by relation-name. Specify this keyword if relation-name is a multidimensional relation. Also, when there are multiple relations between the dimension being limited and the dimension specified by related-dimension-name, you can use this phrase to identify which relation Oracle OLAP uses to perform the limit.
Identifies the values by which Oracle OLAP performs the limit, where:
relation-dimension-name is the name of a dimension of the relation specified by relation-name.
inclusive-val-args specifies the values of relation-dimension-name to use when determining the parent values. 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. See LIMIT (using values) command for detailed syntax. When you omit this argument, Oracle OLAP uses the current status list of the related dimensions when performing the limit.
Specifies the name of a one-dimensional relation or a dimension that is related to the dimension being limited. For related-dimension-name, you can also specify a dimension surrogate for the dimension you are limiting, or a dimension surrogate of the related dimension. For example, dimsurr
is a dimension surrogate of dim2
and dim2
is related to dim1
. The dimension surrogate dimsurr
has the values Dsv1
, Dsv2
, Dsv3
and Dsv4
. The following statement limits dim1
by specifying values of dimsurr
.
LIMIT dim1 TO dimsurr dsv1 dsv3
The values of the related dimension or a dimension surrogate for the related dimension or the dimension specified using the syntax shown in LIMIT command. See LIMIT (using values) command for detailed syntax. When this argument is present in a LIMIT statement, status is obtained by selecting the values of the dimension being limited, which are related to the related-dimension values. When valuelist is omitted, the current status value of related-dimension is used.
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.)
Usage Notes
Limiting to a Related Dimension Is a Two-Step Process
When you limit a dimension or valueset to a related dimension, the resulting status is determined in a two-step process:
The dimension values are arranged in the order of the values of the related dimension.
When there are multiple values of the dimension for any value of the related dimension, those values are arranged in the order of their default status.
Suppressing the Sort When Limiting to a Related Dimension
You can suppress the sort that occurs when you limit a dimension or valueset to a related dimension by setting LIMITSORTREL to NO
which can significantly improve performance when the dimension you are limiting is large.
Note: When LIMIT.SORTREL isNO , printed output of a dimension may not appear in logical order. |
Examples
Limiting with a Related Dimension
Here the status of a dimension is limited using a related dimension. This statement limits district
to Boston
and Atlanta
, which are in the East
region.
LIMIT district TO region 'East'
This statement limits product
to Sportswear
and Footwear
, which are in the division that appears last in the list of division
values.
LIMIT product TO division LAST 1