OLAP_EXPRESSION

OLAP_EXPRESSION is a SQL function that dynamically executes an OLAP DML boolean expression within the context of an OLAP_TABLE function. In addition to returning a custom measure, you can use this function in the WHERE and ORDER BY clauses to modify the result set of the query of the analytic workspace.

OLAP_EXPRESSION_TEXT returns character data. To return text, boolean, or date data, use OLAP_EXPRESSION_TEXT, OLAP_EXPRESSION_BOOL, or OLAP_EXPRESSION_DATE SQL functions.

Before you use this function, 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.

Note:

You cannot execute this function from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL Worksheet.

Returns

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

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

Syntax

OLAP_EXPRESSION(
          r2c                  IN   RAW(32),
          numeric_expression   IN   VARCHAR2)
      RETURN NUMBER;

Parameters

r2c: The name of a column specified by a ROW2CELL clause in the limit map. See "ROW2CELL Clause" of OLAP_TABLE
numeric_experssion: An OLAP DML expression that returns a numeric result.

See:
"Numeric Expressions", Chapter 7, "OLAP DML Functions: A - K", Chapter 8, "OLAP DML Functions: L - Z", and "Guidelines for Using Quotation Marks in OLAP DML Commands".

Examples

The following script was used to create the view unit_cost_price_view, which is used in Example A-12, "OLAP_EXPRESSION: Time Series Function in a WHERE Clause" and Example A-13, "OLAP_EXPRESSION: Numeric Calculation in an ORDER BY CLause" to illustrate the use of OLAP_EXPRESSION. For information about creating views of analytic workspaces, see "Creating Relational Views Using OLAP_TABLE".

Sample View: MYAW.UNIT_COST_PRICE_VIEW

-- Create the logical row
CREATE TYPE unit_cost_price_row AS OBJECT (
            aw_unit_cost          NUMBER,
            aw_unit_price         NUMBER,
            aw_product            VARCHAR2(50),
            aw_time               VARCHAR2(20),
            r2c                   RAW(32));
/
-- Create the logical table
CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row;
/
-- Create the view
CREATE OR REPLACE VIEW unit_cost_price_view AS
    SELECT aw_unit_cost, aw_unit_price, aw_product, aw_time, r2c
      FROM TABLE(OLAP_TABLE(
         'myaw DURATION SESSION',
         'unit_cost_price_table', 
         '',
         'MEASURE aw_unit_cost  FROM price_cube_unit_cost
          MEASURE aw_unit_price FROM price_cube_unit_price
          DIMENSION product WITH 
             HIERARCHY product_parentrel
                INHIERARCHY product_inhier
             ATTRIBUTE aw_product FROM product_short_description
          DIMENSION time WITH 
             HIERARCHY time_parentrel
                INHIERARCHY time_inhier
             ATTRIBUTE aw_time FROM time_short_description
          ROW2CELL r2c'));
/

The following query shows some aggregate data in the view.

SQL>SELECT * FROM unit_cost_price_view 
             WHERE aw_product = 'Hardware'
             AND aw_time in ('2000', '2001', '2002', '2003')
             ORDER BY aw_time;

AW_UNIT_COST    AW_UNIT_PRICE    AW_PRODUCT    AW_TIME    R2C
------------    -------------    ----------    -------    -----
   211680.12        224713.71      Hardware       2000    00...
   195591.60        207513.16      Hardware       2001    00...
   184413.05        194773.78      Hardware       2002    00...
    73457.31         77275.06      Hardware       2003    00...

Example A-12 OLAP_EXPRESSION: Time Series Function in a WHERE Clause

This example uses the view described in "Sample View: MYAW.UNIT_COST_PRICE_VIEW".

The following SELECT statement calculates an expression with an alias of PERIODAGO, and limits the result set to calculated values greater than 50,000. The calculation uses the LAG function to return the value of the previous time period.

SQL>SELECT aw_time time, aw_unit_cost unit_cost, 
            OLAP_EXPRESSION(r2c, 
                           'LAG(price_cube_unit_cost, 1, time, 
                                LEVELREL time_levelrel)') periodago
        FROM unit_cost_price_view
        WHERE aw_product = 'Hardware'
        AND OLAP_EXPRESSION(r2c,
                           'LAG(price_cube_unit_cost, 1, time, 
                                LEVELREL time_levelrel)') > 50000;

This SELECT statement produces these results.

TIME                  UNIT_COST  PERIODAGO
-------------------- ---------- ----------
2003                   73457.31  184413.05
2004                              73457.31
1999                   231095.4  162526.92
2000                  211680.12   231095.4
2001                   195591.6  211680.12
2002                  184413.05   195591.6
Q2-99                  57587.34   57856.76
Q3-99                  59464.25   57587.34
Q4-99                  56187.05   59464.25
Q1-00                  53982.32   56187.05
Q2-00                  53629.74   53982.32
Q3-00                  53010.65   53629.74
Q4-00                  51057.41   53010.65
Q1-01                  49691.22   51057.41

Example A-13 OLAP_EXPRESSION: Numeric Calculation in an ORDER BY CLause

This example uses the view described in "Sample View: MYAW.UNIT_COST_PRICE_VIEW".

This example subtracts costs from price, and gives this expression an alias of MARKUP. The rows are ordered by markup from highest to lowest.

SQL>SELECT aw_time time, aw_unit_cost unit_cost, aw_unit_price unit_price, 
            OLAP_EXPRESSION(r2c, 
                      'PRICE_CUBE_UNIT_PRICE - PRICE_CUBE_UNIT_COST') markup
     FROM unit_cost_price_view 
     WHERE aw_product = 'Hardware' 
     AND aw_time in ('1998', '1999', '2000', '2001')
     ORDER BY OLAP_EXPRESSION(r2c, 
                      'PRICE_CUBE_UNIT_PRICE - PRICE_CUBE_UNIT_COST') DESC;

This SELECT statement produces these results.

TIME                  UNIT_COST   UNIT_PRICE  MARKUP
-------------------- ----------  ----------  ---------
1999                  231095.40   245412.91   14317.51
2000                  211680.12   224713.71   13033.59
2001                  195591.60   207513.16   11921.56
1998                  162526.92   173094.41   10567.49