Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

LIMIT command

The LIMIT command sets the current status list of a dimension and its dimension surrogates, or assigns values to a valuesets the current status list of a dimension and its dimension surrogates, or assigns values to a valueset. You use LIMIT to restrict the data values you are working on by temporarily limiting the range of the dimensions of the data. Using LIMIT, you create a current status list for a dimension. The current status list of a dimension is an ordered list of currently accessible values for the dimension. Values that are in the current status list of a dimension are said to be "in status." For more information on dimension status and its importance when working with analytic workspace data, see "How to Specify the Set of Data that OLAP DML Operations Work Against".

Tip:

You set the current status list of one or more base dimensions of a composite, conjoint dimension, concat dimension, or a partition template based on the selected values of that object, see the LIMIT BASEDIMS command.

Syntax

LIMIT {dimension | valueset } [concat-component] limit-type [limit-clause] [IFNONE label]

where limit-type is one of the following keywords that specify how Oracle OLAP should modify the current status list:

TO
ADD
INSERT [FIRST|LAST|BEFORE position|AFTER position]
KEE
REMOVE
KEEP REORDER
COMPLEMENT
SORT [NAFIRST]

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.

concat-component

Specifies the name of the component of the concat dimension whose values are used to determine the limit. When you specify a value for concat-component, the limit sets the status of the specified concat dimension using the values of dimension which is a component of the concat dimension. This limit-clause applies only when dimension is a concat dimension. The status of a concat dimension and of its component dimensions are not shared. Changing the status of a component dimension after you have used that dimension as the limit-clause in setting the status of a concat dimension does not change the status of the concat dimension.

TO

Replaces the status of a dimension or valueset with the values specified by the limit-clause arguments. The TO keyword selects values from the default status of a dimension in the same order as they appear in the LIMIT statement or in the order implied by the valuelist argument. When you use arguments that imply ordering, the ordering of the values is based on their positions in the default status.

ADD

Expands the status of a dimension or valueset by adding the values specified by the limit-clause arguments that are not already in status. The ADD keywords selects values from the default status of a dimension in the same order as they appear in the LIMIT statement or in the order implied by the valuelist argument. When you use arguments that imply ordering, the ordering of the values is based on their positions in the default status. ADD adds unique dimension values in the specified order after the current status list or valueset list.

INSERT

Expands the status of a dimension or valueset by inserting the values specified by the limit-clause arguments in a specified position in the current status. The INSERT keyword selects values from the default status of a dimension in the same order as they appear in the LIMIT statement or in the order implied by the valuelist argument.

When you use arguments that imply ordering (for example,value1 TO value2), the ordering of the values is based on their positions in the default status. INSERT adds values to a specified position in the current status. When an added value is already in status, it is removed from its position in the current status and added in the order in which it appears in the valuelist, preserving the order of the added values.

FIRST

Inserts the new values before the first value in status.

LAST

Inserts new values after the last value in status.

BEFORE
AFTER

Specifies whether new values Oracle OLAP inserts new values before or after position in the current status.

position

A dimension value in the current status, a character expression whose value is a dimension value in the current status, or an INTEGER expression whose value represents the position of a dimension value in the default status.

KEEP

Reduces the status of a dimension or valueset by keeping only the values specified by the limit-clause arguments. Oracle OLAP performs the selection based on the current dimension status. KEEP preserves the current order of values among the values that remain in the status.

KEEP REORDER

Like a simple KEEP, KEEP REORDER reduces the status of a dimension or valueset by keeping only the values specified by the limit-clause arguments. Oracle OLAP performs the selection based on the current dimension status. However, KEEP REORDER orders the result in the order of the selection arguments (that is, the limit-clause arguments) rather than by the current status order.

REMOVE

Reduces the status of a dimension or a valueset by removing the values specified by the limit-clause arguments. Oracle OLAP performs the selection based on the current dimension status. KEEP preserves the current order of values among the values that remain in the status.

COMPLEMENT

Replaces the status of a dimension or valueset with the values that are not specified by the limit-clause arguments. When you do not specify any arguments after COMPLEMENT, status is replaced by all values not now in status. Oracle OLAP performs the selection based on the current dimension status. COMPLEMENT leaves dimension values that remain in their default order. (Abbreviated COMP)

SORT

Sorts the values of a dimension or valueset according to the limit-clause arguments. LIMIT creates a temporary list of values based on the limit-clause arguments, and uses this list to sort the current status list. Any values not present in the temporary list are moved to the end of the current status list.

NAFIRST

Specifies that NA values are placed first in the sort list rather than last.

limit-clause

Specifies the values to use for the limit. The syntax is quite complex and, consequently, has been divided into the following topics:

LIMIT (using values) command
LIMIT using LEVELREL command
LIMIT (using related dimension) command
LIMIT (using parent relation)
LIMIT NOCONVERT command
LIMIT command (using POSLIST)
IFNONE

(For use only within an OLAP DML program) 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). In either case, the null status is not put into effect when program execution branches. Instead, the original status, before the LIMIT statement was executed, is retained even when OKNULLSTATUS is YES. Within an OLAP DML program, you cannot use both IFNONE and NULL in the same statement.

label

The name of a label elsewhere in the program constructed following the "Guidelines for Constructing a Label". Execution of the program branches to the line directly following the specified label.

Note that label, as specified in IFNONE, must not be followed by a colon. However, the actual label elsewhere in the program must end with a colon.

Usage Notes

Specifying a Value of a Concat Dimension

To specify a value of a nonunique concat dimension, use the following syntax.

<base-dimension: value>.

Default Status List

When you first attach an analytic workspace, the current status list of each dimension consists of all of the values of the dimension that have read permission, in the order in which the values are stored. This list of values is called the default status list for the dimension.

Unique Values

LIMIT selects only unique values of a dimension. When a value appears more than once in a LIMIT statement, it is placed in status in the order of its first appearance. For example, the following lines.

LIMIT time TO 'Jan97', 'Feb97', 'Jan97'
STATUS time

produce this output.

The current status of TIME is:
JAN97, FEB97 

Nonexistent Values

