Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Designing Programs, 8 of 13
Ordinarily, the lines of a program are executed sequentially -- in linear fashion. However, a well-designed program controls the flow of execution by using OLAP DML commands that redirect the path of execution when appropriate.
You can use the following control structures to modify the sequence of command execution.
For more information on an individual command, see the entry for the command in OLAP DML Reference.
When creating a label, follow these guidelines:
.
), or an 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:
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.
Unit sales for TENTS in BOSTON are not within 10% of plan.
Note: While the FOR loop executes, each dimension that is specified in a FOR command is limited temporarily to a single value. If you specified DISTRICT in the FOR loop, but not PRODUCT, then all the values of PRODUCT would be in status while the FOR loop executed. The IF command would then test data for only the first value of the PRODUCT dimension.
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 © 1996-2001, Oracle Corporation. All Rights Reserved. |
|