Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


Within an OLAP DML program, the FOR command specifies one or more dimensions whose status controls the repetition of one or more statements. These statements, along with the FOR statement itself, are often called a FOR loop.


FOR dimension...




One or more dimensions whose current status controls the repetition of one or more statements. The statements are repeated for each combination of the values of the specified dimensions in the current status. When two or more dimensions are specified, the first one varies the slowest. You can specify a composite instead of a dimension.


The statement to be repeated. To repeat two or more statements, enclose them between DO and DOEND.


When you are repeating only one statement after FOR, you can omit DO and DOEND.

Usage Notes

FOR Dimension

A FOR statement loops over the values in status of the specified dimension. After the last dimension value, dimension status is restored to what it was before the loop, and execution of the program resumes with the next statement.

Status Inside a Loop

The TEMPSTAT command limits the dimension you are looping over inside a FOR loop or inside a loop that is automatically generated by a REPORT statement.

No Sorting

Because current status defines and controls a FOR loop, you cannot sort the FOR dimension within the loop.

Assignment Statements and Other Looping Statements

An OLAP DML assignment statement (SET), and some other OLAP DML statements automatically loop over dimension status and do so more efficiently than a FOR loop. Be careful not to cause extra looping by putting an assignment statement or one of these statements in a FOR loop.


You can use BREAK, CONTINUE, and GOTO statements to branch within, or out of, a FOR loop, thereby altering the sequence of statement execution.

Nested FOR Statements

FOR statements can be nested within a FOR loop to any depth when matching DO and DOEND statements are supplied where appropriate.


Example 9-136 Using FOR in a DO Loop to Repeat ROW Commands

In a report program, you want to show the unit sales of tents for each of three months. Use the following FOR statement with a DO/DOEND sequence to repeat ROW commands and BLANK commands for each value of the month dimension.

LIMIT product TO tents
LIMIT month TO 'Jan96' TO 'Mar96'
ROW district
ROW UNDER '-' VALONLY name.product
FOR month
      ROW INDENT 5 month WIDTH 6 UNITS

The program lines produce the following report.

3-Person Tents
     Jan96              307
     Feb96              209
     Mar96              277

Example 9-137 Using a FOR Statement for Looping Over Values

The FOR command executes the commands in the loop for each value in the current status of the dimension. You must limit the dimension to the desired values before executing a FOR statement. For example, you can produce a series of output lines that show the price for each product.

LIMIT product TO ALL
FOR product
SHOW JOINCHARS('Price for ' product ': $' price)

Each output line has the following format.

Price for TENTS: $165.50

When your data is multidimensional, you can specify multiple dimensions in a FOR statement to control the order of processing. For example, you can use the following statement to control the order in which dimension values of the units data are processed.

FOR month district product
   units = ...

When this assignment statement is executed, the month dimension varies the slowest, the district dimension varies the next slowest, and the product dimension varies the fastest. Thus, a loop is performed over all products for the first district before doing the next district, and over all districts for the first month before doing the next month.

Within the FOR loop, each specified dimension is temporarily limited to a single value while it executes the statements in the loop. You can therefore work with specific combinations of dimension values within the loop.

Example 9-138 Using DO/DOEND in a FOR Loop

When actual figures for unit sales are stored in a variable called units and projected figures for unit sales are stored in a variable called units.plan, then the code in your loop can compare these figures for the same combination of dimension values.

LIMIT product TO ALL
LIMIT district TO ALL
FOR district product
     IF (units.plan - units)/units.plan GT .1
       'Unit sales for ' product ' in ' -
       district ' are not within 10% of plan.')

These lines of code are processed in the following manner.

  1. The data is limited to a specific month.

  2. All the districts and products are placed in status, and the FOR loop is entered.

  3. In the FOR loop, the actual figure is tested against the planned figure. When the unit sales figure for Tents in Boston is more than 10 percent below the planned figure, then the following message is sent to the current outfile.

    Unit sales for TENTS in BOSTON are not within 10% of plan.
  4. After processing all the products, the FOR loop is complete for the first district.

  5. The loop is executed for the second district, and so on.

    Note that while the FOR loop executes, each dimension that is specified in a FOR statement is limited temporarily to a single value. When you specify district in the FOR loop, but not product, then all the values of product are in status while the FOR loop executes. The IF...THEN...ELSE command then tests data for only the first value of the product dimension.