Understanding Expression Builder Functions

What are Bessel Functions?

The mathematical cylinder functions for integers are known as Bessel functions.

The following Bessel functions are supported in this release:

Function Name	Description
`BesselI0(x)`	Returns the modified Bessel function of order 0 of the double argument as a double
`BesselI0_exp(x)`	Returns the exponentially scaled modified Bessel function of order 0 of the double argument as a double
`BesselI1(x)`	Returns the modified Bessel function of order 1 of the double argument as a double
`BesselI1_exp(x)`	Returns the exponentially scaled modified Bessel function of order 1 of the double argument as a double
`BesselJ(x,x)`	Returns the Bessel function of the first kind of order n of the argument as a double
`BesselK(x,x)`	Returns the modified Bessel function of the third kind of order n of the argument as a double
`BesselK0_exp(x)`	Returns the exponentially scaled modified Bessel function of the third kind of order 0 of the double argument as a double
`BesselK1_exp(x)`	Returns the exponentially scaled modified Bessel function of the third kind of order 1 of the double argument as a double
`BesselY(x)`	Returns the Bessel function of the second kind of order n of the double argument as a double

What are Conversion Functions?

The conversion functions help in converting values from one data type to other.

The following conversion functions are supported in this release:

Function Name	Description
`bigdecimal(value1)`	Converts the given value to bigdecimal
`boolean(value1)`	Converts the given value to logical
`date(value1,value2)`	Converts the given value to datetime
`double(value1)`	Converts the given value to double
`float(value1)`	Converts the given value to float
`int(value1)`	Converts the given value to integer
`long(value1)`	Converts the given value to long
`string(value1,value2)`	Converts the given value to string

boolean(value1)

Converts the input argument value to logical. The input argument can be one of the following data type: big integer or integer. Returned value type will be Boolean.

Examples

Function	Result
`boolean(5)`	TRUE
`boolean(0)`	FALSE
`boolean(NULL)`	TRUE
`boolean()`	TRUE
`boolean(-5)`	TRUE

double(value1)

Converts the input argument value to double. The input argument can be one of the following data types: integer, big integer, double, text or float. Returned value type will be double.

Examples

Function	Result
`double(3.1406)`	3.1405999660491943
`double(1234.56)`	1234.56005859375

float(value1)

Converts the input argument value to float. The input argument can be one of the following data types: integer, big integer, double, text or float. Returned value will be a single-precision floating-point number.

Examples

Function	Result
`float(1.67898989395)`	1.6789899
`float(1.796709289)`	1.7967093
`float(12.60508090750)`	12.605081

What are Date Functions?

The following date functions are supported in this release:

Function Name	Description
`day(date)`	Returns day of the date
`eventtimestamp()`	Returns event timestamp from stream
`hour(date)`	Returns hour of the date
`minute(date)`	Returns minute of the date
`month(date)`	Returns month of the date
`nanosecond(date)`	Returns nanosecond of the date
`second(date)`	Returns second of the date
`systimestamp()`	Returns the system’s timestamp on which the application is running
`timeformat(value1,value2)`	Returns the provided timestamp in required time format
`timestamp()`	Returns the current output time
`year(date)`	Returns year of the date

Acceptable Formats for Timestamp Values

This sections lists the acceptable formats for timestamp values in Oracle Stream Analytics.

