-

a - b

Subtraction of numeric operands a and b.

Operand Type:

Result Type:

NUMBER

-

-a

Negation of numeric operand a. For example:

-(a*c)

Note: The unary expression operator cannot immediately follow another arithmetical operator unless parenthesized. The following expression is invalid:

a*-b

Instead use either

-b*a

or:

a*(-b)

Operand Type:

NUMBER

Result Type:

NUMBER

/

a / b

Division of numeric operands a and b.

Operand Type:

Result Type:

FLOAT

*

a * b

Multiplication of numeric operands a and b.

Operand Type:

Result Type:

NUMBER

%

a % b

Modulo, the remainder after numerical division of a by b.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

LONG

+

a + b

Addition of numeric operands a and b, or string concatenation.

Operand Type:

Result Type:

NUMBER

Operand Type:

Result Type:

STRING

div

a div b

Division of integer operands without a remainder.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

LONG

AND

a AND b

Logical conjunction

Operand Type:

BOOLEAN BOOLEAN

Result Type:

BOOLEAN

NOT

NOT a

Logical negation

Operand Type:

BOOLEAN

Result Type:

BOOLEAN

OR

a OR b

Logical sum

Operand Type:

BOOLEAN BOOLEAN

Result Type:

BOOLEAN

XOR

a XOR b

Exclusive OR

Operand Type:

BOOLEAN BOOLEAN

Result Type:

BOOLEAN

in

a in {elem[, elem]...}

Tests whether operand a is a member of the specified set. As opposed to the "is in" operation, if operand a is not a member of the set and a null value is a member of the set, then the result is null.

Operand Type:

Any type, set

Result Type:

BOOLEAN

is in

a is in {elem[, elem]...}

Tests whether operand a is a member of the specified set. Always returns TRUE or FALSE.

Operand Type:

Any type, set

Result Type:

BOOLEAN

is not in

a is not in {elem[, elem]...}

Tests whether operand a is not a member of the specified set.

Operand Type:

Any type, set

Result Type:

BOOLEAN

not in

a not in {elem[, elem]...}

Tests whether operand a is not a member of the specified set. As opposed to the "is not in" operation, if operand a is not a member of the set and a null value is a member of the set, then the result is null.

Operand Type:

Any type, set

Result Type:

BOOLEAN

is

a is b

Tests if a is equal to b. Null values are allowed as operands. A typical use is:

a is null

Operand Type:

Any two compatible types or null

Result Type:

BOOLEAN

is not

a is not b

Tests if a is not equal to b. Null values are allowed as operands. A typical use is:

a is not null

Operand Type:

Any two compatible types or null

Result Type:

BOOLEAN

YEAR

Any positive number

DATETIME, DAY

MONTH

1 - 12

DATETIME, DAY

DAY

1 - max.month

DATETIME, DAY

HOUR

0 - 23

DATETIME

MINUTE

0 - 59

DATETIME

SECOND

0 - 59

DATETIME

dateAdd(srcDate, srcValue, fieldName)

Adds the specified srcValue of the type specified by fieldName (YEAR, MONTH, or DAY) to the srcDate. This function allows subtraction, so the srcValue can be negative. The return value is the result of the add (subtract) operation. If any of the operands are invalid or if an attempt is made to add an unsupported fieldName to the date type DAY (HOUR, MINUTE, or SECOND), then the expression reports an error.

Operand Type:

Result Type:

DATE-TYPE

dateDiff(startDate, endDate, fieldName)

Returns the difference between endDate and startDate expressed in fieldName units. If the result exceeds the maximum range of INTEGER, then the value null is returned. If any of the parameters are invalid, the expression reports an error.

A combination of date type DAY and fieldName HOUR, MINUTE, SECOND can be used. The value of these fields is considered to be 0.

Operand Type:

Result Type:

INTEGER

datePart(srcDate, fieldName)

Returns the value of the field fieldName of srcDate. If any of the parameters are invalid, the expression reports an error. For the fields HOUR, MINUTE, and SECOND set for the date type DAY, the function returns 0.

Operand Type:

Result Type:

INTEGER

dateTrunc(srcDate, fieldName)

