Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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.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.
OLAP_EXPRESSION( r2c IN RAW(32), numeric_expression IN VARCHAR2) RETURN NUMBER;
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.
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