|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
Within an OLAP DML program, the RETURN command terminates execution of a program prior to its last line. You can optionally specify a value that the program will return. The value should have the same data type or dimension that you specified when you defined the program.
The expression to be returned to the calling program when the called program terminates.
To return a value, a program must be called as a function. That is, it must be used as an expression in a statement. The following is an example of a user-defined function being used as an argument to the REPORT command.
When a program is called with a CALL command or by using the standalone command format, its return value is discarded.
The value returned by a program is a single value, without any dimensions. However, within the context of the command that calls a user-defined function, the function expression has the dimensions of its arguments. For instance, in "User-Defined Function", when
actual is dimensioned by
month, then the expression
ISRECENT(actual) is also dimensioned by
month. Therefore, Oracle OLAP will call the ISRECENT program once for every combination of
month in the current status.
When you specify a data type when you define a program, the return value will have that data type. When you specify a dimension when you define a program, the return value will be a single value in that dimension. When the expression in the RETURN command does not match the declared data type or dimension, Oracle OLAP will convert it to the declared data type.
When you do not specify a data type or dimension in the definition of a program, its return value is treated as worksheet data. This means Oracle OLAP will convert any return value to the data type that is required by the calling context. This may lead to unexpected results.
When the program returns values of a dimension, the dimension must be declared in the same analytic workspace as the program. The program will be in the output of the LISTBY program, and OBJ(ISBY) will be
TRUE for the dimension.
When a program has been invoked as a function, but it does not provide a return value, the value that is returned to the calling program is
Example 21-3 Terminating a Program Early
In this example, suppose you want a report program that will produce a report only when a variable called
newfigures is present in the current analytic workspace. In your program, you can use an IF command to check whether
newfigures exists and a RETURN to stop execution when it does not.
DEFINE sales.report PROGRAM PROGRAM IF NOT EXISTS('newfigures') THEN DO SHOW 'The new data is not yet available.' RETURN DOEND PUSH month TRAP ON cleanup LIMIT month TO LAST 3 REPORT ACROSS month: newfigures cleanup: POP month END
Now when you run the program without
newfigures in the analytic workspace, the program produces a message and the RETURN command terminates execution of the program at that point.
Example 21-4 Returning a Value
The following program derives next year's budget figures from the
actual variable. It is a temporary calculation. You could call this program in a REPORT command, thus calculating and reporting the budget figures without storing them in an analytic workspace.
DEFINE budget.growth PROGRAM DECIMAL PROGRAM VARIABLE growth DECIMAL VARIABLE factor DECIMAL growth = TOTAL(actual(year 'Yr97') year) - TOTAL(actual(year - 'Yr96') year) factor = ( 1 + growth ) / TOTAL(actual(year 'Yr96') year) RETURN TOTAL(actual(year 'Yr97') year) * (factor * factor/2) END