Truncates less important parts of the srcDate up to the level specified by fieldName. Truncation changes values of the fields by the following rules: MONTH and DAY to 1, HOUR, MINUTE, and SECOND to 0.

The function may be used even for the DAY type with the fieldName HOUR, MINUTE, and SECOND. The function does not have an effect on the data. Result and input values are the same.

If any of the parameters are invalid, the expression reports an error.

Example: For srcDate 5.5.1980 12:35:10 and fieldName HOUR, the function returns 5.5.1980 12:00:00.

Operand Type:

Result Type:

DATE-TYPE

getDate(srcExpression)

Returns the date in the format defined by the specified srcExpression (type DAY or DATETIME), with the time set to zero (HH:mm:ss:sss).

Operand Type:

DATE-TYPE

Result Type:

DAY

getRequestTime()

Returns the time at which processing of the current request started. This is the iWay DQC application start time in batch mode, and the Web service request time in online mode.

Result Type:

DATETIME

now()

Returns the current time with the type DATETIME. This function always returns the time when it is evaluated, that is, the current time.

Result Type:

DATETIME

today()

Returns the current date in type DAY. This function returns the same value for all records (iWay DQC application start date), even if iWay DQC runs past midnight.

Result Type:

DAY

capitalize(srcStr)

Transforms all words in the string srcStr in the following manner: the first character of each word to uppercase and all following characters to lowercase. A word consists of alphabetic characters (letters). All other characters are considered separators.

Operand Type:

STRING

Result Type:

STRING

capitalizeWithException (srcStr,exc[, exc]...)

Transforms all words in the string srcStr (with the exception of the words given as the parameters exc) in the following manner: the first character of each word to uppercase and all following characters to lowercase. A word consists of alphabetic characters (letters). All other characters are considered separators.

Operand Type:

Result Type:

STRING

containsWord(srcStr, srcWord)

Searches for the occurrence of the word srcWord in the string srcStr. Word is a sequence of letters with no whitespaces. Words in the string are defined as sequences of letters separated by a space (' '). Beginning, ending, and multiple spaces are ignored. This function is case-sensitive.

Operand Type:

Result Type:

BOOLEAN

countNonAsciiLetters(srcStr)

Returns the number of characters is the string srcStr that include diacritical marks.

Operand Type:

STRING

Result Type:

INTEGER

cpConvert(str, actualCp, correctCp)

Takes a string as an input wrongly read using the actualCp charset and transforms it into a correct correctCp charset. An example is a file that is all in windows-1250 charset except for one column, a, which is in the latin2 charset. This file will be read using the windows-1250 charset. For the column named a, the following expression can be used:

cpConvert(a, 'windows-1250', 'latin2')

Operand Type:

Result Type:

INTEGER

distinct(srcStr[, srcSeparator[, srcItem[, srcItem]...]])

Returns a string that contains concatenated parts of the original string srcStr. Repeated parts, or parts not listed as srcItem, are omitted. The parameter srcSeparator specifies the separator of the string parts. If srcSeparator is missing or set to NULL, the space character is the separator. The listing of parameters in srcItem restricts the output string parts to the listed items only. If the string srcStr is NULL or empty, the function returns NULL.

Operand Type:

STRING

Result Type:

STRING

Operand Type:

Result Type:

STRING

Operand Type:

Result Type:

STRING

doubleMetaphone(srcStr)

Encodes srcStr to a double metaphone primary string. It removes accents from the srcStr before evaluating the double metaphone value. See the Metaphone article on Wikipedia, at http://www.wikipedia.org.

Operand Type:

STRING

Result Type:

STRING

doubleMetaphone(srcStr, isAlternate)

Encodes srcStr to a double metaphone secondary string if the parameter isAlternate is true. It removes accents from the srcStr before evaluating the double metaphone value. Otherwise, it returns the primary string. See the Metaphone article on Wikipedia, at http://www.wikipedia.org.

Operand Type:

Result Type:

STRING

editDistance(srcStr1, srcStr2 [, caseInsensitive])

Returns the edit distance between strings srcStr1 and srcStr2. The parameter caseInsensitive determines whether case sensitivity should be considered or not. By default, the function is case-insensitive. The difference between Levenshtein and Edit distance lies in the definition of distance of two switched adjacent characters. Levenshtein considers the switch as two changes, whereas Edit distance considers the switch to be one change. If both of the strings are NULL, then the result is 0. If just one of the strings is NULL, then the result is the length of the other string.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

