In most cases, an expression is a comparison between numbers or other data, like a mathematical formula.
For example, when you perform a Mass Update process, you
want to only include employees with an original hire date before
June 1, 2023. To do this, use the following
expression:
PE.P_ORIGHIRE
< {06/01/2023}
Parts of expressions
Usually, the expression compares a database field and a value. It consists of three parts:
This identifies the database and the field to be used for the comparison.
In the example:
- PE is the "alias" for the HRPERSNL database file ( the Employee Personnel file)
- P_ORIGHIRE is the name of the field (the Original Hire Date)
The operator specifies how the database field is compared to the value. In the example, the less than symbol (<) tells the system that you want the field to be less than the value.
|
Symbol |
Description |
|---|---|
| = |
Compares two values of the same type. Use for comparing character, numeric, date, and logical data. Note: When you compare character expressions with the = operator, the results might not be exactly what you expect. Character expressions are compared character for character from left to right until one of the expressions isn’t equal to the other, or until the end of the expression on the right side of the = operator is reached. For example: ="GA" will find both GA and GAUI. |
| == |
Performs an exact comparison of character data. The expressions on both sides of the == operator must contain exactly the same characters, including blanks, to be considered equal. For example, =="GA" will find GA, but not GAUI |
| > |
Greater than |
|
>= |
Greater than or equal to |
| < |
Less than |
| <= |
Less than or equal to |
| <> |
Not equal to |
This is the part of the expression that the field is compared to. The data type of the value must match the data type of the field.
In the example, the Original Hire Date (P_ORIGHIRE)
is a date data type so the data type of the value must also be a
date.
You cannot use 999 for the value because you can’t
compare a date to a number. If the field in the expression is
last name, you cannot enter a date for the value because a date doesn’t
match the data type of a last name.
Rules for using field names in an expression or function
-
All field names must be spelled correctly. See the Data Dictionary for correct spellings.
Important! Use the correct capitalization. For example, SALESREP is not equal to SalesRep. To avoid capitalization problems, you can use the UPPER function when making comparisons. For example, the following expression tells the system to convert the job code to upper case before making the comparison: UPPER(PE.P_JOBCODE)="SALESREP"
- Alphanumeric (character) data must be surrounded by quotes.
- Dates must be enclosed in braces, i.e. {06/01/2023}.
- If you are building a compound expression (when you need more than one condition to be met), the conditions must be separated by the word AND or by the word OR. You should use parentheses in complex conditional expressions. See Building compound expressions.