OLAP_EXPRESSION Examples

The following script was used to create the view unit_cost_price_view, which is used in Example 7-1 and Example 7-2 to illustrate the use of OLAP_EXPRESSION. For information about creating views of analytic workspaces, see "OLAP_TABLE Overview".

Sample View: GLOBAL.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(
         'global 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 of the 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 7-1 OLAP_EXPRESSION: Time Series Function in a WHERE Clause

This example uses the view described in "Sample View: GLOBAL.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 7-2 OLAP_EXPRESSION: Numeric Calculation in an ORDER BY CLause

This example uses the view described in "Sample View: GLOBAL.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

OLAP_EXPRESSION Syntax

The OLAP_EXPRESSION function dynamically executes an OLAP DML numeric expression within the context of an OLAP_TABLE function. In addition to returning a custom measure, OLAP_EXPRESSION can be used in the WHERE and ORDER BY clauses to modify the result set of the query of the analytic workspace.

Syntax

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

Parameters

Table 7-1 OLAP_EXPRESSION Function Parameters

Parameter	Description
`r2c`	The name of a column specified by a `ROW2CELL` clause in the limit map. `OLAP_TABLE` populates this column with information used by the OLAP single-row functions, including `OLAP_EXPRESSION`. See "Limit Map Parameter".
`numeric_expression`	An OLAP DML expression that returns a numeric result. Search for "expressions" in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".

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.

Example

See "OLAP_EXPRESSION Examples".