eraseSpacesInNames (srcStr, minLength, onlyUpper)

Removes spaces between separate characters (words of length 1) in string srcStr. The parameter minLength specifies the minimum length of the newly created word (that is, spaces are removed only if, after their removal, the resulting word has a length of at least minLength). The parameter onlyUpper is a Boolean value that restricts the space removal. If set to TRUE, then only spaces between capitals are processed. If set to FALSE, then all spaces between separate characters are processed.

Operand Type:

Result Type:

STRING

find(srcRegex, srcStr [, caseInsensitive])

Verifies whether the string srcStr or its parts match the regular expression srcRegex. The parameter caseInsensitive determines whether case sensitivity should be considered or not. By default, the function is case-sensitive. If the string srcStr is NULL or empty, the function returns NULL. For information about regular expressions, see Regular Expressions.

Operand Type:

Result Type:

BOOLEAN

Operand Type:

Result Type:

BOOLEAN

hamming(srcStr1, srcStr2 [, caseInsensitive])

Returns the Hamming distance between strings srcStr1 and srcStr2. The parameter caseInsensitive determines whether case sensitivity should be considered or not. By default, the function is case-insensitive. If both of the strings are NULL, then the result is 0. If just one of the strings is NULL, then the result is the length of the other string.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

indexOf(srcStr, subStr)

Returns the index within the string srcStr of the first occurrence of the specified substring subStr. If the substring is not found, the value null is returned.

The index of the first character is 0.

Operand Type:

Result Type:

INTEGER

indexOf(srcStr, subStr, fromIndex)

Returns the index within the string srcStr of the first occurrence of the specified substring subStr, starting at the index fromIndex. If the substring is not found, the value null is returned. If the value fromIndex exceeds the length of the string srcStr, the value null is returned. If the value fromIndex is less than 0, the start of the search is counted relative to the end of the string. However, if the counted start overlaps the string start, then the search starts at the beginning of the string srcStr instead.

The index of the first character is 0.

Operand Type:

Result Type:

INTEGER

isInFile(srcStr, fileName)

Searches for the string srcStr in a file defined by the parameter fileName. The parameter fileName must be a constant expression and must point to a dictionary with simple values. The function returns TRUE if srcStr is found in the dictionary, and FALSE otherwise. Before the search starts, the value of srcStr is trimmed (all whitespaces from the beginning and end of the string are removed), which may lead to a NULL value from the search.

Operand Type:

Result Type:

BOOLEAN

isNumber(srcStr)

Verifies whether the string srcStr represents a number. All characters of the string must be digits, except for the first character, which may be either a plus sign (+) or a minus sign (-). Decimal numbers are evaluated as non-numbers, that is, the period (.) and the comma (,) are illegal.

Operand Type:

STRING

Result Type:

BOOLEAN

lastIndexOf(srcStr, subStr)

Returns the index within the string srcStr of the last (rightmost) occurrence of the substring subStr.

The index of the first character is 0.

Operand Type:

Result Type:

INTEGER

lastIndexOf(srcStr, subStr, fromIndex)

Returns the index within the string srcStr of the last (rightmost) occurrence of the substring subStr, starting at the index fromIndex. If the substring is not found, the value null is returned. If the value fromIndex exceeds the length of the string srcStr, the value null is returned. If the value fromIndex is less than 0, the start of the search is counted relative to the end of the string. However, if the counted start overlaps the string start, then the search starts at the beginning of the string srcStr.

The index of the first character is 0.

Operand Type:

Result Type:

INTEGER

length(srcStr)

Returns the number of characters in the string srcStr.

Operand Type:

STRING

Result Type:

INTEGER

levenstein(srcStr1, srcStr2 [, caseInsensitive])

Returns the Levenstein distance between strings srcStr1 and srcStr2. The parameter caseInsensitive determines whether case sensitivity should be considered or not. By default, the function is case-insensitive. If both of the strings are NULL, then the result is 0. If just one of the strings is NULL, then the result is the length of the other string.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

lower(srcStr)

Transforms all characters of the string srcStr to lowercase.

