OLAP_EXPRESSION Function

The OLAP_EXPRESSION function allows you to execute single-row numeric functions in the analytic workspace and thus generate custom measures in SELECT statements. In addition to calculating an expression, OLAP_EXPRESSION can be used in the WHERE and ORDER BY clauses to modify the result set of a SELECT.

Syntax

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

Parameters

Table 11-6  OLAP_EXPRESSION Function Parameters

Parameter	Description
r2c	The name of a column populated by the ROW2CELL clause of the limit map in a call to the OLAP_TABLE function.
expression	A calculation that will be performed in the analytic workspace.

Returns

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

Usage Notes

You can use OLAP_EXPRESSION only with a table object returned by the OLAP_TABLE function. The returned table object must have a column populated by a ROW2CELL clause in the limit map used in the call to OLAP_TABLE. Refer to Chapter 12, "OLAP_TABLE" for more information about using this function.

Examples

View Used in These Examples

The following script was used to create a view named MEASURE_VIEW, which is used in the examples of OLAP_EXPRESSION that follow.

CREATE TYPE measure_row AS OBJECT (
   time                       VARCHAR2(12),
   geography                  VARCHAR2(30),
   product                    VARCHAR2(30),
   channel                    VARCHAR2(30),
   sales                      NUMBER(16),
   cost                       NUMBER(16),
   promotions                 NUMBER(16),
   quota                      NUMBER(16),
   units                      NUMBER(16),
   r2c                        RAW(32));
/   
   
CREATE TYPE measure_table AS TABLE OF measure_row;
/

CREATE OR REPLACE VIEW measure_view AS
SELECT sales, cost, promotions, quota, units,
      time, geography, product, channel, r2c 
   FROM TABLE(CAST(OLAP_TABLE(
     'xademo DURATION SESSION', 
     'measure_table', 
     '',
     'MEASURE sales FROM xademo_analytic_cube_f.sales
      MEASURE cost FROM xademo_analytic_cube_f.costs
      MEASURE promotions FROM xademo_analytic_cube_f.promo
      MEASURE quota FROM xademo_analytic_cube_f.quota
      MEASURE units FROM xademo_analytic_cube_f.units   
      DIMENSION time FROM xademo_time WITH
        HIERARCHY xademo_time_member_parentrel
           INHIERARCHY xademo_time_member_inhier
      DIMENSION geography FROM xademo_geography WITH 
         HIERARCHY xademo_geography_member_parentrel
            INHIERARCHY xademo_geography_member_inhier
      DIMENSION product FROM xademo_product WITH
         HIERARCHY xademo_product_member_parentrel
            INHIERARCHY xademo_product_member_inhier
      DIMENSION channel FROM xademo_channel WITH
         HIERARCHY xademo_channel_member_parentrel
            INHIERARCHY xademo_channel_member_inhier
   ROW2CELL r2c') 
      AS measure_table))
   WHERE sales IS NOT NULL;
/
COMMIT
/      
GRANT SELECT ON measure_view TO PUBLIC;   

Time Series Function With a WHERE Clause

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

SELECT time, cost, OLAP_EXPRESSION(r2c, 
   'LAG(xademo_analytic_cube_f.costs, 1, xademo_time, 
      LEVELREL xademo_time_member_levelrel)') periodago
FROM measure_view
WHERE geography = 'L1.WORLD' AND
CHANNEL = 'STANDARD_2.TOTALCHANNEL' AND
PRODUCT = 'L1.TOTALPROD' and
OLAP_EXPRESSION(r2c, 'LAG(xademo_analytic_cube_f.costs, 1, xademo_time, 
   LEVELREL xademo_time_member_levelrel)') > 200000;

This SELECT statement produces these results.

TIME               COST  PERIODAGO
------------ ---------- ----------
L1.1997         1078031 2490243.07
L2.Q1.97         615399 560379.445
L2.Q2.96         649004 615398.858
L2.Q2.97         462632 649004.473
L2.Q3.96         582693 462632.064
L2.Q4.96         698166 582693.091
L3.AUG96         194498 209476.344
L3.FEB96         186762 252738.981
L3.JAN96         185755 205214.946
               .
               .
               .

Numeric Calculation With an ORDER BY Clause

This example subtracts costs from sales to calculate profit, and gives this expression an alias of PROFIT. The rows are ordered by geographic areas from most to least profitable.

SELECT geography, sales, cost, OLAP_EXPRESSION(r2c,
   'xademo_analytic_cube_f.sales - xademo_analytic_cube_f.costs') profit
FROM measure_view
WHERE 
channel = 'STANDARD_2.TOTALCHANNEL' AND
product = 'L1.TOTALPROD' AND
time = 'L3.APR97'
ORDER BY OLAP_EXPRESSION(r2c, 
   'xademo_analytic_cube_f.sales - xademo_analytic_cube_f.costs') DESC;

This SELECT statement produces these results.

GEOGRAPHY                           SALES       COST     PROFIT
------------------------------ ---------- ---------- ----------
L1.WORLD                          9010260     209476 8800783.17
L2.EUROPE                         3884776      95204 3789571.85
L2.AMERICAS                       2734436      55322 2679114.66
L2.ASIA                           1625379      37259 1588120.61
L3.USA                            1603043      27547 1575496.86
L2.AUSTRALIA                       765668      21692 743976.058
L3.UK                              733090      19144 713945.952
L3.CANADA                          731734      19666 712067.455
L4.NEWYORK                         684008       8020 675987.377
L3.GERMANY                         659428      12440 646988.197
L3.FRANCE                          596767      19307 577460.113
               .
               .
               .