Oracle OLAP does not signal an error when you try to set the status of a dimension or valueset that has no values, unless you explicitly list values that do not exist. For example, assume that you have not added any values to a newly defined dimension WEEK. In this case, the statement LIMIT week TO FIRST 10 does not cause an error. However, LIMIT week TO 'Pete' causes an error because Pete is not a value. Similarly, LIMIT week TO 20 causes an error because week does not have a value at position 20.

Setting the Status of a Dimension or Valueset to Null

Oracle OLAP allows the status of a dimension or valueset to be set to null (empty status) only when you have explicitly specified that you want null status to be permitted. You can give this permission in either of two ways:

  • Set the OKNULLSTATUS option to YES. This specification indicates that null status should be allowed whenever it occurs (unless the IFNONE argument is present in a LIMIT statement).

  • Use the NULL keyword in a LIMIT statement to set the status of a particular dimension or valueset to null. You can do this by specifying TO NULL or KEEP NULL. This specification indicates that null status should be allowed for this LIMIT statement only.

When you have not used either of these two methods to give permission for null status and you execute a LIMIT statement that would result in null status, Oracle OLAP does not change the status to null when it executes the statement. Instead, Oracle OLAP leaves the status as it was before the statement was issued and either signals an error (when IFNONE is not present) or branches to the IFNONE label (when IFNONE is present).

An IFNONE argument indicates that you do not want program execution to take its normal course when a dimension's status were to be set to null. Therefore, when IFNONE is present, Oracle OLAP branches to the IFNONE label and does not set the status to null, even when OKNULLSTATUS is YES. When the NULL keyword is present with IFNONE, Oracle OLAP signals the inconsistency with an error.

IFNONE requires the use of unstructured programming techniques. Oracle OLAP now provides alternative structured techniques, so the use of IFNONE is discouraged. IFNONE has been retained for compatibility with previous versions of Oracle OLAP.

Limiting a Conjoint

To limit a conjoint dimension to a value list, you can use the following constructions:

  • Specify the actual values, surrounding each combination with angle brackets

    LIMIT proddist TO '<Tents, Boston>' -
       '<Footwear, Denver>'
    

  • Use a variable name for the values, surrounding the combination with angle brackets.

    prodname = 'Canoes'
    distname = 'Seattle'
    LIMIT proddist To <prodname, distname>
    
  • Create a multiline list, where each line is a combination surrounded by angle brackets.

    namelist = '<Tents Boston>\n<Footwear, -
       Denver>\n <Canoes, Seattle>'
    LIMIT proddist TO namelist
    

  • Use the implicit relation between a conjoint dimension and its base dimension to limit the conjoint dimension. For example, use the following statement to limit PRODDIST to all conjoint values having "Canoes" as one of its base values.

    LIMIT proddist TO product 'Canoes'
    

    Note:

    You can use logical position numbers for base dimension values in a conjoint dimension. "Using INSTAT When the Dimension is a Conjoint Dimension" illustrates using logical position numbers

For an example of how you can limit a conjoint dimension that has a concat base dimension, see Example 10-25, "Limiting a Conjoint Dimension with a Concat Base Dimension".

Limiting a Concat

You can define a concat dimension using simple dimensions, conjoint dimensions, and other concat dimensions as the base dimensions of the concat. The syntax for limiting a concat dimension to one of its values is the following.

LIMIT concatdim TO <base-dim: value>

For example, the concat dimension reg.dist.ccdim has the simple dimensions region and district as its base dimensions. The following statement sets the status of reg.dist.ccdim to two of its values, region: East and district: Atlanta.

LIMIT reg.dist.ccdim TO <region: 'East'> <district: 'Atlanta'>

For other methods of setting the status of a concat dimension, see Example 10-16, "Limiting a Concat Dimension".

Alternative to Branching Using an IFNONE Label

As an alternative to branching to an IFNONE label, you can also handle null status for a dimension with the OKNULLSTATUS option. When you set OKNULLSTATUS to YES, then you are allowed to set the status of a dimension to null. You can then check for null status and execute appropriate commands with an IF...THEN...ELSE command, or you can handle null status as a case in a SWITCH command.

OKNULLSTATUS = YES
LIMIT month TO sales GT salesnum
IF STATLEN(month) LT 1
   THEN GOTO showerr

Examples

Example 10-14 Adding and Removing Values

These lines add values to the status for the month dimension.

LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT month ADD 'Jul96' 'Sep96'

Issuing a STATUS month statement produces this output.

The current status of MONTH is:
Jan96 TO Jul96, Sep96

This line removes values from the status for the month dimension.

LIMIT month REMOVE 'Jan96' TO 'Mar96'

Now, issuing a STATUS month statement produces this output

The current status of MONTH is:
Apr96 TO Jul96, Sep96

Example 10-15 Limiting with a Dimension Surrogate

A dimension and any dimension surrogates for it share the same status.

For example, assume that there is a NUMBER dimension named store_id that has the values 25, 410, 150, 205, 310, and 10. It also uses storepos, an INTEGER dimension surrogate for store_id. The dimension surrogate storepos has the values 1, 2, 3, 4, 5, and 6. A TEXT dimension surrogate for store_id is storename. It has the text values Raoul's - Boston, Poldy's Potpourri, Molly's Emporium, Raoul's - Atlanta, Kinch's Kitchen Supplies, and Raoul's - Chicago. The following statements are equivalent.

LIMIT store_id TO 25 410 150
LIMIT store_id TO storepos 1 2 3
LIMIT storepos TO 1 TO 3
LIMIT storepos TO first 3
LIMIT storename TO first 3
LIMIT storename TO 'Raoul\'s - Boston' TO 'Molly\'s Emporium'
LIMIT store_id TO storename storepos 1 2 3
LIMIT storename TO store_id 25 TO 150

The following statements set the status of the store_id dimension by limiting storename, which is a TEXT dimension surrogate for store_id, and report the values of store_id.

LIMIT storename TO 'Raoul\'s Sweets' TO 'Henry\'s Flowers'
REPORT store_id

The preceding statement produces the following output.

STORE_ID
--------------
10
20
30

Example 10-16 Limiting a Concat Dimension

