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 commandThe 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.
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 specified dimension status stack.
Syntax for Retrieving Values From a LIMIT Command
LIMIT([INTEGER] {dimension | valueset | LIMIT_function} [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])
See the LIMIT command for a complete description of this argument.
See the LIMIT command for a complete description of this argument.
Another LIMIT function.
Note:
When you nest LIMIT functions inside each other in this manner, the first argument of the innermost LIMIT function must be the a dimension or a valueset. See also "Nesting the LIMIT Function".See the LIMIT command for a complete description of this argument.
See the LIMIT command for a complete description of this argument.
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:
In the syntax of each of these LIMIT command topics, the limit-clause is that portion of the syntax following the limit-type argument.
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.
Specifies that Oracle OLAP retrieve the status list values for the status list stack for the specified dimension.
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.
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 evaluates the product sales in each month and district combination and produces 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
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