Skip Headers

Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
 Home Book List Contents Index Master Index Feedback

Selecting Data, 3 of 13

Limiting to a Simple List of Values

A common way of selecting data is to limit a dimension to a value or list of values. When limiting dimension values, you can substitute a dimension surrogate for its dimension. The simplified syntax for using the `LIMIT` command in this way is shown below.

```LIMIT dimension TO values

```

The `values` argument can consist of any combination of:

• 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-separated integers.
• Ranges of integer values that represent the logical positions of dimension values, expressed as `value1` `TO` `value2`.
• Valuesets.

Suppose that you want a report of footwear sales in Boston for January through March 1995. The following commands limit the appropriate dimensions and request the report.

```LIMIT month TO 'JAN95' 'FEB95' 'MAR95'
LIMIT product TO 'FOOTWEAR'
LIMIT district TO 'BOSTON'
REPORT sales

```

The report output looks like this.

```DISTRICT: BOSTON
-------------SALES--------------
-------------MONTH--------------
PRODUCT          JAN95      FEB95      MAR95
-------------- ---------- ---------- ----------
FOOTWEAR        91,406.82  86,827.32 100,199.46

```

As an example of limiting dimension values using a dimension substitute, suppose you have a `NUMBER` dimension named `storeid` that has store identification numbers as values. The values of `storeid` are 10, 20, 30, 100, 110, 120, and 200. You have an `INTEGER` dimension surrogate for `storeid`, named `storenum`, that has an integer value for each position of the values of `storeid`. The values of `storenum` are the integers 1 through 7. You can limit the current status list of both `storeid` and `storenum` to the same set of values with any of the following commands.

```LIMIT storeid TO 10, 100
LIMIT storenum TO 1, 4
LIMIT storenum TO storeid 10, 100
LIMIT storenum TO storenum 1, 4
```

Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
 Home Book List Contents Index Master Index Feedback