In the following examples, the concat dimension reg.dist.ccdim has the simple dimensions region and district as its base dimensions. A concat dimension has an implicit relation to each of its component dimensions.

  • The following statement sets the status of the concat dimension using the related dimension syntax and specifying the positions of the component (related) dimension.

    LIMIT reg.dist.ccdim TO district 1, 4, 5
    

    Issuing a STATUS reg.dist.ccdim statement produces the following output.

    The current status of REG.DIST.CCDIM is:
    <DISTRICT: BOSTON>, <DISTRICT: DALLAS>, <DISTRICT: DENVER>
    
  • The following statement limits the concat dimension directly to the values specified by positions of the concat dimension.

    LIMIT reg.dist.ccdim TO 1, 4, 5
    

    Issuing a STATUS reg.dist.ccdim statement produces the following output.

    The current status of REG.DIST.CCDIM is:
    <REGION: EAST>, <DISTRICT: BOSTON>, <DISTRICT: ATLANTA>
    
  • The following statements set the status of district and then limit reg.dist.ccdim to the status of district.

    LIMIT district TO LAST 3
    LIMIT reg.dist.ccdim TO district
    

    Issuing a REPORT reg.dist.ccdim statement produces the following output.

    REG.DIST.CCDIM
    ----------------------
    <district: Dallas>
    <district: Denver>
    <district: Seattle>
    
  • In the following statement, the limit-clause argument is a list of values of the concat dimension.

    LIMIT reg.dist.ccdim TO <region: 'East'> <district:  'Boston'> <district: 'Atlanta'>
    
  • The following statements define a valueset for reg.dist.ccdim, store the current status of the concat dimension in the valueset, reset the status of the concat to ALL, and then limit the concat to the valueset and report the values of the concat in status.

    DEFINE regdist.vset VALUESET reg.dist.ccdim
    LIMIT regdist.vset TO reg.dist.ccdim
    LIMIT reg.dist.ccdim TO ALL
    LIMIT reg.dist.ccdim TO regdist.vset
    RPR W 22 reg.dist.ccdim
    

    The preceding statements produce the following result.

    REG.DIST.CCDIM
    ----------------------
    <region: East>
    <district: Boston>
    <district: Atlanta>
    

You can also limit a concat dimension using a valueset of one of its component dimensions:

  • When the component dimensions contain identical values, you can limit the concat dimension to those values by using a Boolean expression. When the district and region dimensions both have New York as a value, then the following statement limits the reg.dist.ccdim to those values.

    LIMIT reg.dist.ccdim TO BASEVAL(reg.dist.ccdim) EQ 'New York'
    
  • In the following example, the concat dimension geog has the simple dimension region and the conjoint dimension cityandstate as its base dimensions. The following statement sets the status of the concat dimension by limiting the conjoint base dimension.

    LIMIT geog TO cityandstate <'Princeton' 'New Jersey'> -
       <'Patterson' 'New Jersey'>
    

    Issuing a STATUS geog statement produces the following output.

    The current status of GEOG is:
    <CITYANDSTATE: <PRINCETON, NEW JERSEY>, <CITYANDSTATE: <PATTERSON, NEW JERSEY>>
    
  • The following statements sets the status of the concat dimension by limiting the conjoint base dimension by specifying a value of a base dimension of the conjoint dimension.

    LIMIT geog TO cityandstate city 'Princeton'
    RPR W 30 geog
    

    The preceding statement produces the following output.

    GEOG
    ------------------------------
    <cityandstate: <Princeton, New Jersey>>
    <cityandstate: <Princeton, Indiana>>
    

Example 10-17 Limiting with a Worksheet

This example shows how to limit a dimension to the values that are contained in a column of a worksheet. Here the dimension month is limited to the values that are contained in the first column of the worksheet workitem. The following statements produce a workitem report, which is shown following the statements.

LIMIT month TO ALL
LIMIT wkscol TO 1
LIMIT wksrow TO workitem NE NA
REPORT workitem
               -WORKITEM-
               --WKSCOL--
WKSROW             1
-------------- ----------
             1 Jan96
             2 Feb96
             3 Mar96
             4 Apr96
             5 May96
             6 Jun96
             7 Jul96
             8 Aug96
             9 Sep96
            10 Oct96
            11 Nov96
            12 Dec96

The following statement limits the month dimension to the values that are listed in the first column of workitem.

LIMIT month TO CHARLIST(workitem)

Issuing a STATUS month statement produces the following output.

The current status of MONTH is:
Jan96 TO Dec96

Example 10-18 Using Ampersand Substitution with LIMIT

Assume that you want specify exactly two products for a program named product.rpt. In this cae, you could declare two dimension-value arguments to handle them. But when you want to be able to specify any number of products using LIMIT commands, then you can use a single argument with ampersand substitution.

Suppose you use the following commands in your program.

ARGUMENT natext TEXT
ARGUMENT widthamt INTEGER
ARGUMENT rptprod TEXT
    ...
LIMIT product TO &rptprod

You can run the program and specify that you want the first three products in the report.

CALL product.rpt ('Missing' 8 'first 3')

The single quotation marks are necessary to indicate that "first 3" should be taken as a single argument, rather than two separate arguments separated by a space. The ampersand causes the LIMIT command to interpret 'first 3' as a keyword expression rather than as a dimension value.

Example 10-19 Branching on Null Status

Your program might try to set or refine the status of the product dimension to include only the products for which unit sales are greater than 500. When no products have unit sales of more than 500, then you can use the IFNONE keyword to specify that execution branch to the novals label.

LIMIT product KEEP units GT 500 IFNONE novals

In the commands following the novals label, you can handle the special situation in which no products have units sales greater than 500.


LIMIT (using values) command

A LIMIT command with a using values limit clause assigns values to a valueset or sets the current status list of a dimension or dimension surrogates to:

  • Specified value or values. The values can be any of the following:

    • Dimension values, expressed as literal values separated by commas, or as a multiline text expression, each line of which is a value of the dimension.

    • Ranges of dimension values, expressed as value1 TO value2.

    • Integer values that represent the logical positions of dimension values, expressed as comma-delimited INTEGER values.

    • Ranges of INTEGER values that represent the logical positions of dimension values, expressed as value1 TO value2.

    • Valuesets.

  • Values for which a Boolean expression is TRUE.

  • The top or bottom performers of a dimension based on a criterion

  • The top or bottom performers of a dimension, by percentage, based on a criterion represented as an expression

