| Oracle9i OLAP Developer's Guide to the OLAP DML Release 2 (9.2) Part Number A95298-01 |
|
Developing Programs, 7 of 12
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.
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:
.), or underscore (_).:).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:
GOTO commands in an IF command, you can often place your alternative sets of commands between DO and DOEND commands within the IF command itself.IF command to choose between two different programs, or use the SWITCH command to choose among many different programs.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 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 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.
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 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 as described below.
FOR loop is entered.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.
FOR loop is complete for the first district.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.
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.
OKNULLSTATUS = YES LIMIT month TO sales GT salesnum IF STATLEN(month) LT 1 THEN GOTO showerr
|
![]() Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|