Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
OLAP_CONDITION
is a SQL function that dynamically executes an OLAP DML command during a query of an analytic workspace.
See Also:
The OLAP_CONDITION
function executes an OLAP DML command at one of three entry points in the limit map used in a call to OLAP_TABLE
as described in "Entry Points for OLAP_CONDITION in the OLAP_TABLE Limit Map".
Note:
You cannot execute this function from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL Worksheet.The number 1
to indicate a successful invocation of OLAP_CONDITION
.
OLAP_CONDITION( r2c IN RAW(32), expression IN VARCHAR2, event IN NUMBER DEFAULT 1); RETURN NUMBER;
The name of a column specified by a ROW2CELL
clause in the limit map. This parameter is used by OLAP_CONDITION
to identify a particular invocation of OLAP_TABLE
.
The ROW2CELL
column is used in the processing of the single-row functions. (See "OLAP_EXPRESSION") OLAP_CONDITION
simply uses it as an identifier.
For information on creating a ROW2CELL
column, see "ROW2CELL Clause".
A single OLAP DML command to be executed within the context of the OLAP_TABLE
function identified by the r2c parameter
The event during OLAP_TABLE
processing that triggers the execution of the OLAP DML command specified by the expression parameter. This parameter can have the value 0, 1, or 2, as described in Table A-2
Entry Points for OLAP_CONDITION in the OLAP_TABLE Limit Map
Parameters of OLAP_CONDITION
identify an invocation of OLAP_TABLE
, specify an entry point in the limit map, and provide the OLAP DML command to be executed at that entry point.
The target limit map must include a ROW2CELL
column. OLAP_CONDITION
uses this column to identify an instance of OLAP_TABLE
. Within that instance OLAP_CONDITION
executes the OLAP DML command at one of three possible entry points. The entry point that you specify determines whether the condition affects the data returned by the query and whether the condition remains in effect upon completion of the query.
OLAP_CONDITION
can be triggered at any of the following points:
Before the status of the dimensions in the limit map is saved (which occurs before the result set is calculated).
After the result set has been calculated and before it is fetched. (Default)
After the result set has been fetched and the status of the dimensions in the limit map has been restored.
The entry points for OLAP_CONDITION
are described in Table A-2. Refer to "Order of Processing in OLAP_TABLE" to determine where each entry point occurs.
Table A-2 Entry Points for OLAP_CONDITION in the OLAP_TABLE Limit Map
Entry Point | Description |
---|---|
0 |
Execute the OLAP DML command after the The entry point is between steps 1 and 2 in "Order of Processing in OLAP_TABLE". If |
1 |
Execute the OLAP DML command after the conditions of the The entry point is between steps 4 and 5 in "Order of Processing in OLAP_TABLE". If an OLAP DML command (other than If |
2 |
Execute the OLAP DML command after the data is fetched and the status of dimensions in the limit map has been restored. The entry point is after step 8 in "Order of Processing in OLAP_TABLE". If |
Several sample queries using OLAP_CONDITION
are shown in Example A-11, "Queries of UNIT_COST_PRICE_VIEW Using OLAP_CONDITION". These examples use the PRICE_CUBE
in an analytic workspace namedMYAW
. The cube has a time dimension, a product dimension, and measures for unit cost and unit price.
The examples are based on a view called unit_cost_price_view
. The SQL for creating this view is shown in Example A-10, "View of PRICE_CUBE". For information about creating views of analytic workspaces, see "Creating Relational Views Using OLAP_TABLE".
Example A-10 View of PRICE_CUBE
-- Create the logical row SQL>CREATE TYPE unit_cost_price_row AS OBJECT ( aw_unit_cost NUMBER, aw_unit_price NUMBER, aw_product VARCHAR2(50), aw_product_gid NUMBER(10), aw_time VARCHAR2(20), aw_time_gid NUMBER(10), r2c RAW(32)); -- Create the logical table SQL>CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row; -- Create the view SQL>CREATE OR REPLACE VIEW unit_cost_price_view AS SELECT aw_unit_cost, aw_unit_price, aw_product, aw_product_gid, aw_time, aw_time_gid, 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 GID aw_product_gid FROM product_gid ATTRIBUTE aw_product FROM product_short_description DIMENSION time WITH HIERARCHY time_parentrel INHIERARCHY time_inhier GID aw_time_gid FROM time_gid ATTRIBUTE aw_time FROM time_short_description ROW2CELL r2c')); -- query 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_PRODUCT_GID AW_TIME AW_TIME_GID R2C ------------ ------------- ---------- -------------- ------- ----------- ----- 211680.12 224713.71 Hardware 3 2000 3 00... 195591.60 207513.16 Hardware 3 2001 3 00... 184413.05 194773.78 Hardware 3 2002 3 00... 73457.31 77275.06 Hardware 3 2003 3 00...
Example A-11 Queries of UNIT_COST_PRICE_VIEW Using OLAP_CONDITION
The queries in this example use OLAP_CONDITION
to modify the query of UNIT_COST_PRICE_VIEW
in Example A-10, "View of PRICE_CUBE". In each query, OLAP_CONDITION
uses a different entry point to limit the TIME
dimension to the year 2000.
In the first query, OLAP_CONDIITON
uses entry point 0. The limited data is returned by OLAP_TABLE
, and the limit remains in effect in the analytic workspace.
SQL>SELECT * FROM unit_cost_price_view WHERE aw_product = 'Hardware' AND aw_time in ('2000', '2001', '2002', '2003') AND OLAP_CONDITION(r2c, 'limit time to time_short_description eq ''2000''', 0)=1 ORDER BY aw_time; AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C ------------ ------------- ---------- -------------- ------- ----------- ----- 211680.12 224713.71 Hardware 3 2000 3 00... --Check status in the analytic workspace SQL>exec dbms_aw.execute('rpr time_short_description'); TIME TIME_SHORT_DESCRIPTION ---- ---------------------- 3 2000 -- Reset status SQL>exec dbms_aw.execute('allstat');
In the next query, OLAP_CONDIITON
uses entry point 1. The limited data is returned by OLAP_TABLE
, but the limit does not remain in effect in the analytic workspace.
Note that the third parameter is not required in this case, since entry point 1 is the default.
SQL>SELECT * FROM unit_cost_price_view WHERE aw_product = 'Hardware' AND aw_time in ('2000', '2001', '2002', '2003') AND OLAP_CONDITION(r2c, 'limit time to time_short_description eq ''2000''', 1)=1 ORDER BY aw_time; AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C ------------ ------------- ---------- -------------- ------- ----------- ----- 211680.12 224713.71 Hardware 3 2000 3 00... --Check status in the analytic workspace SQL>exec dbms_aw.execute('rpr time_short_description'); TIME TIME_SHORT_DESCRIPTION ---- ---------------------- 19 Jan-98 20 Feb-98 21 Mar-98 22 Apr-98 . . . 1 1998 2 1999 3 2000 4 2001 85 2002 102 2003 119 2004 -- Reset status SQL>exec dbms_aw.execute('allstat');
In the final query, OLAP_CONDIITON
uses entry point 2. The limit does not affect the data returned by OLAP_TABLE
, but the limit remains in effect in the analytic workspace.
SQL>SELECT * FROM unit_cost_price_view WHERE aw_product = 'Hardware' AND aw_time in ('2000', '2001', '2002', '2003') AND OLAP_CONDITION(r2c, 'limit time to time_short_description eq ''2000''', 2)=1 ORDER BY aw_time; AW_UNIT_COST AW_UNIT_PRICE AW_PRODUCT AW_PRODUCT_GID AW_TIME AW_TIME_GID R2C ------------ ------------- ---------- -------------- ------- ----------- ----- 211680.12 224713.71 Hardware 3 2000 3 00... 195591.60 207513.16 Hardware 3 2001 3 00... 184413.05 194773.78 Hardware 3 2002 3 00... 73457.31 77275.06 Hardware 3 2003 3 00... --Check status in the analytic workspace SQL>exec dbms_aw.execute('rpr time_short_description'); TIME TIME_SHORT_DESCRIPTION ---- ---------------------- 3 2000