Format	Example Values
`MM/dd/yyyy HH:mm:ss.SSSS`	3/21/2018 11:14:23.1111
`MM/dd/yyyy HH:mm:ss.SSS`	3/21/2018 11:14:23.111
`MM/dd/yyyy HH:mm:ss.SS`	3/21/2018 11:14:23.11
`MM/dd/yyyy HH:mm:ss.S`	3/21/2018 11:14:23.1
`MM/dd/yyyy HH:mm:ss`	3/21/2018 11:14:23
`MM/dd/yyyy HH:mm`	3/21/2018 11:14
`MM/dd/yyyy HH`	3/21/2018 11
`MM/dd/yyyy`	3/21/2018
`MM-dd-yyyy HH:mm:ss.SSSS`	11-21-2018 11:14:23.1111
`MM-dd-yyyy HH:mm:ss.SSS`	11-21-2018 11:14:23.111
`MM-dd-yyyy HH:mm:ss.SS`	11-21-2018 11:14:23.11
`MM-dd-yyyy HH:mm:ss.S`	11-21-2018 11:14:23.1
`MM-dd-yyyy HH:mm:ss`	11-21-2018 11:14:23
`MM-dd-yyyy HH:mm`	11-21-2018 11:14
`MM-dd-yyyy HH`	11-21-2018 11
`MM-dd-yyyy`	11-21-2018
`dd-MMM-yy hh.mm.ss.SSSSSS a`	11-Jan-18 11.14.23.111111 AM
`dd-MMM-yy hh.mm.ss.SSSS`	11-Jan-18 11.14.23.1111
`dd-MMM-yy hh.mm.ss.SSS`	11-Jan-18 11.14.23.111
`dd-MMM-yy hh.mm.ss.SS`	11-Jan-18 11.14.23.11
`dd-MMM-yy hh.mm.ss.S`	11-Jan-18 11.14.23.1
`dd-MMM-yy hh.mm.ss`	11-Jan-18 11.14.23
`dd-MMM-yy hh.mm`	11-Jan-18 11.14
`dd-MMM-yy hh`	11-Jan-18 11
`dd-MMM-yy`	11-Jan-18
`dd/MMM/yy`	15/MAR/18
`yyyy-MM-dd HH:mm:ss.SSSSSS`	2018-03-5 15:16:0.756000 +5:30, 2018-03-5 15:16:0.756000
`yyyy-MM-dd HH.mm:.ss.SSSSSS`	2018-03-5 15.16.0.756000 +5:30, 2018-03-5 15.16.0.756000
`yyyy-MM-dd HH:mm:ss`	2018-03-5 15:16:0; 2018-03-5 15:16:0 +5:30
`yyyy-MM-dd HH.mm.ss`	2018-03-5 15.16.0; 2018-03-5 15.16.0 +5:30
`yyyy-MM-dd HH:mm`	2018-03-5 15:16; 2018-03-5 15:16 +5:30
`yyyy-MM-dd HH.mm`	2018-03-5 15.16; 2018-03-5 15.16 +5:30
`yyyy-MM-dd HH`	2018-03-5 15
`yyyy-MM-dd`	2018-03-5
`HH:mm:ss`	11:14:14 PST
`yyyy-MM-dd'T'HH:mm:ss'.'SSS`	2018-03-04T12:08:56.235
`yyyy-MM-dd'T'HH:mm:ss'.'SSSZ`	2018-03-04T12:08:56.235-0700
`yyyy-MM-dd'T'HH:mm:ss'.'SSSz`	2018-03-04T12:08:56.235 PDT
`yyyy-MM-dd'T'HH:mm:ss`	2018-03-04T12:08:56
`yyyy-MM-dd'T'HH:mm:ssZ`	2018-03-04T12:08:56-0700
`yyyy-MM-dd'T'HH:mm:ssz`	2018-03-04T12:08:56 PDT

Day(date)