Operand Type:

STRING

Result Type:

STRING

matches(srcRegex, srcStr [, caseInsensitive])

Verifies whether the string srcStr matches exactly the pattern of the regular expression srcRegex. The parameter caseInsensitive determines whether case sensitivity should be considered or not. By default, the function is case-sensitive. If the string srcStr is NULL or empty, the function returns NULL. For information about regular expressions, see Regular Expressions.

Operand Type:

Result Type:

Operand Type:

Result Type:

metaphone(srcStr)

Encodes srcStr to a metaphone string. It removes accents from the srcStr before evaluating the metaphone value. See the Metaphone article on Wikipedia, at http://www.wikipedia.org.

Operand Type:

STRING

Result Type:

STRING

removeAccents(srcStr)

Returns a copy of the string srcStr, in which all characters containing a diacritic are replaced by the corresponding characters without a diacritic.

Operand Type:

STRING

Result Type:

STRING

replace(srcStr, what, withWhat)

Replaces occurrences of the string what with the string withWhat in the string srcStr. Overlapping occurrences of the string what are replaced only once. For example,

replace("conoconoco", "conoco", "XXXX")

returns:

"XXXXnoco"

Operant Type:

Result Type:

STRING

replicate(srcStr, n)

Returns n copies of the string srcStr, concatenated without any separator. If n is less than or equal to 0, or srcStr = "", then the resulting value is null.

Operand Type:

Result Type:

STRING

sortWords(srcStr[, srcLocale[, srcSeparator[, srcDesc]]])

Returns a string that consists of sorted parts of the string srcStr. If the parameter srcLocale is set, then the sort is done for the given locale. The parameter srcSeparator specifies the separator of the string parts. If srcSeparator is missing or set to NULL or empty, the input string srcStr is parsed to separate characters, which are then sorted. If the Boolean parameter srcDesc is set to TRUE, reverse sort order is used. If the string srcStr is NULL or empty, the function returns NULL.

Operand Type:

Result Type:

STRING

Operand Type:

Result Type:

STRING

Operand Type:

Result Type:

Operand Type:

Result Type:

soundex(srcStr)

Returns the Soundex value of the srcStr parameter. It removes accents and non-ASCII characters from the srcStr before evaluating the Soundex value. See the Soundex article on Wikipedia at http://www. wikipedia.org.

Operand Type:

STRING

Result Type:

STRING

squeezeSpaces(srcStr)

Removes whitespace characters from both ends of the string srcStr and reduces multiple whitespace characters within the string. The only whitespace character is the space (' ') character.

Operand Type:

STRING

Result Type:

STRING

substituteAll(srcPattern, srcReplacement, srcStr [, caseInsensitiveFlag])

Replaces all occurrences of srcPattern in string srcStr with srcReplacement. If the parameter caseInsensitiveFlag is set to TRUE, then the search for srcPattern is case-insensitive. For information about regular expressions, see Regular Expressions.

Operand Type:

Result Type:

STRING

substituteMany(srcPattern, srcReplacement, srcStr, srcVolume [, caseInsensitiveFlag])

Replaces all occurrences of srcPattern in the string srcStr with srcReplacement. The maximum number of replacements is defined by the parameter srcVolume. If the total number of replacements in the string srcStr exceeds the srcVolume parameter, only the first srcVolume replacements will be applied. If the parameter caseInsensitiveFlag is set to TRUE, then the search for srcPattern is case-insensitive. For information about regular expressions, see Regular Expressions.

Operand Type:

Result Type:

STRING

substr(srcStr, beginIndex)

Returns a new string that is a substring of the string srcStr. The substring begins with the character at the index beginIndex and extends to the end of the string. If beginIndex is less than 0, then beginIndex is set to beginIndex + length(srcStr). If beginIndex is still less than 0, beginIndex is set to 0. An empty substring is returned as a null string.

The index of the first character is 0.

Operant Type:

Result Type:

STRING

substr(srcStr, beginIndex, strLen)

Returns a new string that is a substring of the string srcStr. The substring begins at the index beginIndex and extends to the character at index beginIndex + strLen - 1. If beginIndex is less than 0, then beginIndex is set to beginIndex + length(srcStr). If beginIndex is still less than 0, beginIndex is set to 0. If strLen is less than 0, strLen is set to 0. If strLen is greater than length(srcStr) - beginIndex, strLen is set to length(srcStr) - beginIndex. An empty substring is returned as a null string.