Syntax

LIMIT {dimension | valueset} [concat-component] limit-type -

     {inclusive-val-args....| exclusive-val-args} [IFNONE label]

where:

  • inclusive-val-args is one or more of the following constructs:


         intvaluelist 
         text-expression
         value1 TO value2
         valuelist
         valueset
  • exclusive-val-args is one of the following constructs:


         ALL
         boolean-expression
         {BOTTOM|TOP} BASEDON expression
         {BOTTOM|TOP} n-percent PERCENTOF expression
         {FIRST|LAST} n
         NTH {n |n TO n}...
         LONGLIST
         LONGLIST
    SESSION
    STATUS
         NULL

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.

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

intvaluelist

A list of one or more INTEGER values, or the name of a single-cell variable that holds a numeric value. Separate the values with commas (,). Numeric values with decimal places (SHORTDECIMAL or DECIMAL values) are automatically truncated to INTEGER values before being used as dimension values. An INTEGER specifies a dimension value by its logical position in the full set of dimension values. You cannot specify a NUMBER dimension value by an INTEGER position. When the values of the NUMBER dimension are INTEGER values, then you can set the status of the dimension by specifying dimension values, as in intvalue1, intvalue2 and so on.

text-expression

A multiline text expression, each line of which is a value of dimension.

value1 TO value2

Specifies a range of dimension values where value1 and value2 can be either INTEGER values or dimension values. Such a range can be increasing (for example, 1 to 10) or decreasing (for example, 10 to 1). When you specify an INTEGER value, that value is the logical position of a value in the default status list for the dimension. When you specify a dimension value, the command convertsthe value to the logical position of the value in the default status list for the dimensioon. The current status of the dimension or valueset is assigned accordingly.

Tip:

You cannot specify the values of a NUMBER dimension using INTEGER positions. Instead, define an INTEGER dimension surrogate for the NUMBER dimension and limit the dimension by the positions of the surrogate.
valuelist

A list of one or more values of dimension. A dimension value can be specified as a text expression whose value is a valid dimension value. For a NUMBER dimension, dimension values are numbers. For dimensions with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, dimension values can also be specified as DATE expressions.

valueset

An analytic workspace valueset object that is a saved list that holds the values for the dimension whose status is being set. You cannot define a valueset for a dimension surrogate, therefore you cannot specify a valueset when setting the status of a dimension surrogate. However, when you limit a dimension with a valueset, then you automatically limit to the same set any dimension surrogates of that dimension. You can also specify a LIMIT function.

ALL

Specifies that all dimension values in the default status are to be included in the status. The default status is made up of all dimension values for which read permission is granted, in the same order as when the dimension was last maintained. When you start up an analytic workspace, the status for each dimension in your analytic workspace is the default status. Changing the read permission for a dimension with PERMIT or PERMITRESET statement changes the default status for the dimension.

boolean-expression

An expression whose TRUE values are used by Oracle OLAP when limiting the dimension or status. The boolean-expression must be dimensioned by the dimension whose status is being set. For a dimension surrogate, the Boolean expression is evaluated over the dimension for which it is a surrogate. The data types of the expressions you are comparing in the Boolean expression must be similar. See the CONVERT function for information on converting data types. To correctly use LIMIT with a Boolean expression you must understand how it works with a Boolean expression that has with multiple dimensions, see "How LIMIT Handles Boolean Expressions With More Than One Dimension" for details.

BOTTOM  n BASEDON expression
TOP n BASEDON expression

Specifies that the status of a dimension or valueset is set based on a criterion, where n is the number of values to select and expression is the criterion on which to base the selection. All dimensions of expression other than the one whose status is being set must be limited to a single value. TOP results in the status sorted in descending order, BOTTOM results in the status sorted in ascending order. You cannot use a composite after the BASEDON keyword. When you attempt to do so, an error message is displayed.

BOTTOM n-percent PERCENTOF expression
TOP n-percent PERCENTOF expression

Specifies that the status of a dimension or valueset is set by finding the top or bottom performers based on a criterion represented as an expression. This construction sorts values and adds them to the status that is based on their contribution, by percentage, to an expression.

For example, the following statement sorts products in descending order by each product's contribution to TOTAL(sales) and then add values to the status, starting from the top, until the cumulative total of sales by product reaches or exceeds 30 percent of all sales.

LIMIT product TO TOP 30 PERCENTOF TOTAL(sales, product)

Important:

Do not use a criterion expression that causes a side effect or changes its own value.
FIRST n
LAST n

Specifies the first n, last n values in the dimension's full set of values when used with TO, ADD, COMPLEMENT, or INSERT. When used with KEEP or REMOVE, specifies the first n, last n or nth values in the current status.

Important:

It can happen that the last item in status, based on a PERCENTOF criterion, is one of several dimension values having the same associated criterion value. In this case, LIMIT includes all dimension values with that criterion value in the resulting status, even when that causes the total of the criterion value to far exceed the specified percentage.
NTH {n |n TO n}

Specifies the n values in the dimension's full set of values when used with TO, ADD, COMPLEMENT, or INSERT. When used with KEEP or REMOVE, specifies the n values in the current status. You can specify any number of values or range of values.

LONGLIST

Indicates that there can be up to 2,000 arguments in the LIMIT statement. When there are less than 300 arguments, LONGLIST is not needed.

SESSION

Specifies that Oracle OLAP use only those dimension members that were created using a MAINTAIN ADD SESSION statement when performing the limit.

STATUS

Specifies that Oracle OLAP use the values that are presently in status when performing the limit. Specifying this keyword is equivalent to (but more efficient than) using a VALUES (dimname) statement.

NULL

Indicates an empty dimension or valueset list. Using this keyword with the TO or KEEP arguments removes all values from the current status, leaving an empty dimension or valueset list, even when OKNULLSTATUS is NO. You cannot use IFNONE and NULL in the same LIMIT statement. ADD, INSERT, and REMOVE NULL leave status unchanged. COMPLEMENT NULL places all values in status.

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

Considerations When Specifying Values

