1 Basic Elements

Syntax

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

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.

Unary Arithmetic Operators

A unary operator operates on only one operand.

operator operand

Binary Arithmetic Operators

A binary operator operates on two operands.

operand operator operand

Concatenation Operator

The concatenation operator (||) combines text expressions.

operand || operand

Simple Comparison Conditions

Comparison conditions compare one expression with another.

You can use these comparison operators:

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

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:

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

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.

expr [ NOT ] BETWEEN expr AND expr

Multiple Conditions

Conjunctions compare a single expression with two conditions.

expr operator condition1 { AND | OR } condition2 

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.

NOT {BETWEEN | IN | LIKE | NULL }

Special Conditions

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

expr IS [ NOT ] NULL

Pattern-Matching Conditions

The pattern-matching conditions compare character data.

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

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

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

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

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 

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.