Scalar Functions

Scalar functions are calculated for one single value.

Scalar functions can be used in WINDOW conditions, WINDOW from expressions, QUERY SELECT, and QUERY conditions.

The following aggregate functions are available:

Functions Returning Boolean

Function	Description
matches	matches( string, ‘regex pattern’) Slower performance!

Functions Returning Number

Function	Description
ceil	ceil(number) returns the number rounded up. ceil(3.4) returns 4.
date	date(string) converts string to epoc millis using the default date format in riodb.conf. date(string, format) returns epoch millis based on a given format. Example: date(‘03-aug-2003’,’dd-mon-yyyy’), as defined in SimpleDateFormat
date_add	date_add(my_timestamp, 5) adds 5 days to my_timestamp
date_addhours	date_addhours(my_timestamp, 5) adds 5 hours to my_timestamp
date_addmillis	date_addmillis(my_timestamp, 5) adds 5 milliseconds to my_timestamp
date_addmimutes	date_addmimutes(my_timestamp, 5) adds 5 minutes to my_timestamp
date_addseconds	date_addseconds(my_timestamp, 5050) adds 5 seconds to my_timestamp
decode_number	decode(http_response,200,1,400,1,0) SQL decode function. But for numbers.
floor	floor (number) returns the number rounded down. floor(3.4) returns 3.
greatest	greatest(number1, number2, …, number_n) returns the greatest number passed.
ifnull_number	ifnull(mynumber, 0) returns 0 if mynumber was null
instr	instr(string, substring) returns the position of the substring in string. -1 if not found.
least	least(number1, number2, …, number_n) returns the lowest number passed.
length	length(string) returns the number of characters in a string
round	round(number) rounds a number half-up. round(3.4) is 3. round(3.5) is 4.
sysdate	sysdate() returns the current date/time in epoch millis
to_number	to_number(string) converts the string representation of a number into a number datatype

Functions Returning String

Function	Description
concat	concat(string1, string2, …, string_n) returns a string made of concatenating the values passed.
decode	decode(my_string_field, ‘error’, ‘E’, ‘warning’, ‘W’, ‘info’, ‘I’, ‘O’). decode.
format	format(timestamp) converts numeric epoch millis into human-readable date string.
ifnull	ifnull(myfield, ‘nothing’) replaces a value with another when null.
lower	lower(‘HELLO’) returns ‘hello’
replace	replace(‘HELLO’,’ELLO’,’APPY’) returns ‘HAPPY’
substr	substr(‘BIG HOUSE’, 4) returns ‘HOUSE’
to_string	to_string(123.4) returns string datatype ‘123.4’
upper	upper(‘hello’) return ‘HELLO’

Example

May be used in any expression, including mathematical evaluations, or grouping with parenthesis () .

SELECT upper(symbol), concat(symbol,'-NYSE'), replace(ifnull(lower(symbol),'nada'),'k','c')

WHEN greatest(s.avg,s.median) * to_number(tax_rate) < 100

CREATE WINDOW last_1000_bids
RUNNING count, sum, avg
FROM NUMBER (floor(my_stock_stream.bid))
WHEN greatest(s.avg,s.median) * to_number(tax_rate) < 100