JBoss.orgCommunity Documentation

Chapter 4. Scalar Functions

4.1. Numeric Functions
4.1.1. Parsing Numeric Datatypes from Strings
4.1.2. Formatting Numeric Datatypes as Strings
4.2. String Functions
4.3. Date/Time Functions
4.3.1. Parsing Date Datatypes from Strings
4.3.2. Specifying Time Zones
4.4. Type Conversion Functions
4.5. Choice Functions
4.6. Decode Functions
4.7. Lookup Function
4.7.1. Clearing the Cache
4.8. System Functions
4.9. XML Functions
4.10. Security Functions
4.11. User Defined Functions
4.11.1. UDF Definition
4.11.2. Source Supported UDF
4.11.3. Non-pushdown Support for User-Defined Functions
4.11.4. Installing user-defined functions
4.12. Nondeterministic Function Handling

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, bigdecimal}, return type is double

ASIN(x)

Arc sine of x

x in {double, bigdecimal}, return type is double

ATAN(x)

Arc tangent of x

x in {double, bigdecimal}, return type is double

ATAN2(x,y)

Arc tangent of x and y

x, y in {double, bigdecimal}, 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, bigdecimal}, return type is double

COT(x)

Cotangent of x

x in {double, bigdecimal}, return type is double

DEGREES(x)

Convert x degrees to radians

x in {double, bigdecimal}, 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, bigdecimal}, 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 {double, bigdecimal, biginteger}, return is the same type as x

RADIANS(x)

Convert x radians to degrees

x in {double, bigdecimal}, 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, bigdecimal}, return type is double

SQRT(x)

Square root of x

x in {long, double, bigdecimal}, return type is double

TAN(x)

Tangent of x

x in {double, bigdecimal}, 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 the left most character in x. The empty string will as input will return null.

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 in {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 in {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 or operate on dates, times, or timestamps.

Parse and format Date/Time 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 formats by visiting the Sun Java Web site at the following URL for Sun Java.

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:

  1. SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second)

  2. SQL_TSI_SECOND - seconds

  3. SQL_TSI_MINUTE - minutes

  4. SQL_TSI_HOUR - hours

  5. SQL_TSI_DAY - days

  6. SQL_TSI_WEEK - weeks

  7. SQL_TSI_MONTH - months

  8. SQL_TSI_QUARTER - quarters (3 months)

  9. SQL_TSI_YEAR - years

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')

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:

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.

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

USER()

Retrieve the name of the user executing the query

return is string

ENV(key)

Retrieve an environment property. The only key currently allowed is ‘sessionid’, although this will expand in the future.

key in {string}, return is string

COMMANDPAYLOAD()

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

COMMANDPAYLOAD(key)

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

XPATHVALUE(doc, xpath)

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

hasRole(roleType, roleName)

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.

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:

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).

Teiid categorizes functions by varying degrees of determinism. When a function is evaluated and to what extent the result can be cached are based upon its determinism level.

  1. 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, such as the lookup function, are not truly deterministic, but is treated as such for performance. All functions not categorized below are considered deterministic.

  2. Session Deterministic - the function will return the same result for the given inputs under the same user session. This category includes the hasRole, env, and user functions. 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.

  3. Command Deterministic - the result of function evaluation is only deterministic within the scope of the user command. This category include the curdate, curtime, now, and commandpayload functions. 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 evaulation will occur prior to pushdown - however multiple occurances of the same command deterministic time function are not guarenteed to evaluate to the same value.

  4. Nondeterministic - the result of function evaluation is fully nondeterministic. This category includes the rand function and UDFs marked as 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 it's execution context (for example if the function is used in the select clause).