Keep the following points in mind when specifying values in limit-clause:

  • The setting of the LIMITSTRICT option determines how Oracle OLAP behaves when a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value. By default, when you specify a nonexistent value, Oracle OLAP treats the nonexistent value as an invalid value and stops executing the limit and issues an error. If, instead, you want Oracle OLAP to treat a nonexistent value as an NA value, set the value of LIMITSTRICT to NO.

  • You can embed a quoted string within a quoted string, which is necessary when there are special characters in a base dimension value of a composite or conjoint dimension, such as Joe's Deli. See the "Text Literals".

  • When the dimension has the NTEXT data type and an argument that represents a dimension value has the TEXT data type, LIMIT converts the argument value to NTEXT. Similarly, when the dimension has the TEXT data type and an argument that represents a dimension value has the NTEXT data type, LIMIT converts the argument value to TEXT; however, in this case, the conversion can result in data loss when the NTEXT value cannot be represented in the database character set.

  • When you specify a value of a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the value can be in the format specified by the VNF (value name format) for the dimension (or in the default VNF for the type of dimension you are limiting when the dimension does not have a VNF) or in a valid input style for DATE values.

    You must only provide the date components that are relevant for the type of dimension you are limiting. For a DAY or WEEK dimension, you must supply the day, month, and year components. For a MONTH or QUARTER dimension, you must only supply the month and year (for example, Jun95 or 0695 for June 1995). For a YEAR dimension, you must only specify the year (for example, 95 for 1995). The valid input styles for dates are discussed in DATEORDER.

    When you specify a DATE expression or a text value that represents a complete date, you can specify any date that falls within the time period that is represented by the desired dimension value. Oracle OLAP uses the DATEORDER option to resolve any ambiguities.

How LIMIT Handles Boolean Expressions With More Than One Dimension

When you have used this type of LIMIT command (or LIMIT function) to limit only one dimension of a multi-dimensional Boolean expression, you effectively limit that expression to the values of limited dimension and only the current values of the unlimited dimensions.

For example, assume that you you have a sales variable is dimensioned by three dimensions: product, district, and month.

Let's look first at what happens when you explicitly limit district and month dimensions to single values and then limit product using a Boolean expression.

LIMIT month TO 'Jan95'
LIMIT district TO 'Boston'
LIMIT product TO sales GT 90000
STATUS product

The STATUS statement produces the following output.

The current status of PRODUCT is:
Footwear

In this case, the resulting status is all of the products whose sales exceed $90,000 for the month of January 1995 in the Boston district, which is only Footwear.

Now consider the following example in which the MONTH dimension is not limited to a single value.

LIMIT product TO ALL
LIMIT month TO 'Jan95' 'Feb95' 'Mar95'
LIMIT district TO 'Boston'

When you execute a REPORT sales statement, you can see the BOSTON sales figures for three months.

DISTRICT: BOSTON
               -------------SALES--------------
               -------------MONTH--------------
PRODUCT          Jan95      Feb95      Mar95
-------------- ---------- ---------- ----------
Tents           32,153.52  32,536.30  43,062.75
Canoes          66,013.92  76,083.84  91,748.16
Racquets        52,420.86  56,837.88  58,838.04
Sportswear      53,194.70  58,913.40  62,797.80
Footwear        91,406.82  86,827.32 100,199.46

However, the following LIMIT and STATUS commands produce the output shown following them. Again, only Footwear is in the status for month.

LIMIT product TO sales GT 90000
STATUS product
 
The current status of PRODUCT is:
Footwear

In this case, each product has three sales figures, one for each month. For each product, LIMIT evaluates the sales data for only the first month in status. A product is added to the status when its sales data exceeds $90,000 in that month.

When you would like all months evaluated for each product, you can use the EVERY, ANY, or NONE functions. For example, the following LIMIT statement adds a product to the status when any of its months has a sales figure that exceeds $90,000.

LIMIT product TO ANY(sales GT 90000, product)

In this case a STATUS product statement produces the following output.

The current status of product is:
Canoes, Footwear

Limiting Using Implicit Relations

Every dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR is related to all other dimensions of this type through an implicit relation. When you limit the values of one DAY, WEEK, MONTH, QUARTER, or YEAR dimension by specifying another DAY, WEEK, MONTH, QUARTER, or YEAR dimension as the related-dimension, Oracle OLAP uses the implicit relation by default. However, when an explicit relation is defined between the two of these types of dimensions, you can override the default by specifying the name of the explicit relation as the related-dimension. For example, you can issue the following statement.

LIMIT month TO quarter year

This statement temporarily limits quarter to year, then limits month to quarter, and finally, restores quarter to its original status.

Examples

Example 10-20 Using LIMIT to Partially Populate Variables

DEFINE GEOG DIMENSION TEXT
DEFINE PRODUCTS DIMENSION TEXT
DEFINE SALES VARIABLE DECIMAL <PRODUCTS GEOG>
DEFINE COSTS VARIABLE DECIMAL <PRODUCTS GEOG>

Assume also as shown by the following reports that you have populated the dimensions but not the variables. All of the elements of the costs and sales variables appear in the report and all have the value of NA.

PRODUCTS
------------
TVs
Radios
Skis
Bikes
 
GEOG
------------
Boston
Springfield
New Orleans
Baton Rouge
Quebec City
Montreal
Toronto
Norfolk
 
             -------------------SALES-------------------
             -----------------PRODUCTS------------------
GEOG            TVs       Radios      Skis      Bikes
------------ ---------- ---------- ---------- ----------
Boston               NA         NA         NA         NA
Springfield          NA         NA         NA         NA
New Orleans          NA         NA         NA         NA
Baton Rouge          NA         NA         NA         NA
Quebec City          NA         NA         NA         NA
Montreal             NA         NA         NA         NA
Toronto              NA         NA         NA         NA
Norfolk              NA         NA         NA         NA
 
             -------------------COSTS-------------------
             -----------------PRODUCTS------------------
GEOG            TVs       Radios      Skis      Bikes
------------ ---------- ---------- ---------- ----------
Boston               NA         NA         NA         NA
Springfield          NA         NA         NA         NA
New Orleans          NA         NA         NA         NA
Baton Rouge          NA         NA         NA         NA
Quebec City          NA         NA         NA         NA
Montreal             NA         NA         NA         NA
Toronto              NA         NA         NA         NA
Norfolk              NA         NA         NA         NA
 

