Administering Oracle CRM On Demand > Expression Builder > Expression Builder Operators
Expression Builder Operators
This topic describes the operators that you can use in Expression Builder, and the order in which they are evaluated.
NOTE: The language-independent field names and the field-name syntax used in any example expressions in this topic are the field names and the field-name syntax that were used in Expression Builder in releases earlier than Release 29 Service Pack 1. These examples are still valid. However, if you select the corresponding fields for an expression from the Fields list in Expression Builder in Release 29 Service Pack 1 or later, then the field names and the field-name syntax will be different from those shown in the examples. It is recommended that you always select the fields for your expressions from the Fields list in Expression Builder, so that the field names and the field-name syntax are always correct. For more information about the field names and the field-name syntax in expressions, see About Expressions.
Arithmetic Operators
The following table describes the purpose of each arithmetic operator and gives an example of how it is used.
Operator
|
Purpose
|
Example
|
+
|
Add
|
[<Record Number>] + 1
|
-
|
Subtract
|
[<Record Number>] - 1
|
-
|
Negate
|
[<Revenue>] < -100
|
*
|
Multiply
|
[<Subtotal>] * 0.0625
|
/
|
Divide
|
[<Total Items>] / [<Total Orders>]
|
^
|
Exponent
|
[<Grid Height>] ^ 2
|
Logical Operators
The following truth table shows the possible results from the logical operators in Expression Builder.
Logical Operator
|
First Operand
|
Second Operand
|
Result
|
NOT
|
TRUE
|
|
FALSE
|
FALSE
|
|
TRUE
|
NULL
|
|
NULL
|
AND
|
TRUE
|
TRUE
|
TRUE
|
FALSE
|
FALSE
|
FALSE
|
TRUE
|
FALSE
|
FALSE
|
FALSE
|
TRUE
|
FALSE
|
TRUE
|
NULL
|
NULL
|
FALSE
|
NULL
|
FALSE
|
NULL
|
TRUE
|
NULL
|
NULL
|
FALSE
|
FALSE
|
NULL
|
NULL
|
NULL
|
OR
|
TRUE
|
TRUE
|
TRUE
|
FALSE
|
FALSE
|
FALSE
|
TRUE
|
FALSE
|
TRUE
|
FALSE
|
TRUE
|
TRUE
|
TRUE
|
NULL
|
TRUE
|
FALSE
|
NULL
|
NULL
|
NULL
|
TRUE
|
TRUE
|
NULL
|
FALSE
|
NULL
|
NULL
|
NULL
|
NULL
|
Comparison Operators
The following table describes the purpose of each comparison operator and gives an example of how it is used.
Operator
|
Purpose
|
Example
|
=
|
Tests that the first operand is equal to the second operand.
|
[<Last Name>] = "Smith"
|
< >
|
Tests that the first operand is not equal to the second operand.
|
[<Role>] <> "End-User"
|
>
|
Tests that the first operand is greater than the second operand.
|
[<Revenue>] > 5000
|
<
|
Tests that the first operand is less than the second operand.
|
[<Probability>] < .7
|
> =
|
Tests that first operand is greater than or equal to the second operand.
|
[<Revenue>] >= 5000
|
< =
|
Tests that the first operand is less than or equal to the second operand.
|
[<Probability>] <= .7
|
LIKE
|
Tests that the value of the first operand matches the specified string.
|
[<Last Name>] LIKE "Smith"
TIP: You can use the asterisk (*) and question mark (?) pattern matching special characters with the LIKE operator.
|
NOT LIKE
|
Tests that the value of the first operand does not match the specified string.
|
[<Last Name>] NOT LIKE "Smith"
or
NOT ([<Last Name>] LIKE "Smith")
NOTE: The parentheses are required in the second example.
TIP: You can use the asterisk (*) and question mark (?) pattern matching special characters with the NOT LIKE operator.
|
IS NULL
|
Tests that the operand is null.
|
[<Last Name>] IS NULL
|
IS NOT NULL
|
Tests that the operand is not null.
|
[<Last Name>] IS NOT NULL
|
Pattern Matching Special Characters
The following table describes the pattern matching special characters. These special characters can be used with the LIKE and NOT LIKE comparison operators.
Character
|
Purpose
|
Example
|
*
|
Zero or more characters
|
[<Last Name>] LIKE "Sm*" returns all records where the [<Last Name>] value starts with the characters Sm, as in Smith, Smythe, Smart, and so on.
[<Last Name>] LIKE "*om*" returns all records where the [<Last Name>] field contains the characters om, as in Thomas, Thompson, Tomlin, and so on.
|
?
|
One character
|
[<First Name>] NOT LIKE "Da?" returns all records where the [<First Name>] value is three characters long and does not start with the letters Da. Records with Ted, Tom, and Sam are returned, but Dax and Dan are not.
NOT ([<First Name>] LIKE "?o?") returns all records where the [<First Name>] value is three characters long and does not have as its middle character o. Records with Ted and Sam are returned, but Tom and Bob are not.
|
Concatenation Operator
The + (plus) operator is used in emails to concatenate field values and text.
Precedence of Operators
Precedence is the order in which Oracle CRM On Demand evaluates the various operators within a single expression. The following rules apply:
- Operators with higher precedence are evaluated before operators with lower precedence.
- Operators with equal precedence are evaluated from left to right.
- Lower-level numbers indicate higher precedence.
- The use of parentheses affects the order of precedence within an expression. The expression within parentheses is evaluated before the expression outside the parentheses.
The levels of precedence are listed in the following table.
Level
|
Operator
|
1
|
( )
|
2
|
- (negation)
|
3
|
^ (exponentiation)
|
4
|
* (multiplication), / (division)
|
5
|
+ (addition), - (subtraction), NOT logical operator
|
6
|
AND logical operator
|
7
|
OR logical operator
|
8
|
=, <>, >, <, >=, <= comparison operators
|
Related Topics
See the following topics for related Expression Builder information:
|