|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Selecting Data, 4 of 13
You can use the
LIMIT command to limit a dimension according to the result of a Boolean expression. The simplified syntax for using the
LIMIT command in this way is shown below:
When you use this form of the
LIMIT command, the values that are currently in status are replaced with those dimension values for which the Boolean expression is true.
When you are constructing a Boolean expression, keep the following points in mind:
For example, the following Boolean expression has similar data types on both sides of the Boolean operator
In the following example, the values of the
TOTAL function are broken out by
product and compared to a literal (that is, the number 12000000). The
LIMIT command replaces the values that are currently in status for the
product dimension with the values of the
product dimension whose sales, totaled for all months and districts, are greater than 12 million.
An understanding of how the
LIMIT command handles Boolean expressions with more than one dimension is important to the successful use of the command.
The result of a simple Boolean expression is a single value. When you use the
LIMIT command with a Boolean expression, no looping is performed through the dimensions to create and return an array of values for the expression. Instead, the first value in the dimension status list is identified for each dimension in the expression, the expression using those values is evaluated, and a single value is returned.
If you want the result of the Boolean expression to have dimensionality, then use the
NONE functions, which let you specify the dimensions of the result of the Boolean expression.
product have the dimension status shown below.
The current status of MONTH is: JAN95 TO MAR95 The current status of DISTRICT is: BOSTON The current status of PRODUCT is: ALL
Now you want products that have more than $90,000 worth of sales in at least one of the months to be in status for the
product dimension. By issuing the following command, you can see which values in the current dimension status meet this condition.
As shown below, the report displays
YES in both the
CANOES rows. Both of these products have sold more than $90,000 on at least one occasion during January through March 1995.
DISTRICT: BOSTON ---------SALES GT 90000--------- -------------MONTH-------------- PRODUCT JAN95 FEB95 MAR95 -------------- ---------- ---------- ---------- TENTS NO NO NO CANOES NO NO YES RACQUETS NO NO NO SPORTSWEAR NO NO NO FOOTWEAR YES NO YES
You might think that limiting the
product dimension using only the simple Boolean expression shown below would give you your desired result.
However, when the Boolean expression is evaluated, no looping is performed through the
sales variable to create and return an array of values for the
product dimension. Instead, only the first value in the dimension status list is used for each dimension in
sales other than the
product dimension. In this case,
JAN95 is used for the value of the
month dimension of the
sales variable and
BOSTON is used for the value of the
BOSTON, the Boolean expression evaluates to
TRUE only for the
FOOTWEAR product. Consequently, only
FOOTWEAR is in status for the
As shown below, a report of sales in Boston only displays values for the
FOOTWEAR product that have sold more than $90,000 on at least one occasion during January through March 1995.
REPORT sales The current status of PRODUCT is: FOOTWEAR DISTRICT: BOSTON -------------SALES-------------- -------------MONTH-------------- PRODUCT JAN95 FEB95 MAR95 -------------- ---------- ---------- ---------- FOOTWEAR 91,406.82 86,827.32 100,199.46
The way to limit a dimension to all dimension values that match a Boolean expression is to use the
ANY function with the Boolean expression.
LIMIT command (shown below) illustrates how to use the
ANY function to limit the
product dimension to all dimension values that have a value of more than $90,000 in the
sales variable (that is,
ANYfunction (that is,
sales GT 90000) is the Boolean expression you want to evaluate.
ANYfunction (that is,
product) indicates the dimensionality of the result of the Boolean expression.
In this example, when the Boolean function is evaluated, a test is performed for
TRUE values along the
product dimension, and returns an array of values.
product dimension has both
FOOTWEAR in status. Both of these products sold more than $90,000 on at least one occasion during January through March 1995.
As shown below, a report for sales in Boston displays both the