OLAP_EXPRESSION_BOOL
is a SQL function that dynamically executes a single-row boolean function in an analytic workspace and returns the results.
See Also:
Chapter 7, "OLAP_EXPRESSION"This chapter includes the following topics:
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 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_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.
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
The OLAP_EXPRESSION_BOOL
function dynamically executes an OLAP DML boolean expression within the context of an OLAP_TABLE
function.
OLAP_EXPRESSION_BOOL( r2c IN RAW(32), boolean_expression IN VARCHAR2) RETURN NUMBER;
Table 8-1 OLAP_EXPRESSION_BOOL Function Parameters
Parameter | Description |
---|---|
|
The name of a column populated by a
|
|
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". |
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.
Refer to "OLAP_EXPRESSION" for more examples of OLAP single-row functions.