|Oracle® OLAP DML Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
Within an OLAP DML program, 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.
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.
DO statement1 ... statementN DOEND
When you are repeating only one statement after FOR, you can omit DO and DOEND.
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.
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.
Because current status defines and controls a FOR loop, you cannot sort the FOR dimension within the loop.
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.
FOR statements can be nested within a FOR loop to any depth, as long as matching DO and DOEND statements are supplied where appropriate.
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
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
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.
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.
The data is limited to a specific month.
All the districts and products are placed in status, and the FOR loop is entered.
In the FOR loop, the actual figure is tested against the planned figure. When the unit sales figure for
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.
After processing all the products, the FOR loop is complete for the first district.
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