Oracle9i OLAP Developer's Guide to the OLAP DMLRelease 2 (9.2)Part Number A95298-01

Selecting Data, 4 of 13

## Limiting Using a Boolean Expression

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:

```LIMIT dimension TO Boolean-expression

```

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:

• The Boolean expression must be dimensioned by the dimension whose status is being set.

• The data types of the expressions you are comparing in the Boolean expression must be similar.

For example, the following Boolean expression has similar data types on both sides of the Boolean operator `GT`.

```LIMIT market TO units.m GT 50000

```

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.

```LIMIT product TO TOTAL(sales product) GT 12000000
```

### How LIMIT Handles Boolean Multidimensional Expressions

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 `EVERY`, `ANY`, or `NONE` functions, which let you specify the dimensions of the result of the Boolean expression.

Suppose that `month`, `district`, and `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.

```REPORT sales GT 90000

```

As shown below, the report displays `YES` in both the `FOOTWEAR` and `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.

```LIMIT product TO sales GT 90000

```

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 `DISTRICT` dimension.

For `JAN95` and `BOSTON`, the Boolean expression evaluates to `TRUE` only for the `FOOTWEAR` product. Consequently, only `FOOTWEAR` is in status for the `product` dimension.

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
```

### Limiting to Values That Match an Expression

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.

#### Example 6-1 Limiting Using the ANY function

The `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, `CANOES` and `FOOTWEAR`):

• The first argument for the `ANY` function (that is, `sales GT 90000`) is the Boolean expression you want to evaluate.

• The second argument for the `ANY` function (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.

```LIMIT product TO ANY(sales GT 90000, product)

```

The `product` dimension has both `CANOES` and `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 `CANOES` and `FOOTWEAR` products.

```REPORT sales

The current status of PRODUCT is:
CANOES, FOOTWEAR
DISTRICT: BOSTON
-------------SALES--------------
-------------MONTH--------------
PRODUCT          JAN95      FEB95      MAR95
-------------- ---------- ---------- ----------
CANOES          66,013.92  76,083.84  91,748.16
FOOTWEAR        91,406.82  86,827.32 100,199.46
```