![]() Previous |
![]() Next |
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:
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.
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.
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.
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.
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.
Inserts the new values before the first value in status.
Inserts new values after the last value in status.
Specifies whether new values Oracle OLAP inserts new values before or after position in the current status.
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.
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.
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.
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.
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)
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.
Specifies that NA
values are placed first in the sort list rather than last.
Specifies the values to use for the limit. The syntax is quite complex and, consequently, has been divided into the following topics:
(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.
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: 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: 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
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
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
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>>
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
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.
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.