Scalar Functions

The CData Virtuality Server provides several types of built-in scalar functions for different operations. They are listed here in alphabetical order:

Aggregate functions which take sets of values from a group and return a single scalar value computed from the group
Array functions are used for different operations with arrays
Choice functions provide a way to select from two values
Date and time functions return or operate on dates, times, or timestamps
JSON functions provide functionality for working with JSON data
Numeric functions take numeric values or sometimes strings as input and return numeric values
Security functions provide the ability to interact with the security system
String functions generally take strings as inputs and return strings as outputs
System functions provide access to information in the CData Virtuality Server system via a query
Type conversion functions allow converting between data types within a query
Window functions which are similar to aggregate functions, but requires the use of an OVER clause or window specification.
XML functions provide functionality for working with XML data

The supported build-in functions and function parameters can be found in SYS.Functions and SYS.FunctionParams tables.

Function Determinism

A special feature of scalar functions in the CData Virtuality Server is that they can have different degrees of determinism; this is important because the function's determinism level dictates when the function is evaluated and to what extent the result can be cached. In the CData Virtuality Server, there are five levels of function determinism:

Level	Description	Functions
Deterministic	The function will always return the same result for the given inputs; Deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase; Some functions are not truly deterministic but are treated as such for performance	All except listed below
User Deterministic	The function will return the same result for the given inputs for the same user; User deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase; If a user deterministic function is evaluated during the creation of a prepared processing plan, then the resulting plan will be cached only for the user	`HASCALLERROLE` `CALLER`
Session Deterministic	The function will return the same result for the given inputs under the same user session; Session deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase; If a session deterministic function is evaluated during the creation of a prepared processing plan, then the resulting plan will be cached only for the user's session	`ENV`
Command Deterministic	The result of function evaluation is only deterministic within the scope of the user command; Command deterministic functions are delayed in evaluation until processing to ensure that even prepared plans utilizing these functions will be executed with relevant values; Command deterministic function evaluation will occur before pushdown - however, multiple occurrences of the same command deterministic time function are not guaranteed to evaluate to the same value	`CURDATE` `CURTIME` `NOW`
Nondeterministic	The result of function evaluation is fully nondeterministic; Nondeterministic functions are delayed in evaluation until processing with a preference for pushdown; If the function is not pushed down, then it may be evaluated for every row in its execution context (for example, if the function is used in the `SELECT` clause)	`RAND` `HASROLE` `USER`