Skip Headers
Oracle® OLAP Reference
10g Release 2 (10.2)

Part Number B14350-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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.


See Also:

See "OLAP_TABLE Overview" for information about creating views of analytic workspaces.

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

Example 31-1 OLAP_EXPRESSION_BOOL Function in a SELECT List

This example uses the view described in "Sample View: GLOBAL_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


OLAP_EXPRESSION_BOOL Syntax

The OLAP_EXPRESSION_BOOL function dynamically executes an OLAP DML boolean expression within the context of an OLAP_TABLE function.

Syntax

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

Parameters

Table 31-1 OLAP_EXPRESSION_BOOL Function Parameters

Parameter Description

r2c

The name of a column populated by a ROW2CELL clause in a call to OLAP_TABLE.

ROW2CELL is a component of a limit map parameter of the OLAP_TABLE function. See "Limit Map Parameter".

boolean_expression

A boolean calculation that will be performed in the analytic workspace. Search for "boolean expression" in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".


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.

Example

Refer to "OLAP_EXPRESSION" for more examples of OLAP single-row functions.