| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
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.
See Also:
"Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements" and "Adding Calculated Columns to the Relational View".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
The name of a column specified by a ROW2CELL clause in the limit map. See "ROW2CELL Clause" of OLAP_TABLE
An OLAP DML expression that returns a numeric result.
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 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 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
