8 OLAP_EXPRESSION_BOOL

OLAP_EXPRESSION_BOOL is a SQL function that dynamically executes a single-row boolean function in an analytic workspace and returns the results.

OLAP_EXPRESSION_BOOL Overview

OLAP_EXPRESSION_BOOL acts as a boolean single-row function within the context of a SELECT FROM OLAP_TABLE statement. You can specify OLAP_EXPRESSION_BOOL in the same way you specify other Oracle single-row functions, notably in the select list and WHERE clauses.

Single-Row Functions

Single-row functions return a single result row for every row of a queried table or view. Oracle supports a number of predefined single-row functions, for example COS, LOG, and ROUND which return numeric data, and UPPER and LOWER which return character data. For more information on single-row functions, refer to the Oracle Database SQL Reference.

The OLAP single-row functions, OLAP_EXPRESSION and its variants for text, date, and boolean data, return the result of an OLAP DML expression that you specify. The OLAP DML supports a rich syntax for specifying computations ranging from simple arithmetic expressions to statistical, financial, and time-series operations.

You can use OLAP_EXPRESSION_BOOL to dynamically perform any valid boolean expression within an analytic workspace and retrieve its results. For more information on boolean expressions in the OLAP DML, search for "boolean expression" in the Oracle OLAP DML Reference.

OLAP_EXPRESSION_BOOL and OLAP_TABLE

OLAP_TABLE uses a limit map to present the multidimensional data from an analytic workspace in tabular form. The limit map specifies the columns of the logical table. When an OLAP_EXPRESSION_BOOL function is specified in the select list of the query, OLAP_TABLE generates an additional column for the results of the function.

To use OLAP_EXPRESSION_BOOL, 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.

OLAP_EXPRESSION_BOOL Example

The following script was used to create the view awunits_view, which is used in Example 8-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('REPORT 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('REPORT 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('REPORT time_aw_short_description');
 
TIME_AW                     TIME_AW_SHORT_DESCRIPTION
--------------- --------------------------------------------------
4               2001

Example 8-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('REPORT 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 8-1 OLAP_EXPRESSION_BOOL Function Parameters

Parameter Description

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".

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.