9 OLAP_EXPRESSION_DATE

OLAP_EXPRESSION_DATE is a SQL function that dynamically executes a single-row date function in an analytic workspace and returns the results.

OLAP_EXPRESSION_DATE Overview

OLAP_EXPRESSION_DATE acts as a single-row function within the context of a SELECT FROM OLAP_TABLE statement. You can specify OLAP_EXPRESSION_DATE in the same way you specify other Oracle single-row functions, notably in the select list and WHERE and ORDER BY clauses.

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. Oracle supports a number of predefined single-row functions, for example COS, LOG, and ROUND which return numeric data, and UPPER and LOWER which return character data. For more information on single-row functions, refer to the Oracle Database SQL Reference.

The OLAP single-row functions, OLAP_EXPRESSION and its variants for text, date, and boolean data, return the result of an OLAP DML expression that you specify. The OLAP DML supports a rich syntax for specifying computations ranging from simple arithmetic expressions to statistical, financial, and time-series operations.

You can use OLAP_EXPRESSION_DATE to dynamically calculate any valid date expression within an analytic workspace and retrieve its results. For more information on date expressions in the OLAP DML, search for "working with dates in text expressions" and DATEFORMAT in the Oracle OLAP DML Reference.

OLAP_EXPRESSION_DATE and OLAP_TABLE

OLAP_TABLE uses a limit map to present the multidimensional data from an analytic workspace in tabular form. The limit map specifies the columns of the logical table. When an OLAP_EXPRESSION_DATE function is specified in the select list of the query, OLAP_TABLE generates an additional column for the results of the function.

To use OLAP_EXPRESSION_DATE, you must specify a ROW2CELL clause in the limit map used by OLAP_TABLE. ROW2CELL identifies a RAW column that OLAP_TABLE populates with information used by the OLAP single-row functions.

OLAP_EXPRESSION_DATE Syntax

The OLAP_EXPRESSION_DATE function dynamically executes an OLAP DML date expression within the context of an OLAP_TABLE function.

Syntax

OLAP_EXPRESSION_DATE(
     r2c                 IN      RAW(32),
     date_expression     IN      VARCHAR2)
RETURN NUMBER;

Parameters

Table 9-1 OLAP_EXPRESSION_DATE Function Parameters

Parameter Description

Parameter	Description
`r2c`	The name of a column populated by a `ROW2CELL` clause in a call to `OLAP_TABLE`. `ROW2CELL` is a component of a limit map parameter of the `OLAP_TABLE` function. See "Limit Map Parameter".
`date_expression`	A date expression in the analytic workspace. Search for "working with dates in text expressions" and `DATEFORMAT` in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".

r2c

The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE.

ROW2CELL is a component of a limit map parameter of the OLAP_TABLE function. See "Limit Map Parameter".

date_expression

A date expression in the analytic workspace. Search for "working with dates in text expressions" and DATEFORMAT in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".

Returns

An evaluation of date_expression for each row of the table object returned by the OLAP_TABLE function.

OLAP_EXPRESSION_DATE returns date data. To return numeric, boolean, or text data, use the OLAP_EXPRESSION, OLAP_EXPRESSION_BOOL, or OLAP_EXPRESSION_TEXT functions.

Example

Refer to "OLAP_EXPRESSION Examples" and "OLAP_EXPRESSION_BOOL Example" for examples of OLAP single-row functions.