Skip Headers

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

Part Number A95298-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index



Developing Programs, 7 of 12

Controlling the Flow of Execution

Ordinarily, the lines of a program are executed sequentially, that is, in linear fashion. However, a well-designed program controls the flow of execution by using commands that redirect the path of execution when appropriate.

You can use the following control structures to modify the sequence of command execution.

Command or Keyword


Event that Triggers Action

IF command

Executes alternative commands or groups of commands.

A specified Boolean condition is or is not met.

WHILE command

Executes a group of commands repeatedly.

As long as a specified Boolean condition is met.

FOR command

Executes a command or a group of commands.

Once for each value of a dimension.

GOTO command

Branches to a specific labeled location.

Issuing the command.

SWITCH command

Branches to particular branch in a multipath branch.

A specific criterion is met.

TRAP command

Branches to a specific labeled location.

An error occurs during program execution.

IFNONE keyword in a LIMIT, REPORT, ROW, or HEADING command

Branches to a specific labeled location.

An attempt to set status would result in no values or null status.

RETURN command

Branches out of a program or returns to a calling program before the final command in the program.

Issuing the command.

Guidelines for Constructing a Label

When you use control structures to branch to a particular location, you must provide a label for the location in order to identify it clearly. When creating a label, follow these guidelines:

Alternatives to the GOTO Command

While GOTO makes it easy to branch within a program, frequent use of it can obscure the logic of your program, making it difficult to follow its flow. This is particularly true when you have a complex program with several labels and GOTO commands that skip over large portions of code.

To keep the logic of your programs clear, minimize your use of GOTO.

Sometimes a GOTO command is the best programming technique, but often there are better alternatives. For example:

Example 7-7 Using the FOR Command for Looping Over Dimension 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 7-8 Using DO/DOEND in a FOR Loop

If 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 as described below.

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


    While the FOR loop executes, each dimension that is specified in a FOR command is limited temporarily to a single value. If 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 command then tests data for only the first value of the product dimension.

Example 7-9 Branching to Avoid Setting Null Status

Your program might try to set or refine the status of the product dimension to include only the products for which unit sales are greater than 500. If no products have unit sales of more than 500, then you can use the IFNONE keyword to specify that execution branch to the novals label.

LIMIT product KEEP units GT 500 IFNONE novals

In the commands following the novals label, you can handle the special situation in which no products have units sales greater than 500.

Example 7-10 Branching After Setting Null Status

As an alternative to branching to an IFNONE label, you can also handle null status for a dimension with the OKNULLSTATUS option. If you set OKNULLSTATUS to YES, then you will be allowed to set the status of a dimension to null. You can then check for null status and execute appropriate commands with an IF command, or you can handle null status as one of the cases in a SWITCH command.

LIMIT month TO sales GT salesnum
IF STATLEN(month) LT 1
   THEN GOTO showerr

Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index