The index of the first character is 0.

Operand Type:

Result Type:

STRING

transliterate(srcStr, charsFrom, charsTo)

Transforms characters of the string srcStr. The transformation replaces all occurrences of any character named in the parameter charsFrom with the corresponding character defined in the parameter charsTo at their corresponding positions. For example,

transliterate("21d","123","abc")

evaluates to:

"bad"

Operant Type:

Result Type:

STRING

trashConsonants(srcStr)

Removes all consonants and their accented equivalents from the string srcStr. Other characters (digits, punctuation) remain unchanged.

Operant Type:

STRING

Result Type:

STRING

trashDiacritics

Caution: Obsolete function, replaced by the function removeAccents.

trashNonDigits(srcStr)

Returns a string that consists of only the digits included in the original string srcStr. All other characters are discarded.

Operand Type:

STRING

Result Type:

STRING

trashNonLetters(srcStr)

Returns a string that consists of only the letters included in the original string srcStr. All other characters are discarded.

Operand Type:

STRING

Result Type:

STRING

trashVowels(srcStr)

Removes all vowels and their accented equivalents from the string srcStr. Other characters (digits, punctuation) remain unchanged.

Operand Type:

STRING

Result Type:

STRING

trim(srcStr)

Removes whitespace characters from both ends of the string srcStr. Whitespace characters are \t, \n, \f, \r, and a space (' '). For more information, see the trim method of the class java.lang.String in the Java API documentation.

Operand Type:

STRING

Result Type:

STRING

upper(srcStr)

Transforms all characters of the string srcStr to uppercase.

Operand Type:

STRING

Result Type:

STRING

word(srcStr, srcIdx)

Returns the srcIdx-th word from the string srcStr. Words are defined as sequences of letters separated by a space (' ').

The index of the first word is 0.

Operand Type:

Result Type:

STRING

word(srcStr, srcIdx, srcSeparator)

Returns the srcIdx-th word from the string srcStr. Words are defined as sequences of letters separated by the first character of the string srcSeparator. If the string srcSeparator is NULL, then the space character (' ') is the separator.

The index of the first word is 0.

Operand Type:

Result Type:

STRING

wordCount(srcStr)

Returns the number of words in the string srcStr. Words are defined as sequences of letters separated by a space (' '). Beginning, ending, and multiple spaces are ignored.

Operand Type:

STRING

Result Type:

INTEGER

wordCount(srcStr, srcSeparator)

Returns the number of words in the string srcStr. Words are defined as sequences of letters separated by the first character of the string srcSeparator. Beginning, ending, and multiple spaces are ignored. If the string srcSeparator is NULL, then the space character is the separator.

Operand Type:

Result Type:

INTEGER

bitand(a, b)

Bitwise AND

Operand Type:

INTEGER INTEGER

Result Type

INTEGER

Operand Type:

LONG LONG

Result Type:

LONG

bitneg(a)

Bitwise NOT, or complement

Operand Type:

INTEGER

Result Type:

INTEGER

Operand Type:

LONG

Result Type:

LONG

bitor(a, b)

Bitwise inclusive OR

Operand Type:

INTEGER INTEGER

Result Type:

INTEGER

Operand Type:

LONG LONG

Result Type:

LONG

bitxor(a, b)

Bitwise exclusive OR

Operand Type:

INTEGER INTEGER

Result Type:

INTEGER

Operand Type:

LONG LONG

Result Type:

LONG

max(a, b)

Returns the greater of two operands. If either of the operands is NULL, NULL is returned. Strings are compared lexicographically. For Boolean values:

max(TRUE, ?) = TRUE

Operand Type:

Any two compatible types

Result Type:

Operand type

min(a, b)

Returns the lesser of two operands. If either of the operands is NULL, NULL is returned. Strings are compared lexicographically. For Boolean values:

min(FALSE, ?) = FALSE

Operand Type:

Any two compatible types

Result Type:

Operand type

safeMax(a, b)

Returns the greater of two operands. If either of the operands is NULL, then the value of the other operand is returned. Strings are compared lexicographically. For Boolean values:

