OLAP_EXPRESSION_BOOL

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.

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.

Returns

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

OLAP_EXPRESSION_BOOL returns boolean data. To return numeric, date, or text data, use the OLAP_EXPRESSION, OLAP_EXPRESSION_DATE, or OLAP_EXPRESSION_TEXT functions.

Syntax

OLAP_EXPRESSION_BOOL(
          r2c                  IN   RAW(32),
          boolean_expression   IN   VARCHAR2)
      RETURN NUMBER;

Parameters

r2c: The name of a column specified by a ROW2CELL clause in the limit map. See "ROW2CELL Clause" of OLAP_TABLE
boolean_experssion: An OLAP DML expression that returns a Boolean result.

See:
"Boolean Expressions", Chapter 7, "OLAP DML Functions: A - K", Chapter 8, "OLAP DML Functions: L - Z", and "Guidelines for Using Quotation Marks in OLAP DML Commands".

Examples

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 of the 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