day(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the day of the week in the time represented by this date object. Returns a big integer indicating the day of the week represented by this date.

Examples

If Sunday=0, Monday=1 and so on, then:

Function	Result
`day(12/06/17 09:15:22 AM)`	3
`day(2017:11:23 11:20:25 PM)`	4

hour(date)

hour(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the hour in the time represented by this date object. Returns a big integer indicating the hour of the time represented by this date.

Examples

Function	Result
`hour(12/06/17 09:15:22 AM)`	09
`hour(2015:07:21 12:45:35 PM)`	12

minute(date)

minute(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the minutes in the time represented by this date object. Returns a big integer indicating the minutes of the time represented by this date.

Examples

Function	Result
`minute(12/06/17 09:15:22 AM)`	15
`minute(2015:07:21 12:45:35 PM)`	45

month(date)

month(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the month of the year that contains or begins with the instant in time represented by this date object. Returns a big integer indicating the month of the year represented by this date.

Examples

Function	Result
`month(12/06/17 09:15:22 AM)`	12
`month(2017:09:23 11:20:25 AM)`	9

second(date)

second(date) function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the seconds of the instant in time represented by this date object. Returns a big integer indicating the seconds of the time represented by this date.

Example

Function	Result
`second(12/06/17 09:15:22 AM)`	22
`second((2015:07:21 12:45:35 PM)`	35

Year(date)

year(date) function takes as an argument any one of the following data types: time interval or time stamp. The returned value represents the year of the instant in time represented by this date object. Returns a big integer indicating the year represented by this date.

Examples

Function	Result
`year(12/06/17 09:15:22 AM)`	17
`year(2015:07:21 12:45:35 PM)`	2015

What are Geometry Functions?

The Geometry functions allow you to convert the given values into a geometrical shape.

The following interval functions are supported in this release:

Function Name Description

Function Name	Description
`CreatePoint(lat,long,SRID)`	Returns a 2–dimensional point type geometry from the given latitude and longitude. The default SRID is 8307. The return value is of the datatype `sdo geometry`.
`distance(lat1,long1,lat2,long2,SRID)`	Returns distance between the first set of latitude, longitude and the second set of latitude, longitude values. The default SRID is 8307. The return value is of the datatype `double`.

CreatePoint(lat,long,SRID)

Returns a 2–dimensional point type geometry from the given latitude and longitude. The default SRID is 8307.

The return value is of the datatype sdo geometry.

distance(lat1,long1,lat2,long2,SRID)

Returns distance between the first set of latitude, longitude and the second set of latitude, longitude values. The default SRID is 8307.

The return value is of the datatype double.

What are Interval Functions?

The Interval functions help you in calculating time interval from given values.

The following interval functions are supported in this release:

Function Name Description

Function Name	Description
`numtodsinterval(n,interval_unit)`	Converts the given value to an `INTERVAL DAY TO SECOND` literal. The value of the `interval_unit` specifies the unit of `n` and must resolve to one of the string values: DAY, HOUR, MINUTE, or SECOND. The return value is of the datatype `interval`.
`to_dsinterval(string)`	Converts a string in format `DD HH:MM:SS` into a `INTERVAL DAY TO SECOND` data type. The `DD` indicates the number of days between 0 to 99. The `HH:MM:SS` indicates the number of hours, minutes and seconds in the interval from 0:0:0 to 23:59:59.999999. The seconds part can accept upto six decimal places. The return value is of the datatype `interval`.

numtodsinterval(n,interval_unit)

Converts the given value to an INTERVAL DAY TO SECOND literal. The value of the interval_unit specifies the unit of n and must resolve to one of the string values: DAY, HOUR, MINUTE, or SECOND.

The return value is of the datatype interval.

to_dsinterval(string)

Converts a string in format DD HH:MM:SS into a INTERVAL DAY TO SECOND data type. The DD indicates the number of days between 0 to 99. The HH:MM:SS indicates the number of hours, minutes and seconds in the interval from 0:0:0 to 23:59:59.999999. The seconds part can accept upto six decimal places.

The return value is of the datatype interval.

What are Math Functions?

The math functions allow you to perform various mathematical operations and calculations ranging from simple to complex.

The following math functions are supported in this release:

Function Name	Description
`IEEEremainder(value1,value2)`	Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard
`abs(value1)`	Returns the absolute value of a number
`acos(value1)`	Returns arc cosine of a value
`asin(value1)`	Returns arc sine of a value
`atan(value1)`	Returns arc tangent of a value
`atan2(arg1,arg2)`	Returns polar angle of a point (`arg2`, `arg1`)
`binomial(base,power)`	Returns binomial coefficient of the base raised to the specified power
`bitMaskWithBitsSetFromTo(x)`	BitMask with BitsSet (From, To)
`cbrt(value1)`	Returns cubic root of the specified value
`ceil(value1)`	Rounds to ceiling
`copySign(value1,value2)`	Returns the first floating-point argument with the sign of the second floating-point argument
`cos(value1)`	Returns cosine of a value
`cosh(value1)`	Returns cosine hyperbolic of a value
`exp(x)`	Returns exponent of a value
`expm1(x)`	More precise equivalent of `exp(x)`; Returns 1 when x is around zero
`factorial(value1)`	Returns factorial of a natural number
`floor(value1)`	Rounds to floor
`getExponent(value1)`	Returns the unbiased exponent used in the representation of a double
`getSeedAtRowColumn(value1,value2)`	Returns a deterministic seed as an integer from a (seemingly gigantic) matrix of predefined seeds
`hash(value1)`	Returns an integer hashcode for the specified double value
`hypot(value1,value2)`	Returns square root of sum of squares of the two arguments
`leastSignificantBit(value1)`	Returns the least significant 64 bits of this UUID's 128 bit value
`log(value1,value2)`	Calculates the log value of the given argument to the given base, where `value 1` is the value and `value 2` is the base
`log1(value1)`	Returns the natural logarithm of a number
`log10(value1)`	Calculates the log value of the given argument to base 10
`log2(value1)`	Calculates the log value of the given argument to base 2
`logFactorial(value1)`	Returns the natural logarithm (base e) of the factorial of its integer argument as a double
`longFactorial(value1)`	Returns the factorial of its integer argument (in the range k >= 0 && k < 21) as a long
`maximum(value1,value2)`	Returns the maximum of 2 arguments
`minimum(value1,value2)`	Returns the minimum of 2 arguments
`mod(value1,value2)`	Returns modulo of a number
`mosttSignificantBit(value1)`	Returns the most significant 64 bits of this UUID's 128 bit value
`nextAfter(value1,value2)`	Returns the floating-point number adjacent to the first argument in the direction of the second argument
`nextDown(value1)`	Returns the floating-point value adjacent to the input argument in the direction of negative infinity
`nextUp(value1)`	Returns the floating-point value adjacent to the input argument in the direction of positive infinity
`Pow(m,n)`	Returns m raised to the nth power
`rint(value1)`	Returns the double value that is closest in value to the argument and is equal to a mathematical integer
`round(value1)`	Rounds to the nearest integral value
`Scalb(d,scaleFactor)`	Returns d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set
`signum(value1)`	Returns signum of an argument as a double value
`sin(value1)`	Returns sine of a value
`sinh(value1)`	Returns sine hyperbolic of a value
`sqrt(value1)`	Returns square root of a value
`stirlingCorrection(value1)`	Returns the correction term of the Stirling approximation of the natural logarithm (base e) of the factorial of the integer argument as a double
`tan(value1)`	Returns tangent of a value
`tanh(value1)`	Returns tangent hyperbolic of a value
`toDegrees(value1)`	Converts the argument value to degrees
`toRadians(value1)`	Returns the measurement of the angle in radians
`ulp(value1)`	Returns the size of an ulp of the argument

maximum(value1, value2)

Returns the maximum of two arguments. The first argument is a value to compare with the second argument’s value and can be any one of the following data type: big integer, double, interval, integer, float. The second argument is a value to compare with the first argument’s value and can be any one of the following data type: big integer, double, interval, integer, float.

Examples

Function	Result
`maximum(1999220,1997220)`	1999220
`maximum(135.45, 135.50)`	135.50

Note:

If the user provides two different data types as input arguments, then Stream Analytics does implicit conversion to convert one of the argument to the other argument’s type.

minimum(value1, value2)

Returns the minimum of two arguments. The first argument is a value to compare with the second argument’s value and can be any one of the following data type: big integer, double, interval, integer, float. The second argument is a value to compare with the first argument’s value and can be any one of the following data type: big integer, double, interval, integer, float.

Examples

Function	Result
`minimum(16324, 16321)`	16321
`minimum(3.16, 3.10)`	3.10

Note:

If the user provides two different data types as arguments, then Stream Analytics does implicit conversion to convert one argument to the other argument’s type.

round(value1)

Rounds the argument value to the nearest integer value. The input argument can be of the following data types: big integer, double, integer, float.

Examples

Function	Result
`round(7.16)`	7
`round(38.941)`	39
`round(3.5)`	4

toDegrees(value1)

Converts the argument value to degrees. The input argument is an angle in radians and can be of type double. The returned value will be the measurement of the angle in degrees and is of type double.

Examples

Function	Result
`toDegrees(3.14)`	180.0
`toDegrees(0.785)`	45.0

toRadians(value1)

Converts the argument value to radians. The input argument is an angle in degrees and can be of type double. The returned value will be the measurement of the angle in radians and is of type double.

Examples

Function	Result
`toRadians(180.0)`	3.14
`toRadians(45.0)`	0.785

What are Null-related Functions?

The following null-related functions are supported in this release:

Function Name	Description
`nvl(value1,value2)`	Replaces null with a value of the same type

nvl(value1, value2)

nvl lets you replace null (returned as a blank) with a value of the same type as the first argument. For example, in a list of employees and commission, you can substitute Not Applicable if the employee receives no commission using the nvl(value1,value2) function as nvl(Not Applicable,Commission).

Example

Function	Result
`nvl(Not Applicable,Commission)`	Not Applicable

What are Statistical Functions?

Statistical functions help you in calculating the statistics of different values.

The following statistical functions are supported in this release:

Function Name	Description
`beta1(value1,value2,value3)`	Returns the area from zero to `value3` under the beta density function
`betaComplemented(value1,value2,value3)`	Returns the area under the right hand tail (from `value3` to infinity) of the beta density function
`binomial2(value1,value2,value3)`	Returns the sum of the terms 0 through `value1` of the Binomial probability density. All arguments must be positive.
`binomialComplemented(value1,value2,value3)`	Returns the sum of the terms `value1+1` through `value2` of the binomial probability density. All arguments must be positive.
`chiSquare(value1,value2)`	Returns the area under the left hand tail (from 0 to `value2`) of the chi square probability density function with `value1` degrees of freedom. The arguments must both be positive.
`chiSquareComplemented(value1,value2)`	Returns the area under the right hand tail (from `value2` to infinity) of the chi square probability density function with `value1` degrees of freedom. The arguments must both be positive.
`errorFunction(value1)`	Returns the error function of the normal distribution
`errorFunctionComplemented(value1)`	Returns the complementary error function of the normal distribution
`gamma(value1,value2,value3)`	Returns the gamma function of the arguments
`gammaComplemented(value1,value2,value3)`	Returns the integral from `value3` to infinity of the gamma probability density function
`incompleteBeta(value1,value2,value3)`	Returns the incomplete beta function evaluated from zero to `value3`
`incompleteGamma(value1,value2)`	Returns the incomplete gamma function
`incompleteGammaComplement(value1,value2)`	Returns the complemented incomplete gamma function
`logGamma(value1)`	Returns the natural logarithm of the gamma function
`negativeBinomial(value1,value2,value3)`	Returns the sum of the terms 0 through `value1` of the negative binomial distribution. All arguments must be positive.
`negativeBinomialComplemented(value1,value2,value3)`	Returns the sum of the terms `value1+1` to infinity of the negative binomial distribution. All arguments must be positive.
`normal(value1,value2,value3)`	Returns the area under the normal (Gaussian) probability density function, integrated from minus infinity to `value1` (assumes mean is zero, variance is one)
`normalInverse(value1)`	Returns the value for which the area under the normal (Gaussian) probability density function is equal to the argument `value1` (assumes mean is zero, variance is one)
`poisson(value1,value2)`	Returns the sum of the first `value1` terms of the Poisson distribution. The arguments must both be positive.
`poissonComplemented(value1,value2)`	Returns the sum of the terms `value1+1` to infinity of the poisson distribution
`studentT(value1,value2)`	Returns the integral from minus infinity to `value2` of the Student-t distribution with `value1` > 0 degrees of freedom
`studentTInverse(value1,value2)`	Returns the value, for which the area under the Student-t probability density function is equal to `1-value1/2`. The function uses the studentT function to determine the return value iteratively.

What are String Functions?

The following String functions are supported in this release:

Function Name	Description
`coalesce(value1,...)`	Returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null
`concat(value1,...)`	Returns concatenation of values converted to strings
`indexof(string,match)`	Returns first index of `\'match\'` in `\'string\'`or 1 if not found
`initcap(value1)`	Returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase
`length(value1)`	Returns the length of the specified string
`like(value1,value2)`	Returns a matching pattern
`lower(value1)`	Converts the given string to lower case
`lpad(value1,value2,value3)`	Pads the left side of a string with a specific set of characters (when `string1` is not null)
`ltrim(value1,value2)`	Removes all specified characters from the left hand side of a string
`replace(string,match,replacement)`	Replaces all `\'match\'` with `\'replacement\'` in `\'string\'`
`rpad(value1,value2,value3)`	Pads the right side of a string with a specific set of characters (when `string1` is not null)
`rtrim(value1,value2)`	Removes all specified characters from the right hand side of a string
`substr(string,from)`	Returns substring of a 'string' when indices are between 'from' (inclusive) and up to the end of the string
`substring(string,from,to)`	Returns substring of a \'string\' when indices are between \'from\' (inclusive) and \'to\' (exclusive)
`translate(value1,value2,value3)`	Replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.
`upper(value1)`	Converts given string to uppercase

coalesce(value1,... )

coalesce returns the first non-null expression in the list of expressions. You must specify at least two expressions. If all expressions evaluate to null then the coalesce function will return null.

For example:

In coalesce(expr1,expr2):

If expr1 is not null then the function returns expr1.
If expr1 is null then the function returns expr2.
If expr1 and expr2 are null then the function returns null.

In coalesce(expr1,expr2,......,exprn)

If expr1 is not null then the function returns expr1.
If expr1 is null then the function returns expr2.
If expr1 and expr2 are null then the function returns the next non-null expression.

length(value1)

Returns the length in characters of the string passed as an input argument. The input argument is of the data type text. The returned value is an integer representing the total length of the string.

If value1 is null, then length(value1) returns null.

If value1 is an empty string, then length(value1) returns null.

Examples

Function	Result
`length(“one”)`	3
`length()`	ERROR: Function has invalid parameters.
`length(“john”)`	4
`length(” “)`	NULL
`length(null)`	NULL
`length(“firstname.lastname@example.com”)`	30

lower(value1)

Converts a string to all lower-case characters. The input argument is of the data type text. The returned value is the lowercase of the specified string.

Examples

Function	Result
`lower(“PRODUCT”)`	product
`lower(“ABCdef”)`	abcdef
`lower(“abc”)`	abc

replace(string, match, replacement)

Replaces all match characters in a string with replacement characters. The first input argument is the string and is of the data type text. The second argument is the match and is of the data type text. The third argument is replacement and is of data type text. The returned value is a text in which the third string argument (replacement) replaces the second string argument (match).

If match is not found in the string, then the original string will be returned.

Examples

Function	Result
`replace(“aabbccdd”,”cc”,”ff”)`	aabbffdd
`replace(“aabbcccdd”,”cc”,”ff”)`	aabbffcdd
`replace(“aabbddee”,”cc”,”ff”)`	aabbddee

substring(string, from, to)

Returns a substring of a string when indices are between from (inclusive) and to (exclusive). The first input argument is the string and is of the data type text. The second argument is the start index and is an integer. The third argument is the finish index and is an integer. The returned value is a substring and is of type text.

Examples

Function	Result
`substring(“abcdefgh”,3,7)`	cdef
`substring(“abcdefgh”,1,6)`	abcde

upper(value1)

Converts a string to all upper-case characters. The input argument is of the data type text. The returned value is the uppercase of the specified string.

Examples

Function	Result
`upper(“name”)`	NAME
`upper(“abcdEFGH”)`	ABCDEFGH
`upper(“ABCD”)`	ABCD