Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements

Using OLAP DML Expressions as Single-Row Functions

SQL functions are typically single-row functions return a single result row for every row of a queried table or view. Oracle supports a number of predefined SQL single-row functions, for example COS, LOG, and ROUND which return numeric data, and UPPER and LOWER which return character data.

Within the context of a SELECT FROM OLAP_TABLE statement, there are four SQL functions that you can use to wrap OLAP DML functions in such a way that the OLAP DML function acts as a single-row function. There are four functions that the results of expressions of different OLAP DML data types: OLAP_EXPRESSION for Oracle OLAP numeric expressions, OLAP_EXPRESSION_BOOL for Oracle OLAP Boolean expressions, OLAP_EXPRESSION_DATE for Oracle OLAP datetime expressions, and OLAP_EXPRESSION_TEXT for Oracle OLAP text expressions. One of the arguments of each of these SQL functions is an OLAP DML function.

You can specify the OLAP_EXPRESSION function and its variants in the same way you specify other Oracle single-row functions, notably in the select list, WHERE, and ORDER BY clauses.

Modifying an Analytic Workspace From Within a SELECT FROM OLAP_TABLE Statement

There are several mechanisms for modifying an analytic workspace on the fly during the execution of OLAP_TABLE.

You can use the OLAP_CONDITION SQL function modifies an analytic workspace within the context of a SELECT FROM OLAP_TABLE statement. You can specify OLAP_CONDITION like other Oracle functions, typically in the WHERE clause. Using the OLAP_CONDITION, you can set an option, execute a LIMIT command, execute an OLAP model or forecast, or run a program. The changes made to the workspace can be transitory or they can persist in your session upon completion of the query.

In addition to OLAP_CONDITION, you can use syntax supported by the OLAP_TABLE function itself: The PREDMLCMD and POSTDMLCMD clauses in the limit map, as well as the olap_command parameter. OLAP_CONDITION has the advantage of portability, since it is not embedded within OLAP_TABLE, and versatility, since it can be applied at different entry points.

OLAP_TABLE saves the status of dimensions in the limit map before executing the LIMIT commands that generate the result set for the query. After the data is fetched, OLAP_TABLE restores the status of the dimensions. You can specify a PREDMLCMD clause in the limit map to cause an OLAP DML command to execute before the dimension status is saved. Modifications resulting from the PREDMLCMD clause remain in the workspace after execution of OLAP_TABLE, unless reversed with a POSTDMLCMD clause. For more information, see limit_map.

The olap_command parameter of OLAP_TABLE specifies an OLAP DML command that executes immediately before the result set is fetched. In some circumstances, the olap_command parameter may contain an OLAP DML FETCH command, which itself manages the fetch. Limits set by the olap_command parameter are only in effect during the execution of OLAP_TABLE. For more information, see olap_command.