Within an OLAP DML program, the RETURN command terminates execution of a program before its last line. You can optionally specify a value that the program returns when the program is called as a function. 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.
The value returned by a program is a single value, without any dimensions. However, within the context of the statement that calls a user-defined function, the function expression has the dimensions of its arguments. In this case, the program is called once for every combination of the dimension values of the function expression.
When you specify a data type when you define a program, the return value has that data type. When you specify a dimension when you define a program, the return value is a single value in that dimension. When the expression in a RETURN statement does not match the declared data type or dimension, Oracle OLAP converts 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 and Oracle OLAP converts any return value to the data type that is required by the calling context which 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 is in the output of the LISTBY program, and OBJ(ISBY) is 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 NA.
Example 10-106 Terminating a Program Early
In this example, suppose you want a report program that produces a report only when a variable called newfigures is present in the current analytic workspace. In your program, you can use an IF statement 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 statement terminates execution of the program at that point.
Example 10-107 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 statement, 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