safeMax(TRUE, ?) = TRUE

Operand Type:

Any two compatible types

Result Type:

Operand type

safeMin(a, b)

Returns the lesser of two operands. If either of the operands is NULL, then the value of the other operand is returned. Strings are compared lexicographically. For Boolean values:

safeMin(FALSE, ?) = FALSE

Operand:

Any two compatible types

Result:

Operand type

avg(expression)

Returns the average value of non-NULL values in a group, rounded to an integer number. For example, avg(2, null, 4) = 6/2 = 3.

Operand Type:

NUMBER

Result Type:

NUMBER

Operand Type:

DATE-TYPE

Result Type:

DATE-TYPE

concatenate(expression [, srcSeparator=" " [, srcLimit=1000]])

Returns a concatenated string made up of non-NULL values in a group, separated by the value in srcSeparator (optional). The resulting string never exceeds srcLimit (optional). Elements causing overflow are not added.

Operand Type:

Result Type:

STRING

Operand Type

Result Type:

STRING

Operand Type

Result Type:

STRING

Operand Type:

Result Type:

STRING

count()

Returns the number of all members of a group.

Result Type:

INTEGER

count(expression)

Returns the number of non-NULL values in a group. This is equivalent to the following conditional notation:

countif(expression is not null)

Operand Type:

Any type

Result Type:

INTEGER

countDistinct(expression)

Returns the number of distinct non-NULL values in a group.

Operand Type:

Any type

Result Type:

INTEGER

countUnique(expression)

Returns the number of non-NULL values in a group, which occurs only one time.

Operand Type:

Any type

Result Type:

INTEGER

first(expression)

Returns the first value in a group (including NULL values). This aggregation value depends on the order of group members, which is given by context.

Operand Type:

Any type

Result Type:

Operand type

last(expression)

Returns the last value in a group (including NULL values). This aggregation value depends on the order of group members, which is given by context.

Operand Type:

Any type

Result Type:

Operand type

maximum(expression)

Returns the maximum of non-NULL values in group.

Operand Type:

Any type

Result Type:

Operand type

minimum(expression)

Returns the minimum of non-NULL values in group.

Operand Type:

Any type

Result Type:

Operand type

modus(expression)

Returns the most frequent non-NULL value in a group. In case of more than one value with the same frequency, one of the matching values is chosen arbitrarily.

Operand Type:

Any type

Result Type:

Operand type

modus(expression-1, expression-2)

Returns the first non-NULL value of expression-2 members having the most frequent non-NULL value expression-1. In case of more than one value with the same frequency, one of the matching values is chosen arbitrarily.

Operand Type:

Any type

Result Type:

Second operand type

sum(expression)

Returns the sum of non-NULL values in a group. For Boolean arguments, this function performs the logical sum (OR). For example:

sum(true, true, false) = true

Operand Type:

NUMBER

Result Type:

NUMBER

Operand Type:

BOOLEAN

Result Type:

BOOLEAN

case(expr, exprValue[, expr, exprValue]...[, defaultExpr])

Returns the value of the expression exprValue immediately following the first expression expr whose value is TRUE. If none of the expressions expr are evaluated as TRUE, then defaultExpr is returned, if defaultExpr is specified. Otherwise, NULL is returned. The type of all values of exprValue must be the same.

decode(decodeExpr, expr, exprValue[, expr, exprValue]...[, defaultExpr])

Returns the value of the expression exprValue immediately following the first expression expr whose value is equal to decodeExpr. If none of the expressions expr are evaluated as TRUE, then defaultExpr is returned, if defaultExpr is specified. Otherwise, NULL is returned. The type of all values of exprValue must be the same. Additionally, all types of the value of exprValue must correspond to the type of the expression expr.

iif(ifExpr, trueExpr, elseExpr)

Returns trueExpr if ifExpr is TRUE. If ifExpr is FALSE or UNKNOWN, returns elseExpr.

nvl(expr[, expr]...)

Returns the value of the first expression expr whose value is not NULL. If no such value exists, then NULL is returned.

ceil(expr)

or

ceiling(expr)

Converts the expression expr to the nearest higher integer value.

Operand Type:

FLOAT

