You can use OLAP DML commands within your SELECT FROM OLAP_TABLE statements as described in:
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
ROUND which return numeric data, 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,
ORDER BY clauses.
There are several mechanisms for modifying an analytic workspace on the fly during the execution of
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
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.