OLAP DML Operators

An operator is a symbol that transforms a value or combines it in some way with another value. This chapter introduces the OLAP DML operators. It includes the following topics:

Categories of OLAP DML Operators
Arithmetic Operators
Comparison and Logical Operators
Assignment Operator

For information on the OLAP DML conditional operators, see "Conditional Expressions". For information on the OLAP DML substitution operator, see "Substitution Expressions".

Categories of OLAP DML Operators

Table 3-1, "OLAP DML Operators" describes the categories of OLAP DML operators.

Table 3-1 OLAP DML Operators

Category	Description
Arithmetic	Operators that you can use in numeric expressions with numeric data to produce a numeric result. You can also use some arithmetic operators in date expressions with a mix of date and numeric data, which returns either a date or numeric result. For a list of arithmetic operators, see "Arithmetic Operators". For more information on numeric expressions, see "Numeric Expressions"
Comparison	Operators that you can use to compare two values of the same basic type (numeric, text, date, or, in rare cases, Boolean), which returns a `BOOLEAN` result. For a list of comparison operators, see "Comparison and Logical Operators". For more information on `BOOLEAN` expressions, see "Boolean Expressions".
Logical	The `AND`, `OR`, and `NOT` operators that you can use to transform `BOOLEAN` values using logical operations, which returns a `BOOLEAN` result. For a list of logical operators, see "Comparison and Logical Operators". For more information on `BOOLEAN` expressions, see "Boolean Expressions".
Assignment	An operator that you use to assign the results of an expression into an object or to assign a value to an OLAP DML option. For more information on using assignment statements, see "Assignment Operator", SET, and SET1.
Conditional	The `IF...THEN...ELSE`, `SWITCH`, and `CASE` operators that you can use to use to select between values based on a condition. For more information, see "Conditional Expressions".
Substitution	The `&` (ampersand) operator that you can use to evaluate an expression and substitute the resulting value. For more information, see "Substitution Expressions".

Arithmetic Operators

Table 3-2, "Arithmetic Operators" shows the OLAP DML arithmetic operators, their operations, and priority where priority is the order in which that operator is evaluated. Operators of the same priority are evaluated from left to right. When you use two or more operators in a numeric expression, the expression is evaluated according to standard rules of arithmetic. You must code a comma before a negative number that follows another numeric expression, or the minus sign is interpreted as a subtraction operator. For example, intvar,-4

Table 3-2 Arithmetic Operators

Operator	Operation	Priority
`-`	Sign reversal	1
`**`	Exponentiation	2
`*`	Multiplication	3
`/`	Division	3
*	Addition	4
`-`	Subtraction	4

Comparison and Logical Operators

You use comparison and logical operators to make expressions in much the same way as arithmetic operators. Each operator has a priority that determines its order of evaluation. Operators of equal priority are evaluated left to right, unless parentheses change the order of evaluation. However, the evaluation is halted when the truth value is already decided.

Table 3-3, "Comparison and Logical Operators" shows the OLAP DML comparison operators and logical operators (AND, OR, and NOT). It lists the operator, the operations, example, and priority where priority is the order in which that operator is evaluated. Operators of the same priority are evaluated from left to right.

Table 3-3 Comparison and Logical Operators

Operator	Operation	Example	Priority
`NOT`	Returns opposite of `BOOLEAN` expression	`NOT(YES) = NO`	1
`EQ`	Equal to	`4 EQ 4 = YES`	2
`NE`	Not equal to	`5 NE 2 = YES`	2
`GT`	Greater than	`5 GT 7 = NO`	2
`LT`	Less than	`5 LT 7 = YES`	2
`GE`	Greater than or equal to	`8 GE 8 = YES`	2
`LE`	Less than or equal to	`8 LE 9 = YES`	2
`IN`	Is a date in a time period?	`'1Jan02' IN w1.02 = YES`	2
`LIKE`	Does a text value match a specified text pattern?	`'Finance' LIKE '%nan%' = YES`	2
`AND`	Both expressions are true	`8 GE 8 AND 5 LT 7 = YES`	3
`OR`	Either expression is true	`8 GE 8 OR 5 GT 7 = YES`	4

Assignment Operator

In the OLAP DML, as in many other programming languages, the = (equal) sign is used as an assignment operator.

An expression creates temporary data; you can display the resulting values, but these values are not automatically stored in your analytic workspace for future refrence during a session. You use an assignment statement to store the result of an expression in an object that has the same data type and dimensions as the expression. If you update and commit the object, then the values are available to you in future sessions.

Like other programming languages, an assignment statement in the OLAP DML sets the value of the target expression equal to the results of the source expression. However, an OLAP DML assignment statement does not work exactly as it does in other programming languages. Like many other OLAP DML statements it does not assign a value to a single cell, instead, when the target-expression is a multidimensional object, Oracle OLAP loops through the cells of the target object setting each one to the results of the source-expression. Additionally, you can use UNRAVEL to copy the values of an expression into the cells of a target object when the dimensions of the expression are not the same as the dimensions of the target object.

For more information on using assignment statements in the OLAP DML, see SET and SET1.