The logical operators of AND, OR, NOT, and XOR are available.

The following comparison operators are available:

The following mathematical operators are supported:

The following are a few of the mathematical functions supported. A complete list of functions is available on the web site.

The following are a few of the string functions supported. A complete list of functions is available on the web site.

Pattern matching is supported using the LIKE operator.

For example, 'abcdef' LIKE 'ab%' is true. The '%' is used to match one or more characters, and '_' is used to match a single character.

Data type conversions are performed using the CAST(x,y) function. The CAST function takes any object as the first parameter and takes a string literal representation of the type to cast to as the second parameter. Note that the type must be put in single quotes as a string literal. Example:

CAST(45, 'VARCHAR') creates '45'

Possible type names are: 'VARCHAR', 'CHAR', 'INT', 'FLOAT', 'DOUBLE', 'DATE', 'TIMESTAMP', 'TIME'.

The following are a few of the date functions supported. A complete list is on the website.

The following aggregate functions are supported:

For queries on a single database, UnityJDBC parses functions and passes them directly to the database engine for execution. Thus, all functions that can be executed at the source are available. UnityJDBC and user-defined functions are used only when applying functions to data after it is extracted from the sources. UnityJDBC will parse queries containing functions that it itself cannot process in its internal database engine. These functions are passed down to the database engine and executed locally. Only functions that require inputs from more than one database are processed in the UnityJDBC database engine. All other functions are passed down to the sources.

UnityJDBC supports user-defined functions (UDFs). Adding your own user-defined function is easy. There are two types of functions: row functions and aggregate functions. A row function operates on one row at a time for its data and includes functions like SUBSTRING() and ABS(). An aggregate function is used in GROUP BY queries and aggregates an expression (usually a column) across multiple rows in a group to produce a single value. Examples include MAX() and COUNT().

To create a row function, you must create a Java class that extends the Function class. A template example is in the file F_Function_Template.java. This class must implement a constructor, an evaluate() method, and provide information on the parameters it requires. Once completed, as long as this function is available in the CLASSPATH, UnityJDBC will search for it when called. A similar template is available for aggregate functions, A_Aggregrate_Template.java. Sample code is provided in the directory unity/functions.

UnityJDBC has a database of known functions. This database contains information on what functions are supported on each data source. This is how UnityJDBC processes functions:

The UnityJDBC function database is encrypted and stored in the unityjdbc.jar. To add user-defined functions to the function database, create a mapping.xml file in the JRE classpath (execution directory, etc.) that stores the information on the function. An example is included in the release and more information is available on the web site.

UnityJDBC attempts to support most of the SQL standard. If there is a function or feature not supported, it is possible to use the NP() function to pass the query string directly to the data source by-passing UnityJDBC validation. This may be used to support non-standard functions or SQL syntax. Below are several examples.

Query: 
SELECT N1.n_nationkey, NP('OrderDB','n_name','varchar') 
FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1;

Result: (n_name is substituted directly into the query)
SELECT n_nationkey, n_name 
FROM Nation N1 WHERE N1.n_nationkey = 1

Query:
SELECT N1.n_nationkey,  NP('OrderDB','(select n_name from nation n2 
where N1.n_nationkey = N2.n_nationkey)','varchar') as name 
FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1

Result: 
SELECT N1.N_NATIONKEY, 
(select n_name from nation n2 where N1.n_nationkey = N2.n_nationkey) name
FROM NATION N1 WHERE N1.N_NATIONKEY = 1

Query:
SELECT N2.* 
FROM NP('OrderDB',
 '(select n_name,n_nationkey from nation)','n_name,n_nationkey') N1,
NP('PartDB',
 '(select n_name,n_nationkey from nation)','n_name,n_nationkey') as N2 
where N2.n_nationkey < 2 and N1.n_nationkey = N2.n_nationkey;

Result:
// Substitutes subquery for each of the two data sources (OrderDB and PartDB).  
// The result of the two subqueries is then joined at the UnityJDBC level.
// OrderDB: 
SELECT N2.n_name, N2.n_nationkey
FROM (select n_name,n_nationkey from nation) N2\n WHERE N2.n_nationkey < 2
// PartDB:
SELECT N1.n_nationkey FROM (select n_name,n_nationkey from nation) N1

More information on non-parsed functions is available on the web site.