Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

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

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

1 Basic Elements

This chapter describes the basic building blocks of the OLAP expression syntax. It contains these topics:

Dimensional Object Names

The naming conventions for dimensional objects follow standard Oracle naming rules. All names are case-insensitive.

Syntax

owner.{ cube | dimension | table }.{ measure | column | attribute } 

Table 1-1 Naming Conventions for Dimensional Objects

Convention Quoted ID Unquoted ID

Initial Character

Any character.

Alphabetic character from the database character set.

Other Characters

All characters, punctuation marks, and spaces are permitted.

Double quotation marks and nulls (\0) are not permitted.

Alphanumeric characters from the database character set and underscore (_) are permitted.

The dollar sign ($) and pound sign (#) are permitted but not recommended.

Double quotation marks and nulls (\0) are not permitted.

Reserved Words

Permitted but not recommended.

Not permitted.


Examples

GLOBAL.UNITS_CUBE.SALES identifies the SALES measure in the Units Cube.

TIME.DIM_KEY and TIME.LEVEL_NAME identify columns in the Time views.

TIME.CALENDAR identifies the CALENDAR hierarchy in the Time dimension.

TIME.CALENDAR.CALENDAR_YEAR identifies the CALENDAR_YEAR level of the CALENDAR hierarchy in the Time dimension.

GLOBAL.UNITS_FACT.MONTH_ID identifies a foreign key column in the UNITS_FACT table.

TIME_DIM.CALENDAR_YEAR_DSC identifies a column in the TIME_DIM table.

Dimensional Data Types

Table 1-2 describes the data types that can be used for cubes and measures.

Table 1-2 Dimensional Data Types

Data Type Description

BINARY_DOUBLE

A 64-bit floating number. A BINARY_DOUBLE value requires 9 bytes.

BINARY_FLOAT

A 32-bit floating number. A BINARY_FLOAT value requires 5 bytes.

CHAR (size [BYTE|CHAR])

A fixed length character string with a length of size characters or bytes. The size can range from 1 to 2000.

DATE

A valid date in the range from January 1, 4712 BC to December 31, 9999 CE. It contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone. The default format is determined explicitly by the NLS_DATE_FORMAT parameter and implicitly by the NLS_TERRITORY parameter. A DATE value requires 7 bytes.

DECIMAL (p,s)

A decimal number with precision p and scale s represented as a NUMBER data type.

FLOAT [(p)]

A subtype of NUMBER with precision p. A FLOAT is represented internally as NUMBER. The precision can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.

INTEGER

A whole number represented as a NUMBER data type with a scale of 0.

INTERVAL DAY[(day_precision)] TO SECOND[(second_precision)]

A period of time in days, hours, minutes, and seconds. The day precision is the maximum number of digits in the DAY datetime field. The default is 2. The second precision is the number of digits in the fractional part of the SECOND field. The default value is 6. Both day and second precision can have a value from 0 to 9. An INTERVAL DAY TO SECOND value requires 11 bytes.

INTERVAL YEAR[(precision)] TO MONTH

A period of time in years and months. The precision is the number of digits in the YEAR datetime field, which can have a value of 0 to 9. The default precision is 2 digits. An INTERVAL YEAR TO MONTH value requires 5 bytes.

NCHAR[(size)]

A fixed length character string with a length of size characters. The size can range from 1 character to 2000 bytes. The maximum number of characters depends on the national character set, which can require up to four bytes per character.

NUMBER [(p[,s])]

A decimal number with precision p and scale s. The precision can range from 1 to 38. The scale can range from -84 to 127. A NUMBER value requires from 1 to 22 bytes.

NVARCHAR2(size)

A variable length Unicode character string with a maximum length of size characters. The size can range from 1 character to 4000 bytes. The maximum number of characters depends on the national character set, which can require up to four bytes per character.

TIMESTAMP[(precision)]

A valid date that contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have a time zone. The precision is the number of digits in the fractional part of the SECOND field, which can have a value of 0 to 9. The default precision is 6 digits. The default format is determined explicitly by the NLS_DATE_FORMAT parameter and implicitly by the NLS_TERRITORY parameter. A TIMESTAMP value requires from 7 to 11 bytes depending on the precision.

TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

A valid date with the same description as TIMESTAMP WITH TIME ZONE with these exceptions:

  • The data is stored in the database with the database time zone.

  • The data is converted to the session time zone when it is retrieved.

  • A TIMESTAMP WITH LOCAL TIME ZONE value requires from 7 to 11 bytes depending on the precision.

TIMESTAMP[(precision)] WITH TIME ZONE

A valid date that contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. The precision is the number of digits in the fractional part of the SECOND field, which can have a value of 0 to 9. The default precision is 6 digits. The default format is determined explicitly by the NLS_DATE_FORMAT parameter and implicitly by the NLS_TERRITORY parameter. A TIMESTAMP WITH TIMEZONE value requires 13 bytes.

VARCHAR2(size [BYTE|CHAR])

A variable length character string with a maximum length of size characters or bytes. The size can range from 1 to 4000.


Operators

An operator manipulates data items and returns a result. Operators manipulate individual data items called operands or arguments. They are represented by special characters or by keywords. Syntactically, an operator appears before an operand, after an operand, or between two operands.

The OLAP Expression Syntax has these types of operators:

For conditional operators, go to "Conditions".

Unary Arithmetic Operators

A unary operator operates on only one operand.

Table 1-3 Unary Operators

Operator Description

+

Positive value

-

Negative value


Syntax

operator operand

Example

-5 is a negative number.

Binary Arithmetic Operators

A binary operator operates on two operands.

Table 1-4 Binary Operators

Operator Description

+

Add

-

Subtract

*

Multiply

/

Divide


Syntax

operand operator operand

Examples

Here are two simple examples using numeric literals for the operands.

7 * 2 is 14.

(8/2) + 1 is 5.

This example multiplies the values of the Sales measure by a numeric literal to create a calculated measure named Sales Budget.

UNITS_CUBE.SALES * 1.06

Table 1-5 Multiplication Operator Example

Product Level Sales Sales Budget

Hardware

CLASS

124191336

131642816

Desktop PCs

FAMILY

74556528

79029919

Monitors

FAMILY

3972142

4210470

Memory

FAMILY

5619219

5956372

Modems/Fax

FAMILY

5575726

5910269

CD/DVD

FAMILY

16129497

17097267

Portable PCs

FAMILY

18338225

19438518


The next example creates a calculated measure named Profit by subtracting Cost from Sales.

UNITS_CUBE.SALES - UNITS_CUBE.COST

Product Level Sales Cost Profit
Hardware CLASS 124191336 116058248 8133088
Desktop PCs FAMILY 74556528 71937312 2619215
Monitors FAMILY 3972142 3546195 425947
Memory FAMILY 5619219 4962527 656692
Modems/Fax FAMILY 5575726 5162879 412847
CD/DVD FAMILY 16129497 12510832 3618664
Portable PCs FAMILY 18338225 17938502 399723

Concatenation Operator

The concatenation operator (||) combines text expressions.

Syntax

operand || operand

Example

'The date today is: ' || sysdate generates a text string such as The date today is: 23-AUG-06.

The next example concatenates the level name and dimension keys of the Product dimension to create an identifier.

PRODUCT.LEVEL_NAME || ' ' || PRODUCT.DIM_KEY

Level Dim Key Identifier
CLASS HRD CLASS HRD
FAMILY DTPC FAMILY DTPC
FAMILY MON FAMILY MON
FAMILY MEM FAMILY MEM
FAMILY MOD FAMILY MOD
FAMILY DISK FAMILY DISK
FAMILY LTPC FAMILY LTPC

Conditions

A condition specifies a combination of one or more expressions and logical (Boolean) operators. The OLAP Expression Syntax has these types of conditions:

Return Value

NUMBER (0=FALSE, 1=TRUE)

Simple Comparison Conditions

Comparison conditions compare one expression with another.

You can use these comparison operators:

Table 1-6 Simple Comparison Operators

Operator Description

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

=

Equal to

!= ^=<>

Not equal to


Syntax

expr { > | >= | < | <= | = | != | ^= | <> } expr 

Arguments

expr can be any expression.

Examples

5 > 3 is true, 4 != 5 is true, 6 >= 9 is false.

Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery.

You can use these comparison operators:

Table 1-7 Group Comparison Operators

Operator Description

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

=

Equal to

!= ^= <>

Not equal to

ANY SOME

Returns true if one or more values in the list match, or false if no values match.

ALL

Returns true if all values in the list match, or false if one or more values do not match.


Syntax

expr
     { = | != | ^= | <> | > | < | >= | <= }
     { ANY | SOME | ALL }
     ({ expression_list | subquery })

Examples

5 <= ALL (5, 10, 15) is true, 5 <> ANY (5, 10, 15) is true.

Range Conditions

The BETWEEN operator tests whether a value is in a specific range of values. It returns true if the value being tested is greater than or equal to a low value and less than or equal to a high value.

Syntax

expr [ NOT ] BETWEEN expr AND expr

Example

7 NOT BETWEEN 10 AND 15 is true.

Multiple Conditions

Conjunctions compare a single expression with two conditions.

Table 1-8 Conjunctions

Operator Description

AND

Returns true if both component conditions are true. Returns false if either is false.

OR

Returns true if either component condition is true. Returns false if both are false.


Syntax

expr operator condition1 { AND | OR } condition2 

Example

5 < 7 AND 5 > 3 is true; 5 < 3 OR 10 < 15 is true.

Negation Conditions

The NOT operator reverses the meaning of a condition. It returns true if the condition is false. It returns false if the condition is true.

Syntax

NOT {BETWEEN | IN | LIKE | NULL }

Example

5 IS NOT NULL is true; 5 NOT IN (5, 10, 15) is false.

Special Conditions

The IS operator tests for special conditions, such as nulls, infinity and values that are not numbers.

Table 1-9 Special Conditions Operators

Operator Description

IS INFINITE

Returns true if the expression is infinite, or false otherwise. For mappings only.

IS NAN

Returns true if the expression is not a number, or false otherwise. For mappings only.

IS NULL

Returns true if the expression is null, or false otherwise.


Syntax

expr IS [ NOT ] NULL

Example

13 IS NOT NULL is true.

Pattern-Matching Conditions

The pattern-matching conditions compare character data.

LIKE Operators

The LIKE operators specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE operators can match patterns defined by special pattern-matching ("wildcard") characters.

You can choose from these LIKE operators:

Table 1-10 LIKE Pattern-Matching Operators

Operator Description

LIKE

Uses characters in the input character set.

LIKEC

Uses Unicode complete characters. It treats a Unicode supplementary character as two characters.

LIKE2

Uses UCS2 code points. It treats a Unicode supplementary character as one character.

LIKE4

Uses UCS4 code points. It treats a composite character as one character.


Syntax

char1 [ NOT ] ( LIKE | LIKEC | LIKE2 | LIKE4 )
  char2 [ ESCAPE esc_char ]

Arguments

char1 is a text expression for the search value.

char2 is a text expression for the pattern. The pattern can contain these wildcard characters:

  • An underscore (_) matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

  • A percent sign (%) can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. A '%' cannot match a null.

esc_char is a text expression, usually a literal, that is one character long. This escape character identifies an underscore or a percent sign in the pattern as literal characters instead of wildcard characters. You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @% to search for % and @@ to search for @.

Examples

'Ducks' LIKE 'Duck_' and 'Ducky' LIKE 'Duck_' are true.

'Duckling' LIKE 'Duck_' is false.

'Duckling' LIKE 'Duck%' is true.

Literal Expressions

The OLAP Expression Syntax has three types of literal expressions: strings, numbers, and null. Other data types must be created using conversion functions such as TO_DATE.

The terms text literal, character literal, and string are used interchangeably. They are always enclosed in single quotes to distinguish them from object names.

Examples

'A Literal Text String'

'A Literal Text String with ''Quotes '''

'A Literal Text String
That Crosses Into a Second Line'

2

2.4

+1

-1

NULL

CASE Expressions

CASE expressions let you use IF... THEN... ELSE logic in expressions.

In a simple case expression, CASE searches for the first WHEN... THEN pair for which expr equals comparison_expr, then it returns return_expr. If none of the WHEN... THEN pairs meet this condition, and an ELSE clause exists, then CASE returns else_expr. Otherwise, CASE returns null.

In a searched CASE expression, CASE searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, CASE returns else_expr. Otherwise, CASE returns null.

Return Value

Same as the else_expression argument

Syntax

CASE { simple_case_expression
     | searched_case_expression
     }
     [ ELSE else_expression ]
     END

simple_case_expression::=
     expr WHEN comparison_expr
          THEN return_expr
          [ WHEN comparison_expr
            THEN return_expr ]...

searched_case_expression::=
     WHEN condition THEN return_expr
     [ WHEN condition THEN return_expr ]...

Arguments

expr is the base expression being tested.

comparison_expr is the expression against which expr is being tested. It must be the same basic data type (numeric or text) as expr.

condition is a conditional expression.

return_expr is the value returned when a match is found or the condition is true.

Examples

This statement returns Single Item or Value Pack depending on whether the PACKAGE attribute of the PRODUCT dimension is null or has a value:

CASE PRODUCT.PACKAGE WHEN NULL THEN 'Single Item'  ELSE 'Value Pack'END
Product Package Category
1.44MB External 3.5" Diskette Executive Value Pack
1GB USB Drive -- Single Item
512MB USB Drive -- Single Item
56Kbps V.90 Type II Modem Executive Value Pack
56Kbps V.92 Type II Fax/Modem Laptop Value Pack Value Pack
Deluxe Mouse Executive Value Pack
Envoy Ambassador -- Single Item
Envoy Executive Executive Value Pack
Envoy External Keyboard Executive Value Pack
Envoy Standard Laptop Value Pack Value Pack
External - DVD-RW - 8X Executive Value Pack
External 101-key keyboard Multimedia Value Pack
External 48X CD-ROM -- Single Item
Internal - DVD-RW - 6X Multimedia Value Pack

The next statement increases the unit price by 20%, truncated to the nearest dollar, if the difference between price and cost is less than 10%. Otherwise, it returns the current unit price.

CASE   
  WHEN PRICE_CUBE.UNIT_PRICE < PRICE_CUBE.UNIT_COST * 1.1 
  THEN TRUNC(PRICE_CUBE.UNIT_COST * 1.2)  ELSE PRICE_CUBE.UNIT_PRICE
END
Product Cost Old Price
1GB USB Drive 483.55 546.83
512MB USB Drive 234.69 275.91
56Kbps V.90 Type II Modem 135.72 158.58
56Kbps V.92 Type II Fax/Modem 95.01 111.08
Envoy Ambassador 2686.01 2850.88
Envoy Executive 2799.80 2943.96
Envoy Standard 1933.82 1921.62
External - DVD-RW - 8X 263.83 300.34
External 48X CD-ROM 223.11 254.15
Internal - DVD-RW - 6X 134.46 160.18
Internal 48X CD-ROM 108.32 127.54
Internal 48X CD-ROM USB 46.00 68.54
Monitor- 17"Super VGA 228.53 269.70
Monitor- 19"Super VGA 445.04 504.84
Sentinel Financial 1685.72 1764.14
Sentinel Multimedia 1849.17 1932.54
Sentinel Standard 1572.98 1610.53

The next example creates a Sales Budget calculated measure by multiplying Sales from the previous year by 1.06 for a 6% increase. The detail levels of all dimensions are excluded from the calculation. The Budget is projected only using data from 2006 or later.

CASE 
   WHEN TIME.END_DATE >= TO_DATE('01-JAN-2006') 
      AND TIME.LEVEL_NAME IN ('CALENDAR_YEAR', 'CALENDAR_QUARTER') 
      AND PRODUCT.LEVEL_NAME != 'ITEM' 
      AND CUSTOMER.LEVEL_NAME IN ('TOTAL', 'REGION', 'WAREHOUSE')
   THEN TRUNC(LAG(UNITS_CUBE.SALES, 1) OVER HIERARCHY 
      (TIME.CALENDAR BY ANCESTOR AT LEVEL TIME.CALENDAR.CALENDAR_YEAR 
      POSITION FROM BEGINNING) * 1.06)
   ELSE NULL
END 
Product Time Sales
Hardware Q1.05 28172590
Hardware Q2.05 34520379
Hardware Q3.05 29466573
Hardware Q4.05 32031795
Hardware Q1.06 32711891
Hardware Q2.06 33637473
Hardware Q3.06 29227635
Hardware Q4.06 31319881
Hardware Q1.07 --
Hardware Q2.07 --
Hardware Q3.07 --
Hardware Q4.07 --

Qualified Data References (QDRs)

Qualified data references (QDRs) limit a dimensional object to a single member in one or more dimensions for the duration of a query.

Syntax

expression [ qualifier [ , qualifier]... ]

qualifier::=

dimension_id = member_expression

Note: The outside square brackets shown in bold are part of the syntax. In this case, they do not indicate an optional argument.

Arguments

expression is a dimensional expression, typically the name of a measure.

dimension_id is a cube dimension of expression.

member_expression resolves to a single member of dimension_id.

Examples

global.sales[global.time = 'CY2007'] returns Sales values for the year 2007.

sales[customer = 'US', time = 'CY2007'] returns Sales values only for the United States in calendar year 2007.