LIMIT function

The LIMIT function returns the dimension or dimension surrogate values that result from a specified LIMIT command. A dimension and any surrogate for that dimension share the same status. In this entry, references to dimensions apply equally to dimension surrogates, except where noted. The LIMIT function does not change the status of a dimension or a valueset. The LIMIT function operates on the current status.

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 (includingr 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

LIMIT([INTEGER] {dimension|valueset} -

{TO|ADD|INSERT|KEEP|REMOVE|COMPLEMENT} -

[limit-clause] [IFNONE label])

where:

limit-clause is one of the following:

valuelist

concat-component [valuelist]

LEVELREL relation [valueset]

related-dimension [related-dimension-valuelist]

family-phrase

NOCONVERT {unrelated-dimension|valueset}]

POSLIST poslist-exp

Arguments

See the LIMIT command for a complete description of all arguments other than the INTEGER keyword.

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.

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

TEXT and NTEXT

When the dimension has the NTEXT data type and an argument that represents a dimension value has the TEXT data type, the LIMIT function 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.

Examples

Example 16-18 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 16-19 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 command, a STATUS month statement produces this output.

The current status of MONTH is:
MAR97 TO MAY97