OLAP_EXPRESSION_BOOL Example

The following script was used to create the view awunits_view, which is used in Example 31-1 to illustrate the use of OLAP_EXPRESSION_BOOL.

Sample View: GLOBAL_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(
         'global_aw.globalaw 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

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

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