Operators and functions in database configuration formulas
Database configuration formulas can use most mathematical operators and commonly used functions to automatically calculate attribute values.
The following operators can be used in database configuration formulas:
| Operator | Description | Example |
|---|---|---|
| + | Addition | purchaseprice+tax |
| - | Subtraction | enddate-startdate |
| * | Multiplication | unitcost*quantity |
| / | Division | totalcost/quantity |
| % | Remainder | a%b |
| ^ | Power | a^2 |
| && | Logical and | IF (a>10 && b<20, x,y) |
| || | Logical or | IF (a>10 || b<20, x,y) |
| >, >= | Greater than, greater than or equal to | IF (a>=10 && b<20, x,y) |
| <, <= | Less than, less than or equal to | IF (a>=10 || b<20, x,y) |
| =, == | Equals comparison | IF (a=10 || b==c,x,y) |
| !=, <> | Not equals | IF (a<> 10 && b!=c,x,y) |
The following functions can be used in database configuration formulas:
| Function | Number of parameters | Description | Example |
|---|---|---|---|
| IF | 3 | Used for if/then conditions. | IF (a>10 || b<20, x,y) |
| MAX, MIN | 2 | Calculates the minimum and maximum values between two numbers. | MAX(a,b) or MAX(a, MAX(b,c)) |
| ABS | 1 | Calculates the absolute value of a number. | ABS (a) |
| DATE | 3 | Indicates a date in the following format: yyyy, mm, dd. | DATE (2015, 10, 21) |
| DATETIME | 6 | Indicates a date and time in the following format: yyyy, mm, dd, hr, min, sec. DATETIME uses a 24-hour clock. | DATETIME (2015, 10, 21, 14, 0, 0) |
| DURATION | 6 | Indicates the duration of a time in the following format: yyyy, mm, dd, hr, min, sec. | DURATION (0, 0, 3, 2, 0, 0) |
| ROUND | 2 | Rounds a value to a specified decimal value. | ROUND (linecost, 2) |
| NVL | 2 | Returns the alternate value if one value is null. | NVL (a,b) |
| PCT | 2 | Calculates the percentage of the first parameter based on the second parameter. | PCT (Val, total) |
| FLOOR, CEILING | 1 | Calculates the largest or smallest whole number value. | FLOOR (3.2) |
| SQRT, SIN, LOG, LOG10. TAN, COS, ASIN, ACOS, ATAN, SINH, COSH, TANH, RAD, DEG | 1 | Calculate basic math functions. | SQRT (4) |