Previous
Previous
 
Next
Next


LIMIT (using related dimension) command

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:

Parameters

dimension

The name of the dimension or dimension surrogate for which you are setting status.

valueset

The name of the valueset for which you are assigning values; or a LIMIT function.

concat-component

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.)

limit-type

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.)

RELATION relation-name

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.

QUALIFY relation-dimension-name [inclusive-val-args]

Identifies the values by which Oracle OLAP performs the limit, where:

related-dimension-name

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
related-dimension-valuelist

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.

IFNONE label

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:

  1. The dimension values are arranged in the order of the values of the related dimension.

  2. 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 is NO, 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