The table below describes the usage of supported functions available from the Add Element by Expression dialog and the Edit Group Filter dialog.
| Function | Description | Syntax | Example |
|---|---|---|---|
|
IF |
Logical IF operator Evaluates boolean_expr, and returns true_return if boolean_expr is true, and false_return if boolean_expr is false. |
IF (boolean_expr, true_return, false_return) |
IF (G_1.DEPARTMENT_ID == 10, 'PASSED', 'FAIL')returns 'PASSED' if DEPARTMENT_ID = 10, otherwise returns 'FAIL' |
|
NOT |
Logical NOT operator Evaluates boolean_expr, and returns true if boolean_expr is false. |
STRING(NOT(boolean_expr)) |
STRING(NOT(G_1.JOB_ID == 'MANAGER'))returns 'TRUE' if JOB_ID = MANAGER, otherwise returns 'FALSE' |
|
AND |
Logical AND operator Evaluates boolean_expr1 and boolean_expr2, and returns true if both boolean expressions are true, otherwise returns false. |
STRING(AND(boolean_expr1, boolean_expr2, ...)) |
STRING(AND (G_1.JOB_ID == 'MANAGER', G_1.DEPARTMENT_ID == 10))returns 'TRUE' if both JOB_ID = MANAGER and DEPARTMENT_ID = 10, otherwise returns 'FALSE' |
|
&& |
Logical AND operator Evaluates boolean_expr1 and boolean_expr2, and returns true if both boolean expressions are true, otherwise returns false. |
STRING(boolean_expr1 && boolean_expr2) |
STRING(G_1.JOB_ID == 'MANAGER' && G_1.DEPARTMENT_ID == 10) returns 'TRUE' if both JOB_ID = MANAGER and DEPARTMENT_ID = 10, otherwise returns 'FALSE' |
|
|| |
Logical OR operator Evaluates boolean_expr1 and boolean_expr2 and returns true if both boolean expressions are true, otherwise returns false. |
STRING(OR(boolean_expr1, boolean_expr2) |
STRING(OR (G_1.JOB_ID == 'MANAGER', G_1.DEPARTMENT_ID == 10)) returns 'TRUE' if either JOB_ID = MANAGER or DEPARTMENT_ID = 10, otherwise returns 'FALSE' |
|
MAX |
Returns the maximum value of the element in the set. |
MAX(expr1, expr2, expr3, ...) |
MAX(G1_Salary, 10000) returns max of salary or 10000 |
|
MIN |
Returns the minimum value of the element in the set. |
MIN(expr1, expr2, expr3, ...) |
MIN(G1_Salary,5000) returns min of salary or 5000 |
|
ROUND |
Returns a number rounded to the integer places right of the decimal point. |
ROUND(number[,integer]) If integer is omitted, number is rounded to 0 places. Integer can be negative to round off digits left of the decimal point. Integer must be an integer. |
ROUND(2.777) returns 3 ROUND(2.777, 2) returns 2.78 |
|
FLOOR |
Returns the smallest integer equal to or less than n. |
FLOOR(n) |
FLOOR(2.777) returns 2 |
|
CEILING |
Returns the largest integer greater than or equal to n. |
CEILING(n) |
CEILING(2.777) returns 3 |
|
ABS |
Returns the absolute value of n. |
ABS(n) |
ABS(-3) returns 3 |
|
AVG |
Returns the average value of the expression. |
AVG(expr1, expr2, expr3, ...) |
AVG(G_1.SALARY,G_1.COMMISSION_PCT*G_1.SALARY) returns the average of SALARY and COMMISSION For example, if SALARY = 14000 and COMMISSION_PCT = .4, the expression evaluates to 9800.0 |
|
LENGTH |
Returns the length of an array. The LENGTH function calculates the length using characters as defined by the input character set. If char is null, the function returns null. If char is an array, it returns the length of the array. |
LENGTH(expr) |
Example to return the length of an array: LENGTH{1, 2, 4, 4}) returns 4 Example to return the length of a string: LENGTH('countries') returns 9 |
|
SUM |
Returns the sum of the value of the expression. |
SUM(expr1, expr2, ...) |
SUM (G_1.SALARY, G_1.COMMISSION_PCT*G_1.SALARY) returns sum of salary and commission For example, if SALARY = 14000 and COMMISSION_PCT =.4, the expression evaluates to 19,600.0 |
|
NVL |
Replaces null (returned as a blank) with a string in the results of a query. |
NVL(expr1, expr2) If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. |
NVL(G_1.COMMISSION_PCT, .3) returns .3 when G_1.COMMISSION_PCT is null |
|
CONCAT |
Returns char1 concatenated with char2. |
CONCAT(char1, char2) |
CONCAT(CONCAT(First_Name, ' '), Last_Name) where First_Name = Joe and Last_Name = Smith returns Joe Smith |
|
STRING |
Returns char as a string data type. |
STRING(expr) |
STRING(G1_SALARY) where salary = 4400 returns 4400 as a string |
|
SUBSTRING |
Extracts a substring from a string. |
SUBSTRING(string, start_pos, end_pos) string is the source string. start_pos is the position to start the extraction. end_pos is the end position of the string to extract (optional). |
SUBSTRING('this is a test', 5, 7) returns "is" (that is, characters 6 through 7)SUBSTRING('this is a test', 5) returns "is a test" |
|
INSTR |
Returns the position/location of the first character of a substring in a string. |
INSTR(string1, string2) string1 is the string to search. string2 is the substring to search for in string1. |
INSTR('this is a test', 'is a') returns 5 |
|
DATE |
Converts a valid Java date string to a date data type in canonical format. |
DATE(char, format_string) where (1) char is any valid Java date string (for example, 13-JAN-2013)(2) format_string is the Java date format of the input string (for example, dd-MMM-yyyy) The input and format strings must be a valid Java date format string. |
DATE(01-Jan-2013,'dd-MMM-yyyy') returns 2013-01-01T08:00:00.000+00:00 |
|
FORMAT_DATE |
Converts a date argument in the Java date format to a formatted string. |
FORMAT_DATE(date,format_string) |
FORMAT_DATE(SYSDATE,'dd-MMM-yyyy') where the value of SYSDATE = 2013-01-24T16:32:45.000-08:00returns 24-Jan-2013 |
|
FORMAT_NUMBER |
Converts a number or numeric string to a string in the specified number format. |
FORMAT_NUMBER(number,format_string) |
FORMAT_NUMBER(SOME_NUMBER, '$9,999.00') where the value of SOME_NUMBER = 12345.678returns $12,345.68 |
|
DECODE |
Replaces the value of an expression with another value based on the specified search and replace criteria. |
DECODE(expr, search, result [, search, result]...[, default]) |
DECODE(PROD_FAMILY_CODE,100,'Colas',200,'Root Beer',300,'Cream Sodas',400,'Fruit Sodas','Other')returns(1) 'Colas' if PROD_FAMILY_CODE = 100(2) 'Root Beer' if PROD_FAMILY_CODE = 200(3) 'Cream Sodas' if PROD_FAMILY_CODE = 300(4) 'Fruit Sodas' if PROD_FAMILY_CODE = 400(5) 'Other' if PROD_FAMILY_CODE is any other value |
|
REPLACE |
Replaces a sequence of characters in a string with another set of characters. |
REPLACE(expr,string1,string2) where string1 is the string to search for and string2 is the string to replace. |
REPLACE(G_1.FIRST_NAME,'B','L') where G_1.FIRST_NAME = Barry returns Larry |