JBoss.orgCommunity Documentation
Teiid provides an extensive set of built-in scalar functions. See also SQL Support and Datatypes . In addition, Teiid provides the capability for user defined functions or UDFs .
Numeric functions return numeric values (integer, long, float, double, biginteger, bigdecimal). They generally take numeric values as inputs, though some take strings.
Function |
Definition |
Datatype Constraint |
---|---|---|
+ - * / |
Standard numeric operators |
x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x |
ABS(x) |
Absolute value of x |
See standard numeric operators above |
ACOS(x) |
Arc cosine of x |
x in {double}, return type is double |
ASIN(x) |
Arc sine of x |
x in {double}, return type is double |
ATAN(x) |
Arc tangent of x |
x in {double}, return type is double |
ATAN2(x,y) |
Arc tangent of x and y |
x, y in {double}, return type is double |
CEILING(x) |
Ceiling of x |
x in {double, float}, return type is double |
COS(x) |
Cosine of x |
x in {double}, return type is double |
COT(x) |
Cotangent of x |
x in {double}, return type is double |
DEGREES(x) |
Convert x degrees to radians |
x in {double}, return type is double |
EXP(x) |
e^x |
x in {double, float}, return type is double |
FLOOR(x) |
Floor of x |
x in {double, float}, return type is double |
FORMATBIGDECIMAL(x, y) |
Formats x using format y |
x is bigdecimal, y is string, returns string |
FORMATBIGINTEGER(x, y) |
Formats x using format y |
x is biginteger, y is string, returns string |
FORMATDOUBLE(x, y) |
Formats x using format y |
x is double, y is string, returns string |
FORMATFLOAT(x, y) |
Formats x using format y |
x is float, y is string, returns string |
FORMATINTEGER(x, y) |
Formats x using format y |
x is integer, y is string, returns string |
FORMATLONG(x, y) |
Formats x using format y |
x is long, y is string, returns string |
LOG(x) |
Natural log of x (base e) |
x in {double, float}, return type is double |
LOG10(x) |
Log of x (base 10) |
x in {double, float}, return type is double |
MOD(x, y) |
Modulus (remainder of x / y) |
x in {integer, long, float, double, biginteger}, return type is same as x |
PARSEBIGDECIMAL(x, y) |
Parses x using format y |
x, y are strings, returns bigdecimal |
PARSEBIGINTEGER(x, y) |
Parses x using format y |
x, y are strings, returns biginteger |
PARSEDOUBLE(x, y) |
Parses x using format y |
x, y are strings, returns double |
PARSEFLOAT(x, y) |
Parses x using format y |
x, y are strings, returns float |
PARSEINTEGER(x, y) |
Parses x using format y |
x, y are strings, returns integer |
PARSELONG(x, y) |
Parses x using format y |
x, y are strings, returns long |
PI() |
Value of Pi |
return is double |
POWER(x,y) |
x to the y power |
x in {integer, long, float, double, biginteger}, if x is biginteger then return type is biginteger, else double |
RADIANS(x) |
Convert x radians to degrees |
x in {double}, return type is double |
RAND() |
Returns a random number, using generator established so far in the query or initializing with system clock if necessary. |
Returns double. |
RAND(x) |
Returns a random number, using new generator seeded with x. |
x is integer, returns double. |
ROUND(x,y) |
Round x to y places; negative values of y indicate places to the left of the decimal point |
x in {integer, float, double, bigdecimal} y is integer, return is same type as x |
SIGN(x) |
1 if x > 0, 0 if x = 0, -1 if x < 0 |
x in {integer, long, float, double, biginteger, bigdecimal}, return type is integer |
SIN(x) |
Sine value of x |
x in {double}, return type is double |
SQRT(x) |
Square root of x |
x in {double, float}, return type is double |
TAN(x) |
Tangent of x |
x in {double}, return type is double |
BITAND(x, y) |
Bitwise AND of x and y |
x, y in {integer}, return type is integer |
BITOR(x, y) |
Bitwise OR of x and y |
x, y in {integer}, return type is integer |
BITXOR(x, y) |
Bitwise XOR of x and y |
x, y in {integer}, return type is integer |
BITNOT(x) |
Bitwise NOT of x |
x in {integer}, return type is integer |
Teiid offers a set of functions you can use to parse numbers from strings. For each string, you need to provide the formatting of the string. These functions use the convention established by the java.text.DecimalFormat class to define the formats you can use with these functions. You can learn more about how this class defines numeric string formats by visiting the Sun Java Web site at the following URL for Sun Java .
For example, you could use these function calls, with the formatting string that adheres to the java.text.DecimalFormat convention, to parse strings and return the datatype you need:
Input String |
Function Call to Format String |
Output Value |
Output Datatype |
---|---|---|---|
'$25.30' |
parseDouble(cost, '$#,##0.00;($#,##0.00)') |
25.3 |
double |
'25%' |
parseFloat(percent, '#,##0%') |
25 |
float |
'2,534.1' |
parseFloat(total, '#,##0.###;-#,##0.###') |
2534.1 |
float |
'1.234E3' |
parseLong(amt, '0.###E0') |
1234 |
long |
'1,234,567' |
parseInteger(total, '#,##0;-#,##0') |
1234567 |
integer |
Teiid offers a set of functions you can use to convert numeric datatypes into strings. For each string, you need to provide the formatting. These functions use the convention established within the java.text.DecimalFormat class to define the formats you can use with these functions. You can learn more about how this class defines numeric string formats by visiting the Sun Java Web site at the following URL for Sun Java .
For example, you could use these function calls, with the formatting string that adheres to the java.text.DecimalFormat convention, to format the numeric datatypes into strings:
Input Value |
Input Datatype |
Function Call to Format String |
Output String |
---|---|---|---|
25.3 |
double |
formatDouble(cost, '$#,##0.00;($#,##0.00)') |
'$25.30' |
25 |
float |
formatFloat(percent, '#,##0%') |
'25%' |
2534.1 |
float |
formatFloat(total, '#,##0.###;-#,##0.###') |
'2,534.1' |
1234 |
long |
formatLong(amt, '0.###E0') |
'1.234E3' |
1234567 |
integer |
formatInteger(total, '#,##0;-#,##0') |
'1,234,567' |
String functions generally take strings as inputs and return strings as outputs.
Unless specified, all of the arguments and return types in the following table are strings and all indexes are 1-based. The 0 index is considered to be before the start of the string.
Function |
Definition |
Datatype Constraint |
---|---|---|
x || y |
Concatenation operator |
x,y in {string}, return type is string |
ASCII(x) |
Provide ASCII value of character x |
return type is integer |
CHR(x) CHAR(x) |
Provide the character for ASCII value x |
x in {integer} |
CONCAT(x, y) |
Concatenates x and y with ANSI semantics. If x and/or y is null, returns null. |
x, y, is string |
CONCAT2(x, y) |
Concatenates x and y with non-ANSI null semantics. If x and y is null, returns null. If only x or y is null, returns the other value. |
x, y, is string |
INITCAP(x) |
Make first letter of each word in string x capital and all others lowercase |
x in {string} |
INSERT(str1, start, length, str2) |
Insert string2 into string1 |
str1 in {string}, start in {integer}, length in {integer}, str2 in {string} |
LCASE(x) |
Lowercase of x |
x in {string} |
LEFT(x, y) |
Get left y characters of x |
x in {string}, y in {string}, return string |
LENGTH(x) |
Length of x |
return type is integer |
LOCATE(x, y) |
Find position of x in y starting at beginning of y |
x in {string}, y in {string}, return integer |
LOCATE(x, y, z) |
Find position of x in y starting at z |
x in {string}, y in {string}, z in {integer}, return integer |
LPAD(x, y) |
Pad input string x with spaces on the left to the length of y |
x in {string}, y in {integer}, return string |
LPAD(x, y, z) |
Pad input string x on the left to the length of y using character z |
x in {string}, y in {string}, z in {character}, return string |
LTRIM(x) |
Left trim x of white space |
x in {string}, return string |
REPEAT(str1,instances) |
Repeat string1 a specified number of times |
str1 in {string}, instances in {integer} return string |
REPLACE(x, y, z) |
Replace all y in x with z |
x,y,z in {string}, return string |
RIGHT(x, y) |
Get right y characters of x |
x in {string}, y in {string}, return string |
RPAD(input string x, pad length y) |
Pad input string x with spaces on the right to the length of y |
x in {string}, y in {integer}, return string |
RPAD(x, y, z) |
Pad input string x on the right to the length of y using character z |
x in {string}, y in {string}, z in {character}, return string |
RTRIM(x) |
Right trim x of white space |
x is string, return string |
SUBSTRING(x, y) |
Get substring from x, from position y to the end of x |
y in {integer} |
SUBSTRING(x, y, z) |
Get substring from x from position y with length z |
y, z in {integer} |
TRANSLATE(x, y, z) |
Translate string x by replacing each character in y with the character in z at the same position |
x in {string} |
UCASE(x) |
Uppercase of x |
x in {string} |
Date and time functions return dates, times, or timestamps.
Function |
Definition |
Datatype Constraint |
---|---|---|
CURDATE() |
Return current date |
returns date |
CURTIME() |
Return current time |
returns time |
NOW() |
Return current timestamp (date and time) |
returns timestamp |
DAYNAME(x) |
Return name of day |
x in {date, timestamp}, returns string |
DAYOFMONTH(x) |
Return day of month |
x in {date, timestamp}, returns integer |
DAYOFWEEK(x) |
Return day of week (Sunday=1) |
x in {date, timestamp}, returns integer |
DAYOFYEAR(x) |
Return Julian day number |
x in {date, timestamp}, returns integer |
FORMATDATE(x, y) |
Format date x using format y |
x is date, y is string, returns string |
FORMATTIME(x, y) |
Format time x using format y |
x is time, y is string, returns string |
FORMATTIMESTAMP(x, y) |
Format timestamp x using format y |
x is timestamp, y is string, returns string |
HOUR(x) |
Return hour (in military 24-hour format) |
x in {time, timestamp}, returns integer |
MINUTE(x) |
Return minute |
x in {time, timestamp}, returns integer |
MONTH(x) |
Return month |
x in {date, timestamp}, returns integer |
MONTHNAME(x) |
Return name of month |
x in {date, timestamp}, returns string |
QUARTER(x) |
Return quarter |
x in {date, timestamp}, returns integer |
PARSEDATE(x, y) |
Parse date from x using format y |
x, y in {string}, returns date |
PARSETIME(x, y) |
Parse time from x using format y |
x, y in {string}, returns time |
PARSETIMESTAMP(x,y) |
Parse timestamp from x using format y |
x, y in {string}, returns timestamp |
SECOND(x) |
Return seconds |
x in {time, timestamp}, returns integer |
TIMESTAMPCREATE(date, time) |
Create a timestamp from a date and time |
date in {date}, time in {time}, returns timestamp |
TIMESTAMPADD(interval, count, timestamp) |
Add a specified interval (hour, day of week, month) to the timestamp, where intervals can have the following definition:
|
The interval constant may be specified either as a string literal or a constant value. Interval in {string}, count in {integer}, timestamp in {date, time, timestamp} |
TIMESTAMPDIFF(interval, startTime, endTime) |
Calculate the approximate number of whole intervals in (endTime - startTime) using a specific interval type (as defined by the constants in TIMESTAMPADD). If (endTime > startTime), a positive number will be returned. If (endTime < startTime), a negative number will be returned. Calculations are approximate and may be less accurate over longer time spans. |
Interval in {string}; startTime, endTime in {date, time, timestamp}, returns a long. |
WEEK(x) |
Return week in year |
x in {date, timestamp}, returns integer |
YEAR(x) |
Return four-digit year |
x in {date, timestamp}, returns integer |
MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone) |
Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. i.e. if the server is in GMT-6, then modifytimezone({ts '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8. |
startTimeZone and endTimeZone are strings, returns a timestamp |
MODIFYTIMEZONE (timestamp, endTimeZone) |
Return a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the startTimeZone is the same as the server process. |
Timestamp is a timestamp; endTimeZone is a string, returns a timestamp |
FROM_UNIXTIME (unix_timestamp) |
Return the Unix timestamp (in seconds) as a Timestamp value |
Unix timestamp (in seconds) |
Teiid does not implicitly convert strings that contain dates presented in different formats, such as ‘19970101’ and ‘31/1/1996’ to date-related datatypes. You can, however, use the parseDate, parseTime, and parseTimestamp functions, described in the next section, to explicitly convert strings with a different format to the appropriate datatype. These functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines date and time string formats by visiting the Sun Java Web site .
For example, you could use these function calls, with the formatting string that adheres to the java.text.SimpleDateFormat convention, to parse strings and return the datatype you need:
String |
Function Call To Parse String |
---|---|
'1997010' |
parseDate(myDateString, 'yyyyMMdd') |
'31/1/1996' |
parseDate(myDateString, 'dd''/''MM''/''yyyy') |
'22:08:56 CST' |
parseTime (myTime, 'HH:mm:ss z') |
'03.24.2003 at 06:14:32' |
parseTimestamp(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss') |
Time zones can be specified in several formats. Common abbreviations such as EST for "Eastern Standard Time" are allowed but discouraged, as they can be ambiguous. Unambiguous time zones are defined in the form continent or ocean/largest city. For example, America/New_York, America/Buenos_Aires, or Europe/London. Additionally, you can specify a custom time zone by GMT offset: GMT[+/-]HH:MM.
For example: GMT-05:00
Within your queries, you can convert between datatypes using the CONVERT or CAST keyword. See also Data Type Conversions .
Function |
Definition |
---|---|
CONVERT(x, type) |
Convert x to type, where type is a Teiid Base Type |
CAST(x AS type) |
Convert x to type, where type is a Teiid Base Type |
These functions are identical other than syntax; CAST is the standard SQL syntax, CONVERT is the standard JDBC/ODBC syntax.
Choice functions provide a way to select from two values based on some characteristic of one of the values.
Function |
Definition |
Datatype Constraint |
IFNULL(x,y) |
If x is null, return y; else return x |
x, y, and the return type must be the same type but can be any type |
NVL(x,y) |
If x is null, return y; else return x |
x, y, and the return type must be the same type but can be any type |
NULLIF(param1, param2) |
Equivalent to case when (param1 = param2) then null else param1 |
param1 and param2 must be compatable comparable types |
COALESCE(x,y+) |
Returns the first non-null parameter |
x and all y's can be any compatible types |
IFNULL and NVL are aliases of each other. They are the same function.
Decode functions allow you to have the Teiid Server examine the contents of a column in a result set and alter, or decode, the value so that your application can better use the results.
Function |
Definition |
Datatype Constraint |
---|---|---|
DECODESTRING(x, y) |
Decode column x using string of value pairs y and return the decoded column as a string |
all string |
DECODESTRING(x, y, z) |
Decode column x using string of value pairs y with delimiter z and return the decoded column as a string |
all string |
DECODEINTEGER(x, y) |
Decode column x using string of value pairs y and return the decoded column as an integer |
all string parameters, return integer |
DECODEINTEGER(x,y,z) |
Decode column x using string of value pairs y with delimiter z and return the decoded column as an integer |
all string parameters, return integer |
Within each function call, you include the following arguments:
x is the input value for the decode operation. This will generally be a column name.
y is the literal string that contains a delimited set of input values and output values.
z is an optional parameter on these methods that allows you to specify what delimiter the string specified in y uses.
For example, your application might query a table called PARTS that contains a column called IS_IN_STOCK which contains a Boolean value that you need to change into an integer for your application to process. In this case, you can use the DECODEINTEGER function to change the Boolean values to integers:
SELECT DECODEINTEGER(IS_IN_STOCK, 'false, 0, true, 1') FROM PartsSupplier.PARTS;
When the Teiid System encounters the value false in the result set, it replaces the value with 0.
If, instead of using integers, your application requires string values, you can use the DECODESTRING function to return the string values you need:
SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS;
In addition to two input/output value pairs, this sample query provides a value to use if the column does not contain any of the preceding input values. If the row in the IS_IN_STOCK column does not contain true or false, the Teiid Server inserts a null into the result set.
When you use these DECODE functions, you can provide as many input/output value pairs if you want within the string. By default, the Teiid System expects a comma delimiter, but you can add a third parameter to the function call to specify a different delimiter:
SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',’:’) FROM PartsSupplier.PARTS;
You can use keyword null in the DECODE string as either an input value or an output value to represent a null value. However, if you need to use the literal string null as an input or output value (which means the word null appears in the column and not a null value) you can put the word in quotes: "null".
SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS;
If the DECODE function does not find a matching output value in the column and you have not specified a default value, the DECODE function will return the original value the Teiid Server found in that column.
The Lookup function allows you to cache a group’s data in memory and access it through a scalar function. This caching accelerates response time to queries that use the lookup groups, known in business terminology as lookup tables or code groups.
A StatePostalCodes group used to translate postal codes to complete state names might represent an example of this type of lookup group. One element, PostalCode, represents a key element. Other groups within the model refer to this two-letter code. A second element, StateDisplayName, would represent the complete name of the state. Hence, a query to this lookup group would typically provide the PostalCode and expect the StateDisplayName in response.
When you call this function for any combination of codeGroup, returnElement, and keyElement for the first time, the Teiid System caches the result. The Teiid System uses this cached map for all queries, in all sessions, that later access this lookup group. The codeGroup requires use of the fully-qualified name, and the returnElement and keyElement parameters should use shortened column names.
Because the Teiid System caches and indexes this information in memory, this function provides quick access after the Teiid System initially caches the lookup group. The Teiid System unloads these cached lookup groups when you stop and restart the Teiid System. Thus, you should not use this function for data that is subject to updates. Instead, you can use it against static data that does not change over time.
The keyElement column is expected to contain unique key values. If the column contains duplicate values, only the last loaded value will be used for lookup purposes. In some cases, this may cause unexpected results, so it is strongly recommended that only columns without duplicate values be used as the keyElement. The lookup caches can be flushed via the svcmgr.
Cached lookup groups might consume significant memory. You can limit the number and maximum size of these code groups by setting properties of the QueryService through the Teiid Console.
Function |
Definition |
Datatype Constraint |
---|---|---|
LOOKUP(codeGroup, returnElement, keyElement, keyValue) |
In the lookup group codeGroup, find the row where keyElement has the value keyValue and return the associated returnElement |
codeGroup must be a fully-qualified string literal containing metadata identifiers, keyValue datatype must match datatype of the keyElement, return datatype matches that of returnElement. returnElement and keyElement parameters should use their shortened names. |
System functions provide access to information in the Teiid system from within a query.
Function |
Definition |
Datatype Constraint |
---|---|---|
|
Retrieve the name of the user executing the query |
return is string |
|
Retrieve an environment property. The only key currently allowed is ‘sessionid’, although this will expand in the future. |
key in {string}, return is string |
|
Retrieve the string form of the command payload or null if no command payload was specified. The command payload is set by a method on the Teiid JDBC API extensions on a per-query basis. |
Returns a string |
|
Cast the command payload object to a java.util.Properties object and look up the specified key in the object |
key in {string}, return is string |
XML functions provide functionality for working with XML data.
Function |
Definition |
Datatype Constraint |
---|---|---|
|
Takes a document and an XPATH query and returns a string value for the result. An attempt is made to provide a meaningful result for non-text nodes. |
Doc in {string, xml} and xpath in {string}. Return value is a string. |
Security functions provide the ability to interact with the security system.
Function |
Definition |
Datatype Constraint |
---|---|---|
|
Whether the current caller has the role roleName of roleType. |
roleType must be one of ('data','admin' , 'repository') and roleName must be a string, the return type is Boolean. |
If you need to extends Teiid's scalar function library, then Teiid provides a means to define custom scalar functions or User Defined Functions(UDF). The following steps need to be taken in creating a UDF.
The FunctionDefinition.xmi file provides metadata to the query engine on User Defined Functions. See our product document on "Creating User-defined Functions" for a more extensive reference on creating that file through the Designer Tool.
The following are used to define a UDF.
Function Name When you create the function name, keep these requirements in mind:
You cannot use a reserved word, which includes existing Teiid System function names. You cannot overload existing Teiid System functions.
The function name must be unique among user-defined functions for the number of arguments. You can use the same function name for different numbers of types of arguments. Hence, you can overload your user-defined functions.
The function name can only contain letters, numbers, and the underscore (_). Your function name must start with a letter.
The function name cannot exceed 128 characters.
Input Parameters - defines a type specific signature list. All arguments are considered required.
Return Type - the expected type of the returned scalar value.
Pushdown - can be one of REQUIRED, NEVER, ALLOWED. Indicates the expected pushdown behavior. If NEVER or ALLOWED are specified then a Java implementation of the function should be supplied.
invocationClass/invocationMethod - optional properties indicating the static method to invoke when the UDF is not pushed down.
Deterministic - if the method will always return the same result for the same input parameters.
While Teiid provides an extensive scalar function library, it contains only those functions that can be evaluated within the query engine. In many circumstances, especially for performance, a user defined function allows for calling a source specific function.
For example, suppose you want to use the Oracle-specific functions score and contains:
SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) > 0
The
score
and
contains
functions are not part of built-in scalar function library. While
you could write your own custom scalar function to mimic their
behavior, it's more likely that you would want to use the actual
Oracle functions that are provided by Oracle when using the Oracle
Free Text functionality.
In addition to the normal steps outlined in the section to create and install a function model (FunctionDefinitions.xmi), you will need to extend the appropriate connector(s).
For example, to extend the Oracle Connector
Required - extend OracleCapabilities and set up SCORE and CONTAINS as supported functions (this lets Teiid know that the connector can accept these functions).
Optionally extend the OracleSQLTranslator to insert new FunctionModifiers to handle translation of these functions. Given that the syntax of these functions is same as other typical functions, this probably isn't needed - the default translation should work.
Create a new connector type - the easiest way is to export the Oracle ANSI connector type from the Console and just modify the properties such as the connector name (to differentiate it from base Oracle connector) and the capabilities class (to use the extended version) and possibly the translation class (if that was extended for b. Also, connector classpath needs to be extended to include a new jar of your changes above.
Install the code as an extension module and add your new connector type in the Console.
Non-pushdown support requires a Java function that matches the metadata supplied in the FunctionDefinitions.xmi file. You must create a Java method that contains the function’s logic. This Java method should accept the necessary arguments, which the Teiid System will pass to it at runtime, and function should return the calculated or altered value.
Code Requirements
The java class containing the function method must be defined public.
The function method must be public and static.
Number of input arguments must match the function metadata defined in section Install user-defined functions
Any exception can be thrown, but Teiid will rethrow the exception as a FunctionExecutionException
.
Example 4.1. Sample code
package userdefinedfunctions; public class TempConv { /** * Converts the given Celsius temperature to Fahrenheit, and returns the * value. * @param doubleCelsiusTemp * @return Fahrenheit */ public static Double celsiusToFahrenheit(Double doubleCelsiusTemp){ if (doubleCelsiusTemp == null) { return null; } return (doubleCelsiusTemp)*9/5 + 32; } }
After coding the functions you should compile the Java code into a Java Archive (JAR) file, so that you can add it to the Teiid System as an Extension Module.
After adding the jar file as an extension module, the name of jar file need to be added to user defined functions classpath using Console tool.
Once a user-defined function model (FunctionDefinitions.xmi) has been created in in the Designer Tool, it should be installed by replacing the existing version under the Extension Modules (for the Enterprise product this will be done through the Console). That will allow the query engine to know about and use functions