OLAP_EXPRESSION_BOOL 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 numeric, text, or date data, use OLAP_EXPRESSION, OLAP_EXPRESSION_TEXT, 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 boolean_expression for each row of the table object returned by the OLAP_TABLE function.
OLAP_EXPRESSION_BOOL returns boolean data in the form 0 for false and 1 for true. To return numeric, date, or text data, use the OLAP_EXPRESSION, OLAP_EXPRESSION_DATE, or OLAP_EXPRESSION_TEXT functions.
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 Boolean result.
The following script was used to create the view awunits_view, which is used in Example A-14 to illustrate the use of OLAP_EXPRESSION_BOOL.
Sample View: MYAW_AW.AWUNITS_VIEW
-- Create the logical row
CREATE TYPE awunits_row AS OBJECT (
awtime VARCHAR2(12),
awcustomer VARCHAR2(30),
awproduct VARCHAR2(30),
awchannel VARCHAR2(30),
awunits NUMBER(16),
r2c RAW(32));
/
-- Create the logical table
CREATE TYPE awunits_table AS TABLE OF awunits_row;
/
-- Create the view
CREATE OR REPLACE VIEW awunits_view AS
SELECT awunits,
awtime, awcustomer, awproduct, awchannel, r2c
FROM TABLE(OLAP_TABLE(
'myaw_aw.myawaw DURATION SESSION',
'awunits_table',
'',
'MEASURE awunits FROM units_cube_aw_units_aw
DIMENSION awtime FROM time_aw WITH
HIERARCHY time_aw_parentrel
DIMENSION awcustomer FROM customer_aw WITH
HIERARCHY customer_aw_parentrel
(customer_aw_hierlist ''MARKET_ROLLUP_AW'')
INHIERARCHY customer_aw_inhier
DIMENSION awproduct FROM product_aw WITH
HIERARCHY product_aw_parentrel
DIMENSION channel_aw WITH
HIERARCHY channel_aw_parentrel
ATTRIBUTE awchannel FROM channel_aw_short_description
ROW2CELL r2c'))
WHERE awunits IS NOT NULL;
/
The following query shows some aggregate data in the view. For all products in all markets during the year 2001, it shows the number of units sold through each channel.
SQL> SELECT awchannel, awunits FROM awunits_view
WHERE awproduct = '1'
AND awcustomer = '7'
AND awtime = '4';
AWCHANNEL AWUNITS
--------- -------
All Channels 415392
Direct Sales 43783
Catalog 315737
Internet 55872
The following statements show the descriptions of the Product, Customer, and Time dimension members used in the query.
SQL>execute dbms_aw.execute('limit product_aw to ''1''');
SQL>execute dbms_aw.execute('rpr product_aw_short_description');
PRODUCT_AW PRODUCT_AW_SHORT_DESCRIPTION
--------------- --------------------------------------------------
1 Total Product
SQL>execute dbms_aw.execute('limit customer_aw to ''7''');
SQL>execute dbms_aw.execute('rpr customer_aw_short_description');
CUSTOMER_AW CUSTOMER_AW_SHORT_DESCRIPTION
--------------- --------------------------------------------------
7 Total Market
SQL>execute dbms_aw.execute('limit time_aw to ''4''');
SQL>execute dbms_aw.execute('rpr time_aw_short_description');
TIME_AW TIME_AW_SHORT_DESCRIPTION
--------------- --------------------------------------------------
4 2001
Example A-14 OLAP_EXPRESSION_BOOL Function in a SELECT List
This example uses the view described in "Sample View: MYAW_AW.AWUNITS_VIEW". The following SELECT statement calculates an expression with an alias of lowest_units, which indicates whether or not the number of units of each product was less than 500.
SQL>SELECT awproduct products,
olap_expression_bool(r2c, 'units_cube_aw_units_aw le 500') lowest_units
FROM awunits_view
WHERE awproduct > 39
AND awproduct < 46
AND awcustomer = '7'
AND awchannel = 'Internet'
AND awtime = '4';
PRODUCTS LOWEST_UNITS
--------------- ------------
40 0
41 1
42 1
43 1
44 1
45 0
This query shows that products 41-44 all had less than 500 units. These products are the documentation sets in German, French, Spanish, and Italian. The selected products are shown as follows.
SQL>execute dbms_aw.execute
('limit product_aw to product_aw gt 39 and product_aw lt 46');
SQL>execute dbms_aw.execute('rpr product_aw_short_description');
PRODUCT_AW PRODUCT_AW_SHORT_DESCRIPTION
--------------- --------------------------------------------------
40 O/S Documentation Set - English
41 O/S Documentation Set - German
42 O/S Documentation Set - French
43 O/S Documentation Set - Spanish
44 O/S Documentation Set - Italian
45 O/S Documentation Set - Kanji