Result Type:

INTEGER

floor(expr)

Converts the expression expr to the nearest lower integer value.

Operand Type:

FLOAT

Result Type:

INTEGER

longCeil(expr)

or

longCeiling(expr)

Converts the expression expr to the nearest higher long value.

Operand Type:

FLOAT

Result Type:

LONG

longFloor(expr)

Converts the expression expr to the nearest lower long value.

Operand Type:

FLOAT

Result Type:

LONG

round(expr [, decimalPlaces=0])

Rounds the expression expr to a given number of decimal places, specified by decimalPlaces.

Operand Type:

FLOAT

Result Type:

FLOAT

Operand Type:

Result Type:

FLOAT

toDate(expr, dateFormat[, dateLocale])

Returns the date specified in the expression expr, converted to date type DAY. If the conversion is not successful, then NULL is returned. The expression expr is a STRING value, and its format is defined by the dateFormat parameter (of type STRING). The localization is defined by the dateLocale parameter (of type STRING). The dateFormat and dateLocale strings depend on the classes SimpleDateFormat and Locale.

Operand Type:

Result Type:

DAY

Operand Type:

Result Type:

DAY

toDateTime(expr, dateFormat[, dateLocale])

Returns the date specified in the expression expr, converted to date type DATETIME. If the conversion is not successful, then NULL is returned. The expression expr is a STRING value, and its format is defined by the dateFormat parameter (of type STRING). The localization is defined by the dateLocale parameter (of type STRING). The dateFormat and dateLocale strings depend on the classes SimpleDateFormat and Locale.

Operand Type:

Result Type:

DATETIME

Operand Type:

Result Type:

DATETIME

toFloat(expr)

Converts the expression expr to a FLOAT value. If the conversion is not successful, then NULL is returned.

Operand Type:

STRING

Result Type:

FLOAT

Operand Type:

INTEGER

Result Type:

FLOAT

Operand Type:

LONG

Result Type:

FLOAT

toInteger(expr)

Converts the expression expr to an INTEGER value. If the conversion is not successful, then NULL is returned.

Operand Type:

STRING

Result Type:

INTEGER

toLong(expr)

Converts the expression expr to a LONG value. If the conversion is not successful, then NULL is returned.

Operand Type:

STRING

Result Type:

INTEGER

Operand Type:

INTEGER

Result Type:

INTEGER

toString(expr, strFormat[, strLocale])

Converts the expression expr to a STRING value. If the conversion is not successful, then NULL is returned. The parameter strFormat is required for expressions of type DATETIME or DAY. When only the expr parameter is set, then the default Java convert method (toString) is used for the conversion. If the parameter strFormat is set, then it is used as the output format. If strLocale is not set, the default locale for the JVM instance is used. If the strFormat parameter (eventually strLocale) is set, then only expressions of type DATETIME, DAY, or INTEGER can be converted. Conversions for other types with parameter strFormat (eventually strLocale), are not defined. The strFormat and strLocale strings depend on the classes SimpleDateFormat and Locale.

Operand Type:

Operand Type:

Operand Type:

INTEGER

Operand Type:

Operand Type:

Operand Type:

Any type

Result Type for All Cases:

STRING

difference(set1, set2 [, separator] [, multiset] [, singularity])

Returns the cardinality of the difference of sets (set1 \ set2).

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

intersection(set1, set2 [, separator] [, multiset])

Returns the cardinality of the intersection of sets.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

symmetricDifference(set1, set2 [, separator] [, multiset ] [, singularity])

Returns the cardinality of the symmetric difference of sets.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

union(set1, set2 [, separator] [, multiset])

Returns the cardinality of the union of sets.

Operand Type:

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

random([[from,] to])

Generates a random number from the interval defined by the parameters from and to. The default values are:

random(0,1)

Result Type:

If you do not supply any operands, the result type is INTEGER.

Operand Type:

INTEGER

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER

sequence([start[, step]])

Generates the next number from a number sequence for each record. The start value is defined by start. The sequence step is set by the parameter step. The default values are:

sequence(0,1)

Result Type:

If you do not supply any operands, the result type is INTEGER.

Operand Type:

INTEGER

Result Type:

INTEGER

Operand Type:

Result Type:

INTEGER