![]() Previous |
![]() Next |
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:
exclusive-val-args is one of the following constructs:
Parameters
The name of the dimension or dimension surrogate for which you are setting status.
The name of the valueset for which you are assigning values.
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.)
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.)
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.
A multiline text expression, each line of which is a value of dimension.
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 aNUMBER dimension using INTEGER positions. Instead, define an INTEGER dimension surrogate for the NUMBER dimension and limit the dimension by the positions of the surrogate. |
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.
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.
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.
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.
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.
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. |
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. |
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.
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.
Specifies that Oracle OLAP use only those dimension members that were created using a MAINTAIN ADD SESSION statement when performing the limit.
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.
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.
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
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
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
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
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
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
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>