Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
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

Go to previous page
Go to next page
View PDF


The FOR command specifies one or more dimensions whose status will control the repetition of one or more statements. These statements, along with the FOR command itself, are often called a FOR loop. You can use the FOR command only within programs.


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.


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 the REPORT command.

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 the BREAK, CONTINUE, and GOTO commands to branch within, or out of, a FOR loop, thereby altering the sequence of statement execution.

Nested FOR Commands

FOR commands can be nested within a FOR loop to any depth, as long as matching DO and DOEND commands are supplied where appropriate.

Related Statements

See also DO ... DOEND, IF...THEN...ELSE, WHILE, and RETURN.


Example 13-24 Repeating ROW Commands

In a report program, you want to show the unit sales of tents for each of three months. Use the following FOR command with a DO/DOEND sequence to repeat ROW commands and BITAND 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 13-25 Using the FOR Command 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 the FOR command. 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 more than one dimension in a FOR command to control the order of processing. For example, you can use the following command 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 commands in the loop. You can therefore work with specific combinations of dimension values within the loop.

Example 13-26 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 command 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.