Previous
Previous
 
Next
Next


DEFINE PROGRAM

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.


See also:

"Creating OLAP DML Programs"

Syntax

DEFINE name PROGRAM [datatype|dimension] [AW workspace] [SESSION]

Parameters

name

The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.

PROGRAM

The object type when you are defining a program.

datatype

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.

dimension

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.

AW workspace

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.

SESSION

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.