![]() Previous |
![]() Next |
The DEFINE command with the PROGRAM keyword adds a new OLAP DML program object to an analytic workspace. An OLAP DML program is a collection of OLAP DML statements that helps you accomplish some workspace management or analysis task. Defining a program merely creates a program object in the analytic workspace. You must also code the actual lines of the program.
Syntax
DEFINE name PROGRAM [datatype|dimension] [AW workspace] [SESSION]
Parameters
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a program.
The data type of the value to be returned by the program when it is called as a function. You can use any of the data types that apply to variables.
The name of a dimension, whose value the program returns when it is called as a function. The return value is a single value of the dimension, not a position (INTEGER
). The dimension must be defined in the same workspace as the program.
The name of an attached workspace in which you want to define the program. When the program returns a dimension, the program must be defined in the same workspace as the dimension. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Usage Notes
Returning Values
Use a RETURN statement in a program when you want it to return a value. The argument to the RETURN statement is an expression that specifies the value to return. When the expression does not match the declared data type or dimension, the value is converted (if possible) to the declared data type or dimension value.
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 required by the calling context which may lead to unexpected results.
For a program to return a value, you must call the program as a function. That is, you must use it as an expression in a statement. In the following example, the program isrecent
is being treated as a function. It is an argument to the REPORT command.
REPORT isrecent(actual)
When the program returns values of a dimension, the program is in the output of the LISTBY function, and OBJ(ISBY) is TRUE
for the dimension.
See the entries for the ARGUMENT, CALL, and RETURN commands for more information about programs as user-defined functions.
Examples
Basing Program Flow on Test Results
The saleseval
program tests whether total sales for a month exceeds total planned sales for the month. The program executes different statements based on the results of the test.
DEFINE SALESEVAL PROGRAM PROGRAM ARGUMENT onemonth MONTH VARIABLE excess DECIMAL ALLSTAT LIMIT month TO onemonth IF TOTAL(sales, month) GT TOTAL(sales.plan, month) THEN DO excess = (TOTAL(sales, month) - - TOTAL(sales.plan, month)) - / TOTAL(sales.plan, month) * 100 SHOW JOINCHARS('Sales exceeded plan by ' excess '%.') DOEND ELSE SHOW JOINCHARS('We\'re not meeting plan. ' - 'Let\'s get working!') REPORT DOWN product W 10 ACROSS district: sales - sales.plan END
When total sales for the month exceeds total planned sales for the month, the THEN statement lines are executed. The program calculates the percentage by which actual sales exceeds planned sales and places the result in a numeric variable called excess
. The program then sends the results to the current outfile. The JOINCHARS function is used to combine the calculated expression excess
with the text expression "Sales exceeded plan by" in the output.
When total sales does not exceed planned sales, the ELSE statement line is executed and a different message is produced.
After the THEN or ELSE statement lines are executed, control flows to the next line in the program, and a report of sales in excess of plan is produced.