Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

FOR

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 statement itself, are often called a FOR loop. You can use the FOR command only within programs.

Syntax

FOR dimension...

     statement

Arguments

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.

statement

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

DO
       statement1
          ...
       statementN
     DOEND

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

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 aREPORT 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.

Branching

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, as long as matching DO and DOEND statements are supplied where appropriate.

Examples

Example 13-24 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 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
BLANK
FOR month
    DO
      ROW INDENT 5 month WIDTH 6 UNITS
      BLANK
    DOEND

The program lines produce the following report.

BOSTON
3-Person Tents
--------------
 
     Jan96              307
     Feb96              209
     Mar96              277

Example 13-25 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 month TO FIRST 1
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 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 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 month TO FIRST 1
LIMIT product TO ALL
LIMIT district TO ALL
FOR district product
   DO
     IF (units.plan - units)/units.plan GT .1
     THEN SHOW JOINCHARS(-
       'Unit sales for ' product ' in ' -
       district ' are not within 10% of plan.')
   DOEND

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 statement then tests data for only the first value of the product dimension.