Now you issue the following LIMIT command so that only values indexed by Boston and Springfield values of the geog dimension are accessible to Oracle OLAP.

LIMIT geog TO 'Boston' 'Springfield'
 

Now you issue new reports for geog, costs, and sales. For the geog dimension only the Boston and Springfield elements values appear. Also, only the elements of the costs and sales variables that are indexed by Boston and Springfield appear in the report.

GEOG
------------
Boston
Springfield
 
             -------------------SALES-------------------
             -----------------PRODUCTS------------------
GEOG            TVs       Radios      Skis      Bikes
------------ ---------- ---------- ---------- ----------
Boston               NA         NA         NA         NA
Springfield          NA         NA         NA         NA
 
             -------------------COSTS-------------------
             -----------------PRODUCTS------------------
GEOG            TVs       Radios      Skis      Bikes
------------ ---------- ---------- ---------- ----------
Boston               NA         NA         NA         NA
Springfield          NA         NA         NA         NA
 

Now you issue two assignment statements that use the RANDOM function to populate the costs and sales variables, followed by the LIMIT command that sets the status of the geog dimension to its default status of ALL.

 
sales = RANDOM (200)
costs = RANDOM (100)
LIMIT geog to ALL
 

Now you issue new reports for geog, costs, and sales. All of the values of the geog dimension and all of the elements of the costs and sales variables appear. However, only the elements of the costs and sales variables that are indexed by Boston and Springfield have non-NA values.

 
GEOG
------------
Boston
Springfield
New Orleans
Baton Rouge
Quebec City
Montreal
Toronto
Norfolk
 
             -------------------SALES-------------------
             -----------------PRODUCTS------------------
GEOG            TVs       Radios      Skis      Bikes
------------ ---------- ---------- ---------- ----------
Boston           199.97     133.82      10.07     148.17
Springfield      173.94      27.56      32.21      47.40
New Orleans          NA         NA         NA         NA
Baton Rouge          NA         NA         NA         NA
Quebec City          NA         NA         NA         NA
Montreal             NA         NA         NA         NA
Toronto              NA         NA         NA         NA
Norfolk              NA         NA         NA         NA
 
             -------------------COSTS-------------------
             -----------------PRODUCTS------------------
GEOG            TVs       Radios      Skis      Bikes
------------ ---------- ---------- ---------- ----------
Boston            43.52      25.32      68.68      10.38
Springfield        9.49      27.96      61.76      16.12
New Orleans          NA         NA         NA         NA
Baton Rouge          NA         NA         NA         NA
Quebec City          NA         NA         NA         NA
Montreal             NA         NA         NA         NA
Toronto              NA         NA         NA         NA
Norfolk              NA         NA         NA         NA

Example 10-21 Limiting with a Boolean Expression

You can limit a dimension or valueset according to the values of a Boolean expression. In this example, the values of the TOTALL function are broken out by product and compared to a constant. The LIMIT statement sets the status to all the products whose sales, totaled for all months and districts, are greater than 12 million.

LIMIT product TO TOTAL(sales product) GT 12000000

Example 10-22 Limiting with a Formula

When you use the same criterion frequently to limit a dimension, you can save the expression as a formula and use the name of the formula as the limit expression.

DEFINE criterion FORMULA TOTAL(sales product) GT 12000000
LIMIT product TO criterion

Example 10-23 Limiting with a Valueset

You can save a status list in a valueset and use those values later to limit the status. When it takes several LIMIT commands to produce the status list you want, the valueset keeps you from having to repeat those LIMIT commands each time you need the same list. The following statements limit district to the districts in which sportswear sales exceeded $1,000,000 in 1996. The status is saved in the valueset sports.district, and you can limit district to the same list with one LIMIT statement.

DEFINE sports.district VALUESET district
LIMIT product TO 'Sportswear'
LIMIT month TO year 'Yr96'
LIMIT sports.district TO TOTAL(sales district) GT 1000000
LIMIT district TO sports.district

Issuing a STATUS district statement produces this output.

The current status of DISTRICT is:
ATLANTA TO DENVER

Example 10-24 Limiting with a Variable

Here the TOP and BASEDON keywords are used to limit the status of a dimension, using the values of a variable as a criterion. The status list is sorted in descending order according to the values of sales.

LIMIT product TO 'Sportswear'
LIMIT month TO 'Jul96'
LIMIT district TO TOP 2 BASEDON sales

The following REPORT statement

REPORT DOWN district sales

produces this output, which shows the results of the LIMIT commands.

PRODUCT: SPORTSWEAR
               --SALES---
               --MONTH---
DISTRICT         Jul96
-------------- ----------
Dallas         220,416.81
Atlanta        211,666.14

Example 10-25 Limiting a Conjoint Dimension with a Concat Base Dimension

Assume that your analytic workspace contains a conjoint dimension named prod.regdist that has the product simple dimension and the reg.dist.ccdim concat dimension as its base dimensions. The conjoint dimension prod.regdist has the following values.

Tents   <region: East>
Tents   <region: West>
Canoes  <region: East>
Canoes  <region: West>
Tents   <district: Boston>
Tents   <district: Atlanta>
Tents   <district: Denver>
Canoes  <district: Atlanta>
Canoes  <district: Seattle>

There are two different ways that you can set the status of a conjoint dimension that has a concat dimension as a base dimension:

  • By specifying the concat dimension, one of its component dimensions, and a value of the component dimension. The following LIMIT statement sets the status of prod.regdist in this manner.

    LIMIT prod.regdist TO reg.proddist.ccdim district 'Atlanta'
    RPR W 20 prod.regdist
    

    The preceding statement produces the following output.

    --------------PROD.REGDIST---------------
          PRODUCT           REG.DIST.CCDIM
    -------------------- --------------------
    Tents                <district: Atlanta>
    Canoes               <district: Atlanta>
    
  • You can also set the status of the conjoint by specifying its values. The following LIMIT statement sets the status of prod.regdist in this manner.

    LIMIT prod.regdist TO <'Tents' '<region: East>'> <'Tents' '<district: Boston>'>
    RPR W 20 prod.regdist
    

    The preceding statement produces the following output.

    --------------PROD.REGDIST---------------
          PRODUCT           REG.DIST.CCDIM
    -------------------- --------------------
    Tents                <region: East>
    Tents                <district: Boston>
    

