LIMIT function

The LIMIT function returns the dimension or dimension surrogate values that result from a specified LIMIT command or a specified dimension status stack. A dimension and any surrogate for that dimension share the same status. The LIMIT function does not change the status of a dimension or a valueset.

See also::

LIMIT command

Return Value

The return value varies depending on the use of the function and whether or not you specify the INTEGER keyword:

When the LIMIT function is an argument to an OLAP DML statement (including a user-defined command or function) that expects a valueset, it returns a valueset.
When the LIMIT function returns an empty valueset, it returns it as a valueset with null status.
In all other cases, the LIMIT function returns either a TEXT value or an INTEGER value depending on whether or not you include the INTEGER keyword. When it returns a TEXT value that represents empty status, it returns it as NA.

Syntax

The syntax of the LIMIT function varies depending on whether you want to retrieve the values of the dimension or dimension surrogate values that result from a specified LIMIT command or the values of a a specified dimension status stack.

Syntax for Retrieving Values From a LIMIT Command

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

[limit-clause] [IFNONE label])

Syntax for Retrieving Values From a Dimension Status Stack

LIMIT([INTEGER] dimension STATDEPTH stack-position] [IFNONE label])

Arguments

See the LIMIT command for a complete description of all arguments other than the INTEGER and STATDEPTH keywords.

dimension | valueset} [concat-component]

See the LIMIT command for a complete description of these arguments.

limit-type

See the LIMIT command for a complete description of this argument.

limit-clause

Specifies the values to use for the limit. There are several types of limit clauses— for example, a limit clause you can use to specifying the limit using values (including using a valueset) and a limit clause you can use to specify the limit using a related dimension. Each of these types of limit clauses has a very complex syntax. Because the syntax is complex, the syntax for the various types of limit clauses are documented separately as part of the following topics:

LIMIT (using values) command

LIMIT using LEVELREL command

LIMIT (using related dimension) command

LIMIT command (using parent relation)

LIMIT NOCONVERT command

LIMIT command (using POSLIST)

In the syntax of each of these LIMIT command topics, the limit-clause is that portion of the syntax following the limit-type argument.

INTEGER

When you use the INTEGER keyword, the function returns the position numbers of the values in the default dimension status rather than the names. When you use INTEGER with a valueset, the function returns the position numbers of the values in the default dimension status, not in the valueset.

STATDEPTH

Specifies that Oracle OLAP retrieve the status list values for the status list stack for the specified dimension.

stack-position

An INTEGER value that specifies the position in the status list stack from which to retrieve the values. Keep the following in mind when specifying a value:

Values from 0 to 1-STATDEPTH(dimension) retrieve stacked values from the top (current status) to the bottom (oldest status.)
Values from 1 to STATDEPTH(dimension) retrieve stacked values from the bottom of the stack (that is, the oldest status) to the top of the stack (that is, the current status).

See the STATDEPTH function for more information about status list stacks.

Notes

Nesting the LIMIT Function

Use the following syntax to return the result of several LIMIT commands for the same dimension by nesting the LIMIT function.

LIMIT (LIMIT (LIMIT (lim-exp1) lim-exp2) lim-exp3)

Use this nested construction to find the status of a series of LIMIT commands. For example, to see the status of the following commands

LIMIT product TO division 'Camping'
LIMIT product KEEP -
   EVERY(sales GT 50000, product)
LIMIT product KEEP FIRST 1

you execute this statement.

REPORT LIMIT(LIMIT(LIMIT(product TO -
   division 'Camping') KEEP EVERY -
   (sales GT 50000, product))KEEP FIRST 1)

Limiting with a Component of a Concat Dimension

You can limit a concat dimension to the current status of one of its component dimensions as in the following statement.

LIMIT(reg.dist.ccdim TO district)

You can also limit a concat dimension to a set of the values of one of its component dimensions as in the following statement.

LIMIT(reg.dist.ccdim TO district 'Boston' 'Chicago' 'Seattle')

Returning Multidimensional Results

The LIMIT function returns multidimensional results when evaluating multidimensional expressions. In the following example, the sales variable has three dimensions: product, district, and month.

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

A REPORT sales statement produces the following output.

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

Suppose you want a list of products whose sales exceed $90,000 for the status shown in the preceding report. The LIMIT function will evaluate the product sales in each month and district combination and will produce a list that is dimensioned by the months and districts in status.

A REPORT limit (product TO sales GT 90000) statement produces the following output.

---LIMIT (PRODUCT TO SALES GT---
          -------------90000)-------------
          -------------MONTH--------------
DISTRICT    Jan95      Feb95      Mar95
--------- ---------- ---------- ----------
Boston    Footwear   NA         Canoes
                                Footwear

Examples

Example 8-9 Returning Multidimensional Results

This example prints a report of the products whose sales were greater than $50,000 in the first two months of 1995 in Boston and Atlanta. Notice that the LIMIT function returns multidimensional results.

These statements

LIMIT month TO 'Jan95' 'Feb95'
LIMIT district TO 'Boston' 'Atlanta'
LIMIT product TO ALL
REPORT LIMIT (product TO sales GT 50000)

produce this report.

--LIMIT (PRODUCT TO--
               ---SALES GT 50000)---
               --------MONTH--------
DISTRICT         JAn95      Feb95
-------------- ---------- ----------
Boston         Canoes     Canoes
               Racquets   Racquets
               Sportswear Sportswear
               Footwear   Footwear
Atlanta        Racquets   Canoes
               Sportswear Racquets
               Footwear   Sportswear
                          Footwear

Example 8-10 LIMIT Command with the LIMIT Function

The following example shows the LIMIT function being used as an argument to the LIMIT command. The result of the LIMIT function is converted to a valueset.

ALLSTAT
LIMIT month TO LIMIT (LIMIT (month TO LAST 10) KEEP FIRST 3)

After the preceding LIMIT statement, a STATUS month statement produces this output.

The current status of MONTH is:
MAR97 TO MAY97