LIMIT using LEVELREL command

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]

where the syntax of level-relation-clause varies depending on the dimensionality of the object you want to specify:

  • To specify a level relation which is multidimensional you 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.)

  • To specify a level relation which is one-dimensional, you can use the following simplified syntax:

     level-relation-name [valueset2]   

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

LEVELREL

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.

RELATION

Specifies that Oracle OLAP performs the limit based on the values of the relation specified by level-relation-name. Specify this keyword if level-relation-name is a multidimensional relation.

level-relation-name

Specifies the name of a level relation for the hierarchical dimension you want to limit. See "Levelrel Relation" for more information.

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

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. (See LIMIT (using values) command for detailed syntax.) When you omit this argument, Oracle OLAP uses the current status of the dimension when performing the limit.

valueset2

Specifies the name of a valueset object is dimensioned by the level dimension for the hierarchical dimension that you want to limit. The result of using this argument is the individual valueset that corresponds to the level value of the current position of the dimensions.

You can also specify aLIMIT function.

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

Examples

Example 10-26 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

This is equivalent to issuing the following statement

LIMIT time TO RELATION timelevelsrel QUALIFY time CURRENT

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:

  • 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 one-dimensional relation.)

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:

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

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

Example 10-27 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

LIMIT (using parent relation)

A LIMIT command that uses a parent relation in its limit clause to set the status of a hierarchical dimension or its dimension surrogate, or assigns values to a valueset, based on family relationships within the hierarchy.

Syntax

LIMIT {dimension | valueset} [concat-component] limit-type-

     [family-keyword ] USING parent-relation-clause [IFNONE label]

where:

  • family-keyword has one of the following constructs:


         PARENTS
         CHILDREN
          ANCESTORS [DISTANCE generation ]
          DESCENDANTS [DISTANCE generation ]
          SIBLINGS
    TOPANCESTORS
    BOTTOMDESCENDANTS
          HIERARCHY [INVERTED] [NOORIGIN] [SKIP n] [DEPTH n] [RUN textexp]] 
  • The syntax for parent-relation-clause varies depending on its dimensionality of the object you want to specify:

    • When the parent relation is multidimensional, use the following syntax:

      RELATION parentrel [QUALIFY relation-dimension-name [inclusive-val-args... | CURRENT]...]

      (You can also use this syntax when the parent relation is one-dimensional.)

    • When the parent relation is one-dimensional, you can use the following simplified syntax:

       parentrel [inclusive-val-args | CURRENT] 

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

PARENTS

Finds the parent of each value in valuelist. For a dimension, when there is no valuelist, finds the parent for each value in status. For a valueset, when there is no valuelist, it finds the parent of each value in the valueset. It uses the parent-relation to look up the parent.

CHILDREN

Finds the children of each value in valuelist. For a dimension, when there is no valuelist, finds the children for each value in status. For a valueset, when there is no valuelist, it finds the children of each value in the valueset. It uses the parent-relation to look up the children.

ANCESTORS [DISTANCE generation]

When you do not include the DISTANCE phrase:

  • Finds the ancestors (that is, parents, grandparents, and so on) of each value in valuelist. For a dimension, when there is no valuelist, it finds the ancestors of each value in status.

  • For a valueset, when there is no valuelist, it finds the ancestors of each value in the valueset. In other words it finds "parents" for the values and the "parents of the parents" until there are no new parents.

When you include the DISTANCE phrase, limits to the ancestors who are members of the generation specified by generation:

  • For generation, specify 0 for the current generation, 1 for parents, 2 for grandparents, 3 for great grandparents, and so on.

  • For negative values, the command returns descendant generations (that is -1 returns children, -2 returns grandchildren, and so on).

DESCENDANTS [DISTANCE generation]

When you do not include the DISTANCE phrase:

  • Finds the descendants (that is, children, grandchildren, and so on) of each value in valuelist. For a dimension, when there is no valuelist, it finds descendants for each value in status.

  • For a valueset, when there is no valuelist, it finds the descendants of each value in the valueset. In other words, it finds the children of the values and the children of the children until there are no new children.

When you include the DISTANCE phrase, limits to the descendants who are members of the generation specified by generation:

  • For generation, specify 0 for the current generation, 1 for children, 2 for grandchildren, 3 for great grandchildren, and so on.

  • For negative, the command returns ancestor generations (that is -1 returns parents, -2 returns grandparents, and so on).

SIBLINGS

Finds all siblings of each value in valuelist, including the valuelist values, themselves. Issuing one LIMIT statement with the SIBLIGS keyword is the same as issuing two consecutive LIMIT statements: 1) LIMIT with PARENTS, 2) LIMIT with CHILDREN. For a dimension, when there is no valuelist, it finds siblings for each value in status. For a valueset, when there is no valuelist, it finds the siblings of each value in the valueset.

TOPANCESTORS

(Abbreviated TOPANC) Finds those members that are at the top of the hierarchy; that is, those members that do not have any ancestors which is equivalent to issuing the following two LIMIT commands.

   LIMIT dimension to ANCESTORS ....
   LIMIT dimension REMOVE DESCENDANTS....
BOTTOMDESCENDANTS

(Abbreviated BOTTOMDESC) Finds those members that are at the bottom of the hierarchy; that is, those members that do not have any descendants.

HIERARCHY

Finds the descendants (that is, children, grandchildren, and so on) based on a particular parent-relation. The difference is the order of the values. DESCENDANTS groups the values by level (all children, then all grandchildren, and so on); HIERARCHY places each group of children next to its parent. HIERARCHY includes the original values (that is, those in status before the LIMIT statement was executed) in status.

INVERTED

Indicates that children should be listed before their parents. By default, children are listed after their parents.

NOORIGIN

Excludes the original values from the status. The default is to include original values.

SKIP

Skips n generations for each value in valuelist. For dimensions, when there is no valuelist, it skips n generations for each value in status. For a valueset, when there is no valuelist, it skips n generations for each value in the valueset. This keyword, in combination with DEPTH, is helpful when drilling down; see Example 10-29, "Drilling Down Using SKIP and DEPT".

DEPTH

Includes n generations down from each value of valuelist. For dimensions, when there is no valuelist, it includes n generations for each value in status. For a valueset, when there is no valuelist, it includes n generations of each value in the valueset. The default depth value is 99. This keyword, in combination with SKIP, is helpful when drilling down on values.

RUN

Executes a statement, represented as a text expression, every time a group of children is constructed. For example, you can sort each group of children based on information stored in an Oracle OLAP variable. In the following statement, markets are sorted in increasing order based on unit sales every time a group of children is constructed.

LIMIT market TO HIERARCHY RUN 'SORT market a unit.m' USING -
  market.market

Note:

In this example, when you use KEEP or REMOVE instead of TO in your LIMIT statement, the SORT statement would have no effect.
USING 

Specifies the values to use when determining parent values.

parentrel

Specifies the name of the parent relation for the dimension.

To limit a dimension surrogate, use the parent relation for the dimension for which it is a surrogate.

RELATION  

Identifies the beginning of the parent-relation-clause. You use this keyword when parentrel is a multidimensional relation.

QUALIFY relation-dimension-name 

Specifies the name of a dimension of parentrel. The use of this clause varies depending on whether you are coding a LIMIT command or a LIMIT function as described in "Looping Behavior of LIMIT (using parent relation)".

inclusive-val-args

Specifies the values 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.

CURENT

Specifies that you want to limit to the values of the children of the current value of the dimension. (This is the same as specifying dimension_name +0.)

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

Looping Behavior of LIMIT (using parent relation)

When you do not include a QUALIFY clause, the set of values that this LIMIT puts into status when parentrel is multi-dimensional varies depending on whether or not you are issuing a LIMIT command or a LIMIT function:

  • LIMIT command. All of the in-status values of the related dimensions of parentrel are included in the set of in-status values. If you only want the current values of the related dimensions of parentrel to be included in the set of in-status values, specify a QUALIFY related-dimension CURRENT clause for each of the related dimensions of parentrel.

  • LIMIT function. Only the current values of the related dimensions of parentrel are included in the set of in-status values. If you want all of the in-status values of the related dimensions of parentrel to be included in the set of in-status values, specify a QUALIFY related-dimension related-dimension clause for each of the related dimensions of parentrel.

Examples

Example 10-28 A Simple Drill Down

This example drills down on districts from the region level of the market dimension. First, the market dimension, which has embedded totals at the district, region, and total U.S. level, is limited to the region level data. This LIMIT is done using the relation mlv.market, which is a relation between market and market.level.

Issuing a REPORT mlv.market statement produces the following output, which shows the values of mlv.market.

MARKET         MLV.MARKET
-------------- ----------
Totus          Totus
East           Region
Boston         District
Atlanta        District
Central        Region
Chicago        District
Dallas         District
West           Region
Denver         District
Seattle        District

The following LIMIT statement limits the values of MARKET, and the STATUS statement produces the values currently in status. The output of STATUS is shown following the statements.

LIMIT market TO mlv.market 'Region'
STATUS market
 
The current status of MARKET is:
EAST, CENTRAL, WEST

To drill down on the district level data from the region level, you can use LIMIT with the CHILDREN keyword. The following example uses a parent-relation called market.market to perform the drill down. For each value of the market dimension, this relation contains the name of its parent.

DEFINE market.market RELATION market <market>
LD Self-relation for the Market Dimension

A report of market.market produces the following output.

MARKET         MARKET.MARKET
-------------- -------------
Totus          NA
East           Totus
Boston         Central
Atlanta        East
Central        Totus
Chicago        Central
Dallas         Central
West           Totus
Denver         West
Seattle        West

You can limit market to the children of the East, Central, and West regions by using the CHILDREN keyword with LIMIT.

LIMIT market TO mlv.market 'Region'
Limit market TO CHILDREN USING market.market

A report of market produces the following output.

MARKET
-------------
Boston
Atlanta
Chicago
Dallas
Denver
Seattle

Example 10-29 Drilling Down Using SKIP and DEPT

Consider the following statement.

LIMIT market TO HIERARCHY DEPTH 2 SKIP 1 USING market.market 'Totus'

Oracle OLAP looks in the child-parent relation (market.market) to find the children and the grandchildren (DEPTH 2) of Totus and it discards the first generation (SKIP 1). The resulting status follows.

Totus
Boston
Atlanta
Chicago
Dallas
Denver
Seattle

Note that Totus is included in status. With HIERARCHY, the original values are included in status.


LIMIT NOCONVERT command

The LIMIT command sets the current status list of a dimension and its dimension surrogates, or assigns values to a valueset.

A LIMIT command with the NOCONVERT keyword sets the status of one dimension based on the numeric position of values in a different dimension.

Syntax

LIMIT{dimension | valueset} [concat-component] limit-type -

     NOCONVERT [{unrelated-dimension|numeric-valueset}] -

     [IFNONE label]

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

NOCONVERT

Sets the status of a dimension based on the numeric position of the specified values in the status list of an another dimension.

unrelated-dimension

Specifies the name of a dimension not related to the dimension being limited. Using this argument specifies that the status of a dimension or valueset is set based on the numeric position of each value in status of the unrelated-dimension. Setting status based on in status values in unrelated dimensions is particularly useful when the two dimensions are in different analytic workspaces (for example, when a one-to-one correspondence exists between the product dimension in two analytic workspaces).

numeric-valueset

Specifies a numeric valueset. When you use this argument, NOCONVERT sets the status based on the numeric values in the valueset. The numeric values represent the positions of the values in the default status of the dimension.

IFNONE label

(For use only within an OLAP DML program) 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.)


LIMIT command (using POSLIST)

The LIMIT command sets the current status list of a dimension and its dimension surrogates, or assigns values to a valueset.

A LIMIT command with the POSLIST keyword sets the status of a dimension based on the position of the values within that dimension.

Syntax

LIMIT {dimension | valueset} [concat-component] limit-type -

     POSLIST poslist-exp [IFNONE label]

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 standard keyword (documented in the main entry for LIMIT command) that specifies how Oracle OLAP should modify the current status list.

POSLIST poslist-textexp

Sets the status of a dimension based on the position of a value within a dimension. poslist-textexp is a text expression, each line of which is a numeric value that evaluates to a numeric position of the dimension being limited.

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