JBoss.orgCommunity Documentation

Teiid - Scalable Information Integration

Teiid Reference Documentation


Legal Notice

1. SQL Support
1.1. Identifiers
1.2. Expressions
1.2.1. Column Identifiers
1.2.2. Literals
1.2.3. Aggregate Functions
1.2.4. Case and searched case
1.2.5. Scalar subqueries
1.2.6. Parameter references
1.3. Criteria
1.4. SQL Commands
1.4.1. SELECT Command
1.4.2. INSERT Command
1.4.3. UPDATE Command
1.4.4. DELETE Command
1.4.5. EXECUTE Command
1.4.6. Cache Hint
1.4.7. Procedural Relational Command
1.5. Temp Tables
1.6. SQL Clauses
1.6.1. SELECT Clause
1.6.2. FROM Clause
1.6.3. WHERE Clause
1.6.4. GROUP BY Clause
1.6.5. HAVING Clause
1.6.6. ORDER BY Clause
1.6.7. LIMIT Clause
1.6.8. INTO Clause
1.6.9. OPTION Clause
1.7. Set Operations
1.8. Subqueries
1.8.1. Inline views
1.8.2. Subqueries in the WHERE and HAVING clauses
2. XML SELECT Command
2.1. Overview
2.2. Query Structure
2.2.1. FROM Clause
2.2.2. SELECT Clause
2.2.3. WHERE Clause
2.2.4. ORDER BY Clause
2.3. Document Generation
2.3.1. Document Validation
3. Datatypes
3.1. Supported Types
3.2. Type Conversions
3.3. Special Conversion Cases
3.3.1. Conversion of String Literals
3.3.2. Converting to Boolean
3.3.3. Date/Time/Timestamp Type Conversions
3.4. Escaped Literal Syntax
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.8. System Functions
4.9. XML Functions
4.9.6. XMLPI
4.10. Security Functions
4.10.1. HASROLE
4.11. Nondeterministic Function Handling
5. Procedures
5.1. Procedure Language
5.1.1. Command Statement
5.1.2. Dynamic SQL Command
5.1.3. Declaration Statement
5.1.4. Assignment Statement
5.1.5. If Statement
5.1.6. Loop Statement
5.1.7. While Statement
5.1.8. Continue Statement
5.1.9. Break Statement
5.1.10. Error Statement
5.2. Virtual Procedures
5.2.1. Virtual Procedure Definition
5.2.2. Procedure Input Parameters
5.2.3. Example Virtual Procedures
5.2.4. Executing Virtual Procedures
5.3. Update Procedures
5.3.1. Update Procedure Definition
5.3.2. Special Variables
5.3.3. Update Procedure Command Criteria
5.3.4. Update Procedure Processing
6. Transaction Support
6.1. AutoCommitTxn Execution Property
6.2. Updating Model Count
6.3. JDBC and Transactions
6.3.1. JDBC API Functionality
6.3.2. J2EE Usage Models
6.4. Limitations and Workarounds
7. Data Roles
7.1. Permissions
7.2. XML Definition
8.1. System Tables
8.1.1. VDB, Schema, and Properties
8.1.2. Table Metadata
8.1.3. Procedure Metadata
8.1.4. Datatype Metadata
8.2. System Procedures
9. Translators
9.1. Introduction to the Teiid Connector Architecture
9.2. Translators
9.2.1. File Translator
9.2.2. JDBC Translator
9.2.3. LDAP Translator
9.2.4. Loopback Translator
9.2.5. Salesforce Translator
9.2.6. Web Services Translator
9.3. Dynamic VDBs
9.3.1. VDB Element
9.3.2. Model Element
9.3.3. Translator Element
10. Federated Planning
10.1. Overview
10.2. Federated Optimizations
10.2.1. Access Patterns
10.2.2. Pushdown
10.2.3. Dependent Joins
10.2.4. Copy Criteria
10.2.5. Projection Minimization
10.2.6. Partial Aggregate Pushdown
10.2.7. Optional Join
10.2.8. Standard Relational Techniques
10.3. Federated Failure Modes
10.3.1. Partial Results
10.4. Query Plans
10.4.1. Getting a Query Plan
10.4.2. Analyzing a Query Plan
10.4.3. Relational Plans
10.5. Query Planner
10.5.1. Relational Planner
10.5.2. Procedure Planner
10.5.3. XML Planner
11. Architecture
11.1. Terminology
11.2. Data Management
11.2.1. Cursoring and Batching
11.2.2. Buffer Management
11.2.3. Cleanup
11.3. Query Termination
11.3.1. Canceling Queries
11.3.2. Timeouts
11.4. Processing
11.4.1. Join Algorithms
11.4.2. Sort Based Algorithms
A. BNF for SQL Grammar

Teiid offers a highly scalable and high performance solution to information integration. By allowing integrated and enriched data to be consumed relationally or as XML over multiple protocols, Teiid simplifies data access for developers and consuming applications.

Commercial development support, production support, and training for Teiid is available through JBoss Inc. Teiid is a Professional Open Source project and a critical component of the JBoss Enterprise Data Services Platform.

Teiid supports SQL for issuing queries and for defining view transformations; see also Procedure Language for how SQL is used in virtual procedures and update procedures.

Teiid provides nearly all of the functionality of SQL-92 DML. SQL-99 and later features are constantly being added based upon community need. The following does not attempt to cover SQL exhaustively, but rather highlights SQL's usage within Teiid. See the grammar for the exact form of SQL accepted by Teiid.

SQL commands contain references to tables and columns. These references are in the form of identifiers, which uniquely identify the tables and columns in the context of the command. All queries are processed in the context of a virtual database, or VDB. Because information can be federated across multiple sources, tables and columns must be scoped in some manner to avoid conflicts. This scoping is provided by schemas, which contain the information for each data source or set of views.

Fully-qualified identifiers can always be used in SQL commands. Partially- or unqualified forms can also be used, as long as the resulting names are unambiguous in the context of the command. Different forms of qualification can be mixed in the same query.

Identifiers, literals, and functions can be combined into expressions. Expressions can be used almost anywhere in a query -- SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER BY.

Literal values represent fixed values. These can any of the 'standard' data types.

Syntax Rules:

  • Integer values will be assigned an integral data type big enough to hold the value (integer, long, or biginteger).

  • Floating point values will always be parsed as a double.

  • The keyword 'null' is used to represent an absent or unknown value and is inherently untyped. In many cases, a null literal value will be assigned an implied type based on context. For example, in the function '5 + null', the null value will be assigned the type 'integer' to match the type of the value '5'. A null literal used in the SELECT clause of a query with no implied context will be assigned to type 'string'.

Some examples of simple literal values are:

  • ‘abc’

  • ‘isn’’t true’ - use an extra single tick to escape a tick in a string with single ticks

  • 5

  • -37.75e01 - scientific notation

  • 100.0 - parsed as double

  • true

  • false

  • '\u0027' - unicode character

Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group.

Syntax Rules:

  • Some aggregate functions may contain a keyword 'DISTINCT' before the expression, indicating that duplicate expression values should be ignored. DISTINCT is not allowed in COUNT(*) and is not meaningful in MIN or MAX (result would be unchanged), so it can be used in COUNT, SUM, and AVG.

  • Aggregate functions may only be used in the HAVING or SELECT clauses and may not be nested within another aggregate function.

  • Aggregate functions may be nested inside other functions.

For more information on aggregates, see the sections on GROUP BY or HAVING.

There are 4 basic commands for manipulating data in SQL, corresponding to the CRUD create, read, update, and delete operations: INSERT, SELECT, UPDATE, and DELETE. In addition, procedures can be executed using the EXECUTE command or through a procedural relational command.

The SELECT command is used to retrieve records any number of relations.

All of these clauses other than OPTION are defined by the SQL specification. The specification also specifies the order that these clauses will be logically processed. Below is the processing order where each stage passes a set of rows to the following stage. Note that this processing model is logical and does not represent the way any actual database engine performs the processing, although it is a useful model for understanding questions about SQL.

  • FROM stage - gathers all rows from all tables involved in the query and logically joins them with a Cartesian product, producing a single large table with all columns from all tables. Joins and join criteria are then applied to filter rows that do not match the join structure.

  • WHERE stage - applies a criteria to every output row from the FROM stage, further reducing the number of rows.

  • GROUP BY stage - groups sets of rows with matching values in the group by columns.

  • HAVING stage - applies criteria to each group of rows. Criteria can only be applied to columns that will have constant values within a group (those in the grouping columns or aggregate functions applied across the group).

  • SELECT stage - specifies the column expressions that should be returned from the query. Expressions are evaluated, including aggregate functions based on the groups of rows, which will no longer exist after this point. The output columns are named using either column aliases or an implicit name determined by the engine. If SELECT DISTINCT is specified, duplicate removal will be performed on the rows being returned from the SELECT stage.

  • ORDER BY stage - sorts the rows returned from the SELECT stage as desired. Supports sorting on multiple columns in specified order, ascending or descending. The output columns will be identical to those columns returned from the SELECT stage and will have the same name.

  • LIMIT stage - returns only the specified rows (with skip and limit values).

This model can be used to understand many questions about SQL. For example, columns aliased in the SELECT clause can only be referenced by alias in the ORDER BY clause. Without knowledge of the processing model, this can be somewhat confusing. Seen in light of the model, it is clear that the ORDER BY stage is the only stage occurring after the SELECT stage, which is where the columns are named. Because the WHERE clause is processed before the SELECT, the columns have not yet been named and the aliases are not yet known.

Procedural relational commands use the syntax of a SELECT to emulate an EXEC. In a procedural relational command a procedure group names is used in a FROM clause in place of a table. That procedure will be executed in place of a normal table access if all of the necessary input values can be found in criteria against the procedure. Each combination of input values found in the criteria results in an execution of the procedure.

Multiple Execution

The usage of 'in' or join criteria can result in the procedure being executed multiple times.

Alternative Syntax

None of issues listed in the syntax rules above exist if a nested table reference is used.

Teiid supports creating temporary,or "temp", tables. Temp tables are dynamically created, but are treated as any other physical table.

Temp tables can be defined implicitly by referencing them in a SELECT INTO or in an INSERT statement or explicitly with a CREATE TABLE statement. Implicitly created temp tables must have a name that starts with '#'.

The following example is a series of statements that loads a temporary table with data from 2 sources, and with a manually inserted record, and then uses that temp table in a subsequent query.

CREATE LOCAL TEMPORARY TABLE TEMP (a integer, b integer, c integer); 
SELECT * INTO temp FROM Src1; SELECT * INTO temp FROM Src2; 
INSERT INTO temp VALUES (1,2,3); 
SELECT a,b,c FROM Src3, temp WHERE Src3.a = temp.b; 

See virtual procedures for more on temp table usage.

This section describes the clauses that are used in the various SQL commands described in the previous section. Nearly all these features follow standard SQL syntax and functionality, so any SQL reference can be used for more information.

The FROM clause specifies the target table(s) for SELECT, UPDATE, and DELETE statements.

Example Syntax:

DEP Hints

MAKEDEP and MAKENOTDEP are hints used to control dependent join behavior. They should only be used in situations where the optimizer does not choose the most optimal plan based upon query structure, metadata, and costing information.

The TEXTTABLE funciton processes character input to produce tabular ouptut. It supports both fixed and delimited file format parsing. The function itself defines what columns it projects. The TEXTTABLE function is implicitly a nested table and may be correlated to preceeding FROM clause entries.


TEXTTABLE(expression COLUMNS <COLUMN>, ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name

COLUMN := name datatype [WIDTH integer]

The XMLTABLE funciton uses XQuery to produce tabular ouptut. The XMLTABLE function is implicitly a nested table and may be correlated to preceeding FROM clause entries. XMLTABLE is part of the SQL/XML 2006 specification.


XMLTABLE([<NSP>,] xquery-expression [<PASSING>] [COLUMNS <COLUMN>, ... )] AS name

COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))

See XMLELEMENT for the definition of NSP - XMLNAMESPACES.

See XMLQUERY for the definition of PASSING.



  • The optional XMLNAMESPACES clause specifies the namepaces for use in the XQuery and COLUMN path expressions.

  • The xquery-expression should be a valid XQuery. Each sequence item returned by the xquery will be used to create a row of values as defined by the COLUMNS clause.

  • If COLUMNS is not specified, then that is the same as having the COLUMNS clause: "COLUMNS OBJECT_VALUE XML PATH '.'", which returns the entire item as an XML value. Each non-ordinality column specifies a type and optionally a PATH and a DEFAULT expression. If PATH is not specified, then the path will be the same as the column name. A FOR ORDINALITY column is typed as integer and will return the 1-based item number as its value.

Syntax Rules:

  • Only 1 FOR ORDINALITY column may be specified.

  • The columns names must be not contain duplicates.


  • Use of passing, returns 1 row [1]:

    select * from xmltable('/a' PASSING {x '<a id="1"/>'} COLUMNS id integer PATH '@id') x

  • As a nested table:

    select x.* from t, xmltable('/x/y' PASSING t.doc COLUMNS first string, second FOR ORDINALITY) x

When the into clause is specified with a SELECT, the results of the query are inserted into the specified table. This is often used to insert records into a temporary table. The INTO clause immediately precedes the FROM clause.


INTO table FROM ...

Syntax Rules:

  • The INTO clause is logically applied last in processing, after the ORDER BY and LIMIT clauses.

  • Teiid's support for SELECT INTO is similar to MS SQL Server. The target of the INTO clause is a table where the result of the rest select command will be inserted. SELECT INTO should not be used UNION query.

A subquery is a SQL query embedded within another SQL query. The query containing the subquery is the outer query.

Supported subquery locations:

A valid XML SELECT Command against a document model is of the form SELECT ... FROM ... [WHERE ...] [ORDER BY ...] . The use of any other SELECT command clause is not allowed.

The fully qualified name for an XML element is: "model"."document name".[path to element]."element name" .

The fully qualified name for an attribute is: "model"."document name".[path to element]."element name".[@]"attribute name"

Partially qualified names for elements and attributes can be used as long as the partial name is unique.

The where clause specifies how to filter content from the generated document based upon values contained in the underlying mapping classes. Most predicates are valid in an XML SELECT Command, however combining value references from different parts of the document may not always be allowed.

Criteria is logically applied to a context which is directly related to a mapping class. Starting with the root mapping class, there is a root context that describes all of the top level repeated elements that will be in the output document. Criteria applied to the root or any other context will change the related mapping class query to apply the affects of the criteria, which can include checking values from any of the descendant mapping classes.

Syntax Rules:

Sibling Root Mapping Classes

Implied root context user criteria against a document model with sibling root mapping classes is not generally semantically correct. It is applied as if each of the conjuncts is applied to only a single root mapping class. This behavior is the same as prior releases but may be fixed in a future release.

XML SELECT Command functions are resemble scalar functions, but act as hints in the WHERE clause. These functions are only valid in an XML SELECT Command.

Teiid supports a core set of runtime types. Runtime types can be different than semantic types defined in type fields at design time. The runtime type can also be specified at design time or it will be automatically chosen as the closest base type to the semantic type.

Table 3.1. Teiid Runtime Types

TypeDescriptionJava Runtime ClassJDBC TypeODBC Type
string or varcharvariable length character string with a maximum length of 4000. Note that the length cannot be explicitly set with the type literal, e.g. varchar(100).java.lang.StringVARCHARVARCHAR
chara single Unicode characterjava.lang.CharacterCHARCHAR
booleana single bit, or Boolean, that can be true, false, or null (unknown) java.lang.BooleanBITSMALLINT
byte or tinyintnumeric, integral type, signed 8-bitjava.lang.ByteTINYINTSMALLINT
short or smallintnumeric, integral type, signed 16-bitjava.lang.ShortSMALLINTSMALLINT
integernumeric, integral type, signed 32-bitjava.lang.IntegerINTEGERINTEGER
long or bigintnumeric, integral type, signed 64-bitjava.lang.LongBIGINTNUMERIC
bigintegernumeric, integral type, arbitrary precision of up to 1000 digitsjava.lang.BigIntegerNUMERICNUMERIC
float or realnumeric, floating point type, 32-bit IEEE 754 floating-point numbersjava.lang.FloatREALFLOAT
doublenumeric, floating point type, 64-bit IEEE 754 floating-point numbersjava.lang.StringDOUBLEDOUBLE
bigdecimal or decimalnumeric, floating point type, arbitrary precision of up to 1000 digits. Note that the precision and scale cannot be explicitly set with the type literal, e.g. decimal(38, 2).java.math.BigDecimalNUMERICNUMERIC
datedatetime, representing a single day (year, month, day)java.sql.DateDATEDATE
timedatetime, representing a single time (hours, minutes, seconds, milliseconds)java.sql.TimeTIMETIME
timestampdatetime, representing a single date and time (year, month, day, hours, minutes, seconds, milliseconds, nanoseconds)java.sql.TimestampTIMESTAMPTIMESTAMP
objectany arbitrary Java object, must implement java.lang.SerializableAnyJAVA_OBJECTVARCHAR
blobbinary large object, representing a stream of bytes java.sql.Blob [a] BLOBVARCHAR
clobcharacter large object, representing a stream of characters java.sql.Clob [b] CLOBVARCHAR
xmlXML document java.sql.SQLXML [c] JAVA_OBJECTVARCHAR

[a] The concrete type is expected to be org.teiid.core.types.BlobType

[b] The concrete type is expected to be org.teiid.core.types.ClobType

[c] The concrete type is expected to be org.teiid.core.types.XMLType

Data types may be converted from one form to another either explicitly or implicitly. Implicit conversions automatically occur in criteria and expressions to ease development. Explicit datatype conversions require the use of the CONVERT function or CAST keyword.

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. See the Developers Guide for adding UDFs. Once added UDFs may be called just like any other function.

Numeric functions return numeric values (integer, long, float, double, biginteger, bigdecimal). They generally take numeric values as inputs, though some take strings.



Datatype Constraint

+ - * /

Standard numeric operators

x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x [a]


Absolute value of x

See standard numeric operators above


Arc cosine of x

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


Arc sine of x

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


Arc tangent of x

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


Arc tangent of x and y

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


Ceiling of x

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


Cosine of x

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


Cotangent of x

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


Convert x degrees to radians

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



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


Floor of x

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


Formats x using format y

x is bigdecimal, y is string, returns string


Formats x using format y

x is biginteger, y is string, returns string


Formats x using format y

x is double, y is string, returns string


Formats x using format y

x is float, y is string, returns string


Formats x using format y

x is integer, y is string, returns string


Formats x using format y

x is long, y is string, returns string


Natural log of x (base e)

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


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


Parses x using format y

x, y are strings, returns bigdecimal


Parses x using format y

x, y are strings, returns biginteger


Parses x using format y

x, y are strings, returns double


Parses x using format y

x, y are strings, returns float


Parses x using format y

x, y are strings, returns integer


Parses x using format y

x, y are strings, returns long


Value of Pi

return is double


x to the y power

x in {double, bigdecimal, biginteger}, return is the same type as x


Convert x radians to degrees

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


Returns a random number, using generator established so far in the query or initializing with system clock if necessary.

Returns double.


Returns a random number, using new generator seeded with x.

x is integer, returns double.


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


1 if x > 0, 0 if x = 0, -1 if x < 0

x in {integer, long, float, double, biginteger, bigdecimal}, return type is integer


Sine value of x

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


Square root of x

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


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


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


parseDouble(cost, '$#,##0.00;($#,##0.00)')




parseFloat(percent, '#,##0%')




parseFloat(total, '#,##0.###;-#,##0.###')




parseLong(amt, '0.###E0')




parseInteger(total, '#,##0;-#,##0')



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



formatDouble(cost, '$#,##0.00;($#,##0.00)')




formatFloat(percent, '#,##0%')




formatFloat(total, '#,##0.###;-#,##0.###')




formatLong(amt, '0.###E0')




formatInteger(total, '#,##0;-#,##0')


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.



Datatype Constraint

x || y

Concatenation operator

x,y in {string}, return type is string


Provide ASCII value of the left most character in x. The empty string will as input will return null. [a]

return type is integer

CHR(x) CHAR(x)

Provide the character for ASCII value x [a]

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}


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}


Lowercase of x

x in {string}

LEFT(x, y)

Get left y characters of x

x in {string}, y in {string}, return string


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


Left trim x of white space

x in {string}, return string

QUERYSTRING(path [, expr [AS name] ...])

Returns a properly encoded query string appended to the given path. Null valued expressions are omitted, and a null path is treated as ''.

Names are optional for column reference expressions.

e.g. QUERYSTRING('path', 'value' as "&x", ' & ' as y, null as z) returns 'path?%26x=value&y=%20%26%20'

path, expr in {string}. name is an identifier


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


Right trim x of white space

x is string, return string


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}

TO_CHARS(x, encoding)

Return a clob from the blob with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.[b]

x is a blob, encoding is a string, and returns a clob

TO_BYTES(x, encoding)

Return a blob from the clob with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.[b]

x in a clob, encoding is a string, and returns a blob

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}


Uppercase of x

x in {string}

[a] Non-ASCII range characters or integers used in these functions may produce different results or exceptions depending on where the function is evalutated (Teiid vs. source). Teiid's uses Java default int to char and char to int conversions, which operates over UTF16 values.

[b] See the Charset JavaDoc for more on supported Charset names. For charsets, unmappable chars will be replaced with the charset default character. binary formats, such as BASE64, will error in their conversion to bytes is a unrecognizable character is encountered.

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.



Datatype Constraint


Return current date

returns date


Return current time

returns time


Return current timestamp (date and time)

returns timestamp


Return name of day

x in {date, timestamp}, returns string


Return day of month

x in {date, timestamp}, returns integer


Return day of week (Sunday=1)

x in {date, timestamp}, returns integer


Return Julian day number

x in {date, timestamp}, returns integer


Format date x using format y

x is date, y is string, returns string


Format time x using format y

x is time, y is string, returns string


Format timestamp x using format y

x is timestamp, y is string, returns string

FROM_UNIXTIME (unix_timestamp)

Return the Unix timestamp (in seconds) as a Timestamp value

Unix timestamp (in seconds)


Return hour (in military 24-hour format)

x in {time, timestamp}, returns integer


Return minute

x in {time, 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


Return month

x in {date, timestamp}, returns integer


Return name of month

x in {date, timestamp}, returns string


Parse date from x using format y

x, y in {string}, returns date


Parse time from x using format y

x, y in {string}, returns time


Parse timestamp from x using format y

x, y in {string}, returns timestamp


Return quarter

x in {date, timestamp}, returns integer


Return seconds

x in {time, timestamp}, returns integer


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 {timestamp}, returns a long.


Return week in year

x in {date, timestamp}, returns integer


Return four-digit year

x in {date, timestamp}, returns integer

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:


Function Call To Parse String


parseDate(myDateString, 'yyyyMMdd')


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 .



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.



Datatype Constraint


Returns the first non-null parameter

x and all y's can be any compatible types


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


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

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.



Datatype Constraint


Decode column x using string of value pairs y and return the decoded column as a string

all string


Decode column x using string of value pairs y with delimiter z and return the decoded column as a string

all string


Decode column x using string of value pairs y and return the decoded column as an integer

all string parameters, return integer


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 table’s data in memory and access it through a scalar function. This caching accelerates response time to queries that use the lookup tables, known in business terminology as lookup tables or code tables.

A StatePostalCodes table used to translate postal codes to complete state names might represent an example of this type of lookup table. One column, PostalCode, represents a key column. Other tables refer to this two-letter code. A second column, StateDisplayName, would represent the complete name of the state. Hence, a query to this lookup table would typically provide the PostalCode and expect the StateDisplayName in response.

When you call this function for any combination of codeTable, returnColumn, and keyColumn 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 table. The codeTable requires use of the fully-qualified name, and the returnColumn and keyColumn 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 table. The Teiid System unloads these cached lookup tables 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.



Datatype Constraint

LOOKUP(codeTable, returnColumn, keyColumn, keyValue)

In the lookup table codeTable, find the row where keyColumn has the value keyValue and return the associated returnColumn

codeTable must be a fully-qualified string literal containing metadata identifiers, keyValue datatype must match datatype of the keyColumn, return datatype matches that of returnColumn. returnColumn and keyColumn parameters should use their shortened names.

System functions provide access to information in the Teiid system from within a query.



Datatype Constraint


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


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 name of the user executing the query

return is string

XML functions provide functionality for working with XML data.

Returns an concatination of XML elements for each content item.

XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*)

See XMLELEMENT for the definition of NSP - XMLNAMESPACES.

Name is an identifier. Content can be any type. Return value is xml.

If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.

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

Teiid supports a procedural language for defining virtual procedures . These are similar to stored procedures in relational database management systems. You can use this language to define the transformation logic for decomposing INSERT, UPDATE, and DELETE commands against views; these are known as update procedures .

Dynamic SQL allows for the execution of an arbitrary SQL command in a virtual procedure. Dynamic SQL is useful in situations where the exact command form is not known prior to execution.


EXECUTE STRING <expression> [AS <variable> <type> [, <variable> <type>]* [INTO <variable>]] 
[USING <variable>=<expression> [,<variable>=<expression>]*] [UPDATE <literal>]        

Syntax Rules:

Here is an example showing a more complex approach to building criteria for the dynamic SQL string. In short, the virtual procedure AccountAccess.GetAccounts has inputs ID, LastName, and bday. If a value is specified for ID it will be the only value used in the dynamic SQL criteria. Otherwise if a value is specified for LastName the procedure will detect if the value is a search string. If bday is specified in addition to LastName, it will be used to form compound criteria with LastName.

Known Limitations and Work-Arounds

Virtual procedures are defined using the Teiid procedural language. A virtual procedure has zero or more input parameters, and a result set return type. Virtual procedures support the ability to execute queries and other SQL commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.



The CREATE VIRTUAL PROCEDURE line demarcates the beginning of the procedure. The BEGIN and END keywords are used to denote block boundaries. Within the body of the procedure, any valid statement may be used.

The last command statement executed in the procedure will be return as the result. The output of that statement must match the expected result set and parameters of the procedure.

You execute procedures using the SQL EXECUTE command. If the procedure has defined inputs, you specify those in a sequential list, or using "name=value" syntax. You must use the name of the input parameter, scoped by the full procedure name if the parameter name is ambiguous in the context of other columns or variables in the procedure.

A virtual procedure call will return a result set just like any SELECT, so you can use this in many places you can use a SELECT. However, within a virtual procedure itself you cannot always use an EXEC directly. Instead, you use the following syntax:


The following are some examples of how you can use the results of a virtual procedure call within a virtual procedure definition:

  • LOOP instruction - you can walk through the results and do work on a row-by-row basis

  • Assignment instruction - you can run a command and set the first column / first row value returned to a variable

  • SELECT * INTO #temp FROM (EXEC ...) AS x - you can select the results from a virtual procedure into a temp table, which you can then query against as if it were a physical table.

Views are abstractions above physical sources. They typically union or join information from multiple tables, often from multiple data sources or other views. Teiid can perform update operations against views. Update commands - INSERT, UPDATE, or DELETE - against a view require logic to define how the tables and views integrated by the view are affected by each type of command. This transformation logic is invoked when an update command is issued against a view. Update procedures define the logic for how a user's update command against a view should be decomposed into the individual commands to be executed against the underlying physical sources. Similar to virtual procedures , update procedures have the ability to execute queries or other commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.

You can use a number of special variables when defining your update procedure.

You can use a number of special SQL clauses when defining UPDATE or DELETE procedures. These make it easier to do variable substitutions in WHERE clauses or to check on the change state of variables without using a lot of conditional logic.

You can use the TRANSLATE CRITERIA clause to convert the criteria from the user application’s SQL command into the form required to interact with the target source or view tables. The TRANSLATE CRITERIA statement uses the SELECT transformation to infer the column mapping. This clause evaluates to a translated criteria that is evaluated in the context of a command.


TRANSLATE [criteria operator] CRITERIA [ON (column list)] [WITH (mapping list)]

You can use these mappings either to replace the default mappings generated from the SELECT transformation or to specify a reverse expression when a virtual column is defined by an expression.

Some samples of the HAS TRANSLATE clause:

TRANSLATE CRITERIATranslates any user criteria using the default mappings.
TRANSLATE CRITERIA WITH (column1 = 'A', column2 = INPUTS.column2 + 2)Translates any criteria with some additional mappings: column1 is always mapped to 'A' and column2 is mapped to the incoming column2 value + 2.
TRANSLATE = CRITERIA ON (column1)Translates only criteria that have = comparison operator and involve column1.

The TRANSLATE CRITERIA, ON clause always refers to view columns. The WITH clause always has items with form <elem> = <expression>, where the <elem> is a view column and the <expression> specifies what that view column should be replaced with when TRANSLATE CRITERIA translates the view criteria (from UPDATE or DELETE) into a physical criteria in the command. By default, a mapping is created based on the SELECT clause of the SELECT transformation (view column gets mapped to expression in SELECT clause at same position).

Teiid utilizes XA transactions for participating in global transactions and for demarcating its local and command scoped transactions. JBoss Transactions is used by Teiid as its transaction manager. See this documentation for the advanced features provided by JBoss Transactions.

The default transaction isolation level for Teiid is READ_COMMITTED.

Data roles, also called entitlements, are sets of permissions that are defined per VDB that dictate data access (create, read, update, delete). The use of data roles is controlled system wide with the property in <jboss-install>/server/<profile>/deploy/teiid/teiid-jboss-beans.xml file in bean configuration section of RuntimeEngineDeployer with property useEntitlements.

Once data roles are enabled, the access permissions defined in a VDB will be enforced by the Teiid Server.

Data roles are defined inside the vdb.xml file (inside the .vdb Zip archive under META-INF/vdb.xml) if you used Designer. This example will show a sample "vdb.xml" file with few simple data rules.

For example, if a VDB defines a table "TableA" in schema "modelName" with columns (column1, column2) - note that the column types do not matter. And we wish to define three roles "RoleA", "RoleB", "RoleC" with following permissions:

The above XML defined two data roles, "RoleA" which allows everything except delete on the table, "RoleC" that allows only read operation on the table. Since Teiid uses deny by default, there is no explict data-policy entry needed for "RoleB". The "mapped-role-name" defines the "role" to whom these policies are applicable. Each data-policy must define a "role" to be enforced by the Teiid Server.

For assigning the roles to your users, in the JBoss AS, check out the instructions for the selected Login Module. Check "Admin Guide" for configuring Login Modules.

"vdb.xml" file is checked against the schema file vdb-deployer.xsd, check the documents sections of the Teiid kit to find a copy of the schema file.


Currently there is no GUI tooling support in the Designer or any other management tool to create this data roles permissions xml, however this is in our roadmap for future releases to provide.

This table supplies information about all the schemas in the virtual database, including the system schema itself (System).

Column Name





VDB name



Schema name



True if this represents a source



Unique ID






URI for the primary metamodel describing the model used for this schema

This table supplies user-defined properties on all objects based on metamodel extensions. Normally, this table is empty if no metamodel extensions are being used.

Column Name





Extension property name



Extension property value



Key unique ID

This table supplies information about all the groups (tables, views, documents, etc) in the virtual database.

Column Name





VDB name



Schema Name



Short group name



Table type (Table, View, Document, ...)



Name of this group in the source



True if this is a source table



True if group can be updated



Group unique ID



Approximate number of rows in the group






True if in system table

This table supplies information about all the elements (columns, tags, attributes, etc) in the virtual database.

Column Name





VDB name



Schema Name



Table name



Element name (not qualified)



Position in group (1-based)



Name of element in source



Teiid runtime data type name



Number of digits after the decimal point



Element length (mostly used for strings)



Whether the length is fixed or variable



Element can be used in SELECT



Values can be inserted or updated in the element



Element is case-sensitive



Element is signed numeric value



Element represents monetary value



Element is auto-incremented in the source



Nullability: "Nullable", "No Nulls", "Unknown"



Minimum numeric value



Maximum numeric value



Searchability: "Searchable", "All Except Like", "Like Only", "Unsearchable"



Format of string value



Default value



Java class that will be returned



Number of digits in numeric value



Measure of return value size



Radix for numeric values



Upper-case full group name



Upper-case element name



Element unique ID




This table supplies information about primary, foreign, and unique keys.

Column Name





VDB name



Schema Name

Table Name


Table name



Key name






Name of key in source system



Type of key: "Primary", "Foreign", "Unique", etc



True if key is indexed



Referenced key UID (if foreign key)



Key unique ID

This table supplies information about the columns referenced by a key.

Column Name





VDB name



Schema Name



Table name



Element name



Key name



Key type: "Primary", "Foreign", "Unique", etc



Referenced key UID






Position in key

This table supplies information about the procedures in the virtual database.

Column Name





VDB name



Schema Name



Procedure name



Procedure name in source system



Returns a result set



Procedure UID




This supplies information on procedure parameters.

Column Name





VDB name



Schema Name



Procedure name



Parameter name



Teiid runtime data type name



Position in procedure args



Parameter direction: "In", "Out", "InOut", "ResultSet", "ReturnValue"



Parameter is optional



Precision of parameter



Length of parameter value



Scale of parameter



Radix of parameter



Nullability: "Nullable", "No Nulls", "Unknown"

This table supplies information on datatypes .

Column Name





Teiid design-time type name



Always false



Always false



Design-time type name (same as Name)



Java class returned for this type



Max scale of this type



Max length of this type



Nullability: "Nullable", "No Nulls", "Unknown"



Is signed numeric?



Is auto-incremented?



Is case-sensitive?



Max precision of this type



Radix of this type



Searchability: "Searchable", "All Except Like", "Like Only", "Unsearchable"



Data type unique ID



Teiid runtime data type name



Base type



Description of type





(string resourcePath)

A single column containing the resource as a clob.


(sting resourcePath)

A single column containing the resource as a blob.



A single column containing the resource paths as strings.


(string document)

A single column containing the schemas as clobs.

A Translator is typically paired with a particular JCA resource adapter. In instances where pooling, environment dependent configuration management, advanced security handling, etc. are not needed, then a JCA resource adapter is not needed. The configuration of JCA ConnectionFactories for needed resource adapters is not part of this guide, please see the Teiid Admin Guide and the kit examples for configuring resource adapters for use in JBossAS.

Translators can have a number of configurable properties. These are broken down into execution properties, which determine aspects of how data is retrieved, and import settings, which determine what metadata is read for import.

The execution properties for a translator typically have reasonable defaults. For specific translator types, e.g. the Derby translator, base execution properties are already tuned to match the source. In most cases the user will not need to adjust their values.


Only a subset of the metadata as to what SQL constructs the source supports can be set through execution properties. If more control is needed, please consult the Teiid Developers Guide.

There are no base importer settings.

The JDBC translator bridges between SQL semantic and data type difference between Teiid and a target RDBMS. Teiid has a range of specific translators that target the most popular open source and proprietary databases.

Type names

Table 9.4. Importer Properties - shared by all JDBC Translators

catalogSee DatabaseMetaData.getTables[1]null
schemaPatternSee DatabaseMetaData.getTables[1]null
tableNamePatternSee DatabaseMetaData.getTables[1]null
procedurePatternNameSee DatabaseMetaData.getProcedures[1]null
tableTypesComma separated list - without spaces - of imported table types. See DatabaseMetaData.getTables[1]null
useFullSchemaNameWhen false, directs the importer to drop the source catalog/schema from the Teiid object name, so that the Teiid fully qualified name will be in the form of <model name>.<table name> - Note: that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exceptiontrue
importKeystrue to import primary and foriegn keystrue
importIndexestrue to import index/unique key/cardinality informationtrue
importApproximateIndexestrue to import approximate index information. See DatabaseMetaData.getIndexInfo[1]true
importProcedurestrue to import procedures and procedure columns - Note that it is not always possible to import procedure result set columns due to database limitations. It is also not currently possible to import overloaded procedures.true
widenUnsignedTypestrue to convert unsigned types to the next widest type. For example SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte.true
quoteNameInSourcefalse will override the default and direct Teiid to create source queries using unquoted identifiers.true

[1] Full JavaDoc for DatabaseMetaData


The default import settings will crawl all available metadata. This import process is time consuming and full metadata import is not needed in most situations. Most commonly you'll want to limit import by schemaPattern and tableTypes.

Example importer settings to only import tables and views from my-schema.

<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="my-schema"/>

The Salesforce translator, known by the type name salesforce supports the SELECT, DELETE, INSERT and UPDATE operations against a Salesforce.com account. It is designed for use with the Teiid Salesforce resource adapter.

The Salesforce translator can import metadata, but does not currently have import settings.

Salesforce does not provide the same set of functionality as a relational database. For example, Salesforce does not support arbitrary joins between tables. However, working in combination with the Teiid Query Planner, the Salesforce connector supports nearly all of the SQL syntax supported by the Teiid.

The Salesforce Connector executes SQL commands by “pushing down” the command to Salesforce whenever possible, based on the supported capabilities. Teiid will automatically provide additional database functionality when the Salesforce Connector does not explicitly provide support for a given SQL construct. In these cases, the SQL construct cannot be “pushed down” to the data source, so it will be evaluated in Teiid, in order to ensure that the operation is performed.

In cases where certain SQL capabilities cannot be pushed down to Salesforce, Teiid will push down the capabilities that are supported, and fetch a set of data from Salesforce. Then, Teiid will evaluate the additional capabilities, creating a subset of the original data set. Finally, Teiid will pass the result to the client.

SELECT sum(Reports) FROM Supervisor where Division = 'customer support';

Neither Salesforce nor the Salesforce Connector support the sum() scalar function, but they do support CompareCriteriaEquals, so the query that is passed to Salesforce by the connector will be transformed to this query.

SELECT Reports FROM Supervisor where Division = 'customer support';

The sum() scalar function will be applied by the Teiid Query Engine to the result set returned by the connector.

In some cases multiple calls to the Salesforce application will be made to support the SQL passed to the connector.

DELETE From Case WHERE Status = 'Closed';

The API in Salesforce to delete objects only supports deleting by ID. In order to accomplish this the Salesforce connector will first execute a query to get the IDs of the correct objects, and then delete those objects. So the above DELETE command will result in the following two commands.

SELECT ID From Case WHERE Status = 'Closed';
DELETE From Case where ID IN (<result of query>);*

*The Salesforce API DELETE call is not expressed in SQL, but the above is an SQL equivalent expression.

It's useful to be aware of unsupported capabilities, in order to avoid fetching large data sets from Salesforce and making you queries as performant as possible. See all Supported Capabilities.

A multi-select picklist is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in SOQL are limited to EQ, NE, includes and excludes. The full Salesforce documentation for selecting from mullti-select picklists can be found at the following link. Querying Mulit-select Picklists

Teiid SQL does not support the includes or excludes operators, but the Salesforce connector provides user defined function definitions for these operators that provided equivalent functionality for fields of type multi-select. The definition for the functions is:

boolean includes(Column column, String param)
boolean excludes(Column column, String param)

For example, take a single multi-select picklist column called Status that contains all of these values.

  • current

  • working

  • critical

For that column, all of the below are valid queries:

SELECT * FROM Issue WHERE true = includes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' );

EQ and NE criteria will pass to Salesforce as supplied. For example, these queries will not be modified by the connector.

SELECT * FROM Issue WHERE Status = 'current';
SELECT * FROM Issue WHERE Status = 'current;critical';
SELECT * FROM Issue WHERE Status != 'current;working';

The Web Services translator, known by the type name ws, exposes stored procedures for calling web services backed by a Teiid WS resource adapter. It will commonly be used with the TEXTTABLE or XMLTABLE table functions to use CSV or XML formated data.

There are ws importer settings, but it does provide metadata for dynamic vdbs.


Setting the org.teiid.CONNECTOR.WS logging context to detail will show the request/response documents as part of the log.

Teiid integration is available via a "Dynamic VDB" without the need for Teiid Designer tooling. While this mode of operation does not yet allow for the creation of view layers, the underlying sources can still be queried as if they are a single source. See the kit's "teiid-example/dynamicvdb-*" for working examples.

To build a dynamic vdb, you'll need to create a "<some-name>-vdb.xml" file. The XML file captures information about the VDB, the sources it integrate, and preferences for importing metadata.

my-vdb.xml: (The vdb-deployer.xml schema for this file is available in the schema folder under the docs with the Teiid distribution.)

<vdb name="${vdb-name}" version=${vdb-version}>

    <property name="UseConnectorMetadata" value="..."/>

    <!-- define a model fragment for each data source -->
    <model name="${model-name}">
        <property name="..." value="..." />

        <source name="${source-name}" translator-name="${translator-name}" 

    <!-- create translator instances that override default properties -->

    <translator name="${translator-name}" type="${translator-type}"/>
        <property name="..." value="..."/> 


Teiid at its core is a federated relational query engine. This query engine allows you to treat all of your data sources as one virtual database and access them in a single SQL query. This allows you to focus on building your application, not on hand-coding joins, and other relational operations, between data sources.

When the query engine receives an incoming SQL query it performs the following operations:

The logical query plan is a tree of operations used to transform data in source tables to the expected result set. In the tree, data flows from the bottom (tables) to the top (output). The primary logical operations are select (select or filter rows based on a criteria), project (project or compute column values), join , source (retrieve data from a table), sort (ORDER BY), duplicate removal (SELECT DISTINCT), group (GROUP BY), and union (UNION).

For example, consider the following query that retrieves all engineering employees born since 1970.

Logically, the data from the Employees and Departments tables are retrieved, then joined, then filtered as specified, and finally the output columns are projected. The canonical query plan thus looks like this:

Data flows from the tables at the bottom upwards through the join, through the select, and finally through the project to produce the final results. The data passed between each node is logically a result set with columns and rows.

Of course, this is what happens logically , not how the plan is actually executed. Starting from this initial plan, the query planner performs transformations on the query plan tree to produce an equivalent plan that retrieves the same results faster. Both a federated query planner and a relational database planner deal with the same concepts and many of the same plan transformations. In this example, the criteria on the Departments and Employees tables will be pushed down the tree to filter the results as early as possible.

In both cases, the goal is to retrieve the query results in the fastest possible time. However, the relational database planner does this primarily by optimizing the access paths in pulling data from storage.

In contrast, a federated query planner is less concerned about storage access because it is typically pushing that burden to the data source. The most important consideration for a federated query planner is minimizing data transfer.

A special optimization called a dependent join is used to reduce the rows returned from one of the two relations involved in a multi-source join. In a dependent join, queries are issued to each source sequentially rather than in parallel, with the results obtained from the first source used to restrict the records returned from the second. Dependent joins can perform some joins much faster by drastically reducing the amount of data retrieved from the second source and the number of join comparisons that must be performed.

The conditions when a dependent join is used are determined by the query planner based on access patterns, hints, and costing information.

Teiid supports the MAKEDEP and MAKENOTDEP hints. Theses are can be placed in either the OPTION clause or directly in the FROM clause . As long as all can be met, the MAKEDEP and MAKENOTDEP hints override any use of costing information.


The MAKEDEP hint should only be used if the proper query plan is not chosen by default. You should ensure that your costing information is representative of the actual source cardinality. An inappropriate MAKEDEP hint can force an inefficient join structure and may result in many source queries.

Teiid provides the capability to obtain "partial results" in the event of data source unavailability. This is especially useful when unioning information from multiple sources, or when doing a left outer join, where you are 'appending' columns to a master record but still want the record if the extra info is not available.

If one or more data sources are unavailable to return results, then the result set obtained from the remaining available sources will be returned. In the case of joins, an unavailable data source essentially contributes zero tuples to the result set.

A source is considered to be 'unavailable' if the connection factory associated with the source issues an exception in response to a query. The exception will be propagated to the query processor, where it will become a warning in the result set.

For each source that is excluded from a query, a warning will be generated describing the source and the failure. These warnings can be obtained from the Statement.getWarnings() method. This method returns a SQLWarning object but in the case of "partial results" warnings, this will be an object of type org.teiid.jdbc.PartialResultsWarning. This class can be used to obtain a list of all the failed connectors by name and to obtain the specific exception thrown by each connector.

When integrating information using a federated query planner, it is useful to be able to view the query plans that are created, to better understand how information is being accessed and processed, and to troubleshoot problems.

A query plan is a set of instructions created by a query engine for executing a command submitted by a user or application. The purpose of the query plan is to execute the user's query in as efficient a way as possible.

Relational plans represent the actually processing plan that is composed of nodes that are the basic building blocks of logical relational operations. Physical relational plans differ from logical relational plans in that they will contain additional operations and execution specifics that were chosen by the optimizer.

The nodes for a relational query plan are:

Every node has a set of statistics that are output. These can be used to determine the amount of data flowing through the node.




Node Output Rows

Number of records output from the node


Node Process Time

Time processing in this node only


Node Cumulative Process Time

Elapsed time from beginning of processing to end


Node Cumulative Next Batch Process Time

Time processing in this node + child nodes


Node Next Batch Calls

Number of times a node was called for processing


Node Blocks

Number of times a blocked exception was thrown by this node or a child


In addition to node statistics, some nodes display cost estimates computed at the node.

Cost Estimates



Estimated Node Cardinality

Estimated number of records that will be output from the node; -1 if unknown


For each sub-command in the user command an appropriate kind of sub-planner is used (relational, XML, procedure, etc).

Each planner has three primary phases:

The GenerateCanonical class generates the initial (or “canonical” plan).  This plan is based on the typical logical order that a SQL query gets executed.  A SQL select query has the following possible clauses (all but SELECT are optional):  SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.  These clauses are logically executed in the following order:

These clause translate into the following types of planning nodes:

There is also a Null Node that can be created as the result of rewrite or planning optimizations. It represents a node that produces no rows

Relational optimization is based upon rule execution that evolves the initial plan into the execution plan.  There are a set of pre-defined rules that are dynamically assembled into a rule stack for every query.  The rule stack is assembled based on the contents of the user’s query and its transformations.  For example, if there are no view layers, then RuleMergeVirtual, which merges view layers together, is not needed and will not be added to the stack.  This allows the rule stack to reflect the complexity of the query.

Logically the plan node data structure represents a tree of nodes where the source data comes up from the leaf nodes (typically Access nodes in the final plan), flows up through the tree and produces the user’s results out the top.  The nodes in the plan structure can have bidirectional links, dynamic properties, and allow any number of child nodes.  Processing plan nodes in contrast typical have fixed properties, and only allow for binary operations - due to algorithmic limitations.

Below are some of the rules included in the planner:

  • RuleRemoveSorts - removes sort nodes that do not have an effect on the result.  This most common when a view has an non-limited ORDER BY.

  • RulePlaceAccess - insert an Access node above every physical Source node.  The source node represents a table typically.  An access node represents the point at which everything below the access node gets pushed to the source.  Later rules focus on either pushing stuff under the access or pulling the access node up the tree to move more work down to the data sources.  This rule is also responsible for placing .

  • RulePushSelectCriteria - pushes select criteria down through unions, joins, and views into the source below the access node.  In most cases movement down the tree is good as this will filter rows earlier in the plan.  We currently do not undo the decisions made by PushSelectCriteria.  However in situations where criteria cannot be evaluated by the source, this can lead to sub optimal plans.

    One of the most important optimization related to pushing criteria, is how the criteria will be pushed trough join.  Consider the following plan tree that represents a subtree of the plan for the query "select ... from A inner join b on (A.x = B.x) where A.y = 3"

              SELECT (B.y = 3)
              JOIN - Inner Join on (A.x = B.x
             /     \    
          SRC (A)   SRC (B)

    Note: SELECT nodes represent criteria, and SRC stands for SOURCE.

    It is always valid for inner join and cross joins to push (single source) criteria that are above the join, below the join.  This allows for criteria originating in the user query to eventually be present in source queries below the joins.  This result can be represented visually as:

              JOIN - Inner Join on (A.x = B.x)
              /    \
             /   SELECT (B.y = 3)
            |        |
          SRC (A)   SRC (B)

    The same optimization is valid for criteria specified against the outer side of an outer join.  For example:

              SELECT (B.y = 3) 
              JOIN - Right Outer Join on (A.x = B.x)
             /     \    
          SRC (A)   SRC (B)


              JOIN - Right Outer Join on (A.x = B.x)
              /    \
             /   SELECT (B.y = 3)
            |        |
          SRC (A)   SRC (B)

    However criteria specified against the inner side of an outer join needs special consideration.  The above scenario with a left or full outer join is not the same.  For example:

              SELECT (B.y = 3)
              JOIN - Left Outer Join on (A.x = B.x)
             /     \    
          SRC (A)   SRC (B)

    Can become (available only after 5.0.2):

              JOIN - Inner Join on (A.x = B.x)
              /    \
             /   SELECT (B.y = 3)
            |        |
          SRC (A)   SRC (B)

    Since the criterion is not dependent upon the null values that may be populated from the inner side of the join, the criterion is eligible to be pushed below the join – but only if the join type is also changed to an inner join.  

    On the other hand, criteria that are dependent upon the presence of null values CANNOT be moved.  For example:

              SELECT (B.y is null)
              JOIN - Left Outer Join on (A.x = B.x)
             /     \   
          SRC (A)   SRC (B)

    This plan tree must have the criteria remain above the join, since the outer join may be introducing null values itself.  This will be true regardless of which version of Teiid is used.

  • RulePushNonJoinCriteria – this rule will push criteria out of an on clause if it is not necessary for the correctness of the join.

  • RuleRaiseNull – this rule will raise null nodes to their highest possible point.  Raising a null node removes the need to consider any part of the old plan that was below the null node.

  • RuleMergeVirtual - merges view layers together.  View layers are connected by nesting canonical plans under source leaf nodes of the parent plan.  Each canonical plan is also sometimes referred to as a “query frame”.  RuleMergeVirtual attempts to merge child frames into the parent frame.   The merge involves renaming any symbols in the lower frame that overlap with symbols in the upper frame.  It also involves merging the join information together.

  • RuleRemoveOptionalJoins – removes optional join nodes form the plan tree as soon as possible so that planning will be more optimal.

  • RulePlanJoins – this rule attempts to find an optimal ordering of the joins performed in the plan, while ensuring that dependencies are met.  This rule has three main steps.  First it must determine an ordering of joins that satisfy the present.  Second it will heuristically create joins that can be pushed to the source (if a set of joins are pushed to the source, we will not attempt to create an optimal ordering within that set.  More than likely it will be sent to the source in the non-ANSI multi-join syntax and will be optimized by the database).  Third it will use costing information to determine the best left-linear ordering of joins performed in the processing engine.  This third step will do an exhaustive search for 6 or less join sources and is heuristically driven by join selectivity for 7 or more sources.

  • RuleCopyCriteria - this rule copies criteria over an equality criteria that is present in the criteria of a join.  Since the equality defines an equivalence, this is a valid way to create a new criteria that may limit results on the other side of the join (especially in the case of a multi-source join).  

  • RuleCleanCriteria - this rule cleans up criteria after all the other rules.

  • RuleMergeCriteria - looks for adjacent criteria nodes and merges them together.  It looks for adjacent identical conjuncts and removes duplicates.  

  • RuleRaiseAccess - this rule attempts to raise the Access nodes as far up the plan as possible.  This is mostly done by looking at the source’s capabilities and determining whether the operations can be achieved in the source or not.

  • RuleChooseDependent - this rule looks at each join node and determines whether the join should be made dependent and in which direction.  Cardinality, the number of distinct values, and primary key information are used in several formulas to determine whether a dependent join is likely to be worthwhile.  The dependent join differs in performance ideally because a fewer number of values will be returned from the dependent side.  Also, we must consider the number of values passed from independent to dependent side.  If that set is larger than the max number of values in an IN criteria on the dependent side, then we must break the query into a set of queries and combine their results.  Executing each query in the connector has some overhead and that is taken into account.  Without costing information a lot of common cases where the only criteria specified is on a non-unique (but strongly limiting) field are missed.  A join is eligible to be dependent if:

    1. there is at least one equi-join criterion, i.e. tablea.col = tableb.col

    2. the join is not a full outer join and the dependent side of the join is on the inner side of the join

    The join will be made dependent if one of the following conditions, listed in precedence order, holds:

    1. There is an unsatisfied access pattern that can be satisfied with the dependent join criteria

    2. The potential dependent side of the join is marked with an option makedep

    3. (4.3.2) if costing was enabled, the estimated cost for the dependent join (5.0+ possibly in each direction in the case of inner joins) is computed and compared to not performing the dependent join.  If the costs were all determined (which requires all relevant table cardinality, column ndv, and possibly nnv values to be populated) the lowest is chosen.

    4. If key metadata information indicates that the potential dependent side is not “small” and the other side is “not small” or (5.0.1) the potential dependent side is the inner side of a left outer join.

    Dependent join is the key optimization we use to efficiently process multi-source joins.

    Instead of reading all of source A and all of source B and joining them on A.x = B.x, we read all of A then build a set of A.x that are passed as a criteria when querying B.  In cases where A is small and B is large, this can drastically reduce the data retrieved from B, thus greatly speeding the overall query.

  • RuleChooseJoinStrategy – Determines the base join strategy.  Currently this is a decision as to whether to use a merge join rather than the default strategy, which is a nested loop join.  Ideally the choice of a hash join would also be evaluated here.  Also costing should be used to determine the strategy cost.  

  • RuleCollapseSource - this rule removes all nodes below an Access node and collapses them into an equivalent query that is placed in the Access node.

  • RuleAssignOutputElements - this rule walks top down through every node and calculates the output columns for each node.  Columns that are not needed are dropped at every node.  This is done by keeping track of both the columns needed to feed the parent node and also keeping track of columns that are “created” at a certain node.

  • RuleValidateWhereAll - this rule validates a rarely used model option.

  • RuleAccessPatternValidation – validates that all access patterns have been satisfied.

The XML Planner creates an XML plan that is relatively close to the end result of the Procedure Planner – a program with instructions.  Many of the instructions are even similar (while loop, execute SQL, etc). Additional instructions deal with producing the output result document (adding elements and attributes).  

The XML planner does several types of planning (not necessarily in this order):

- Document selection - determine which tags of the virtual document should be excluded from the output document.  This is done based on a combination of the model (which marks parts of the document excluded) and the query (which may specify a subset of columns to include in the SELECT clause).  

- Criteria evaluation - breaks apart the user’s criteria, determine which result set the criteria should be applied to, and add that criteria to that result set query.

- Result set ordering - the query’s ORDER BY clause is broken up and the ORDER BY is applied to each result set as necessary

- Result set planning - ultimately, each result set is planned using the relational planner and taking into account all the impacts from the user’s query

- Program generation - a set of instructions to produce the desired output document is produced, taking into account the final result set queries and the excluded parts of the document.  Generally, this involves walking through the virtual document in document order, executing queries as necessary and emitting elements and attributes.

XML programs can also be recursive, which involves using the same document fragment for both the initial fragment and a set of repeated fragments (each a new query) until some termination criteria or limit is met.

<DEFAULT> SKIP : { " " | "\t" | "\n" | "\r" }




<DEFAULT> TOKEN : { <STRING: "string"> | <VARCHAR: "varchar"> | <BOOLEAN: "boolean"> | <BYTE: "byte"> | <TINYINT: "tinyint"> | <SHORT: "short"> | <SMALLINT: "smallint"> | <CHAR: "char"> | <INTEGER: "integer"> | <LONG: "long"> | <BIGINT: "bigint"> | <BIGINTEGER: "biginteger"> | <FLOAT: "float"> | <REAL: "real"> | <DOUBLE: "double"> | <BIGDECIMAL: "bigdecimal"> | <DECIMAL: "decimal"> | <DATE: "date"> | <TIME: "time"> | <TIMESTAMP: "timestamp"> | <OBJECT: "object"> | <BLOB: "blob"> | <CLOB: "clob"> | <XML: "xml"> }

<DEFAULT> TOKEN : { <CAST: "cast"> | <CONVERT: "convert"> }

<DEFAULT> TOKEN : { <ADD: "add"> | <ALL: "all"> | <ALTER: "alter"> | <AND: "and"> | <ANY: "any"> | <ARRAY: "array"> | <AS: "as"> | <ASC: "asc"> | <ATOMIC: "atomic"> | <AUTORIZATION: "authorization"> | <BEGIN: "begin"> | <BETWEEN: "between"> | <BINARY: "binary"> | <BOTH: "both"> | <BREAK: "break"> | <BY: "by"> | <CALL: "call"> | <CALLED: "called"> | <CASCADED: "cascaded"> | <CASE: "case"> | <CHARACTER: "character"> | <CHECK: "check"> | <CLOSE: "close"> | <COLLATE: "collate"> | <COLUMN: "column"> | <COMMIT: "commit"> | <CONNECT: "connect"> | <CONSTRAINT: "constraint"> | <CONTINUE: "continue"> | <CORRESPONDING: "corresponding"> | <CURRENT_DATE: "current_date"> | <CURRENT_TIME: "current_time"> | <CURRENT_TIMESTAMP: "current_timestamp"> | <CURRENT_USER: "current_user"> | <CREATE: "create"> | <CRITERIA: "criteria"> | <CROSS: "cross"> | <CURSOR: "cursor"> | <DAY: "day"> | <DEALLOCATE: "deallocate"> | <DEFAULT_KEYWORD: "default"> | <DECLARE: "declare"> | <DELETE: "delete"> | <DESC: "desc"> | <DESCRIBE: "describe"> | <DETERMINISTIC: "deterministic"> | <DISCONNECT: "disconnect"> | <DISTINCT: "distinct"> | <DROP: "drop"> | <ELSE: "else"> | <END: "end"> | <ERROR: "error"> | <ESCAPE: "escape"> | <EXCEPT: "except"> | <EXEC: "exec"> | <EXECUTE: "execute"> | <EXTERNAL: "external"> | <EXISTS: "exists"> | <FALSE: "false"> | <FETCH: "fetch"> | <FILTER: "filter"> | <FOR: "for"> | <FORIEGN: "foriegn"> | <FROM: "from"> | <FULL: "full"> | <FUNCTION: "function"> | <GET: "get"> | <GLOBAL: "global"> | <GRANT: "grant"> | <GROUP: "group"> | <HAS: "has"> | <HAVING: "having"> | <HOLD: "hold"> | <HOUR: "hour"> | <IF: "if"> | <IDENTITY: "identity"> | <IMMEDIATE: "immediate"> | <IN: "in"> | <INDICATOR: "indicator"> | <INNER: "inner"> | <INPUT: "input"> | <INOUT: "inout"> | <INSENSITIVE: "insensitive"> | <INSERT: "insert"> | <INTERSECT: "intersect"> | <INTERVAL: "interval"> | <INTO: "into"> | <IS: "is"> | <ISOLATION: "isolation"> | <JOIN: "join"> | <LEFT: "left"> | <LANGUAGE: "language"> | <LARGE: "large"> | <LEADING: "leading"> | <LIKE: "like"> | <LIMIT: "limit"> | <LOCAL: "local"> | <LOOP: "loop"> | <MAKEDEP: "makedep"> | <MAKENOTDEP: "makenotdep"> | <MATCH: "match"> | <MERGE: "merge"> | <METHOD: "method"> | <MINUTE: "minute"> | <MODIFIES: "modifies"> | <MODULE: "module"> | <MONTH: "month"> | <NATURAL: "natural"> | <NEW: "new"> | <NOCACHE: "nocache"> | <NO: "no"> | <NONE: "none"> | <NOT: "not"> | <NULL: "null"> | <OF: "of"> | <OLD: "old"> | <ON: "on"> | <ONLY: "only"> | <OPEN: "open"> | <OPTION: "option"> | <OR: "or"> | <ORDER: "order"> | <OUTER: "outer"> | <OUTPUT: "output"> | <OVER: "over"> | <OVERLAPS: "OVERLAPS"> | <PARAMETER: "parameter"> | <PARTITION: "partition"> | <PRECISION: "precision"> | <PREPARE: "prepare"> | <PRIMARY: "primary"> | <PROCEDURE: "procedure"> | <RANGE: "range"> | <READS: "reads"> | <RECURSIVE: "recursive"> | <REFERENCES: "REFERENCES"> | <REFERENCING: "REFERENCING"> | <RETURN: "return"> | <RETURNS: "returns"> | <REVOKE: "REVOKE"> | <RIGHT: "right"> | <ROLLBACK: "ROLLBACK"> | <ROLLUP: "ROLLUP"> | <ROW: "row"> | <ROWS: "rows"> | <SAVEPOINT: "savepoint"> | <SCROLL: "scroll"> | <SEARCH: "search"> | <SECOND: "second"> | <SELECT: "select"> | <SENSITIVE: "sensitive"> | <SESSION_USER: "session_user"> | <SET: "set"> | <SIMILAR: "similar"> | <SPECIFIC: "specific"> | <SOME: "some"> | <SQL: "sql"> | <SQLEXCEPTION: "sqlexception"> | <SQLSTATE: "sqlstate"> | <SQLWARNING: "sqlwarning"> | <START: "start"> | <STATIC: "static"> | <SYSTEM: "system"> | <SYSTEM_USER: "system_user"> | <TABLE: "table"> | <TEMPORARY: "temporary"> | <THEN: "then"> | <TIMEZONE_HOUR: "timezone_hour"> | <TIMEZONE_MINUTE: "timezone_minute"> | <TO: "to"> | <TRAILING: "trailing"> | <TRANSLATE: "translate"> | <TRIGGER: "trigger"> | <TRUE: "true"> | <UNION: "union"> | <UNIQUE: "unique"> | <UNKNOWN: "unknown"> | <USER: "user"> | <UPDATE: "update"> | <USING: "using"> | <VALUE: "value"> | <VALUES: "values"> | <VIRTUAL: "virtual"> | <WHEN: "when"> | <WHENEVER: "whenever"> | <WHERE: "where"> | <WITH: "with"> | <WHILE: "while"> | <WINDOW: "window"> | <WITHIN: "within"> | <WITHOUT: "without"> | <YEAR: "year"> | <ALLOCATE: "allocate"> | <ARE: "are"> | <ASENSITIVE: "asensitive"> | <ASYMETRIC: "asymetric"> | <CYCLE: "cycle"> | <DEC: "dec"> | <DEREF: "deref"> | <DYNAMIC: "dynamic"> | <ELEMENT: "element"> | <FREE: "free"> | <INT: "int"> | <LATERAL: "lateral"> | <LOCALTIME: "localtime"> | <LOCALTIMESTAMP: "localtimestamp"> | <MEMBER: "member"> | <MULTISET: "multiset"> | <NATIONAL: "national"> | <NCHAR: "nchar"> | <NCLOB: "nclob"> | <NUMERIC: "numeric"> | <RELEASE: "release"> | <SPECIFICTYPE: "specifictype"> | <SYMETRIC: "symetric"> | <SUBMULTILIST: "submultilist"> | <TRANSLATION: "translation"> | <TREAT: "treat"> | <VARYING: "varying"> }

<DEFAULT> TOKEN : { <XMLAGG: "xmlagg"> | <XMLATTRIBUTES: "xmlattributes"> | <XMLBINARY: "xmlbinary"> | <XMLCAST: "xmlcast"> | <XMLCONCAT: "xmlconcat"> | <XMLCOMMENT: "xmlcomment"> | <XMLDOCUMENT: "xmldocument"> | <XMLELEMENT: "xmlelement"> | <XMLEXISTS: "xmlexists"> | <XMLFOREST: "xmlforest"> | <XMLITERATE: "xmliterate"> | <XMLNAMESPACES: "xmlnamespaces"> | <XMLPARSE: "xmlparse"> | <XMLPI: "xmlpi"> | <XMLQUERY: "xmlquery"> | <XMLSERIALIZE: "xmlserialize"> | <XMLTABLE: "xmltable"> | <XMLTEXT: "xmltext"> | <XMLVALIDATE: "xmlvalidate"> }

<DEFAULT> TOKEN : { <DATALINK: "datalink"> | <DLNEWCOPY: "dlnewcopy"> | <DLPREVIOUSCOPY: "dlpreviouscopy"> | <DLURLCOMPLETE: "dlurlcomplete"> | <DLURLCOMPLETEWRITE: "dlurlcompletewrite"> | <DLURLCOMPLETEONLY: "dlurlcompleteonly"> | <DLURLPATH: "dlurlpath"> | <DLURLPATHWRITE: "dlurlpathwrite"> | <DLURLPATHONLY: "dlurlpathonly"> | <DLURLSCHEME: "dlurlscheme"> | <DLURLSERVER: "dlurlserver"> | <DLVALUE: "dlvalue"> | <IMPORT: "import"> }

<DEFAULT> TOKEN : { <ALL_IN_GROUP: <ID> <PERIOD> <STAR>> | <ID: <QUOTED_ID> (<PERIOD> <QUOTED_ID>)*> | <#QUOTED_ID: <ID_PART> | "\"" ("\"\"" | ~["\""])+ "\""> | <#ID_PART: ("@" | "#" | <LETTER>) (<LETTER> | "_" | <DIGIT>)*> | <DATETYPE: "{" "d"> | <TIMETYPE: "{" "t"> | <TIMESTAMPTYPE: "{" "ts"> | <BOOLEANTYPE: "{" "b"> | <XMLTYPE: "{" "x"> | <INTEGERVAL: (<MINUS>)? (<DIGIT>)+> | <FLOATVAL: (<MINUS>)? (<DIGIT>)* <PERIOD> (<DIGIT>)+ (["e","E"] (["+","-"])? (<DIGIT>)+)?> | <STRINGVAL: ("N")? "\'" ("\'\'" | ~["\'"])* "\'"> | <#LETTER: ["a"-"z","A"-"Z"] | ["\u0153"-"\ufffd"]> | <#DIGIT: ["0"-"9"]> }

<DEFAULT> TOKEN : { <COMMA: ","> | <PERIOD: "."> | <LPAREN: "("> | <RPAREN: ")"> | <LBRACE: "{"> | <RBRACE: "}"> | <EQ: "="> | <NE: "<>"> | <NE2: "!="> | <LT: "<"> | <LE: "<="> | <GT: ">"> | <GE: ">="> | <STAR: "*"> | <SLASH: "/"> | <PLUS: "+"> | <MINUS: "-"> | <QMARK: "?"> | <DOLLAR: "$"> | <SEMICOLON: ";"> | <CONCAT_OP: "||"> }


::= ( <STRINGVAL> )


::= ( <ID> )


::= ( createUpdateProcedure | userCommand | callableStatement ) ( <SEMICOLON> )? <EOF>


::= ( queryExpression | storedProcedure | insert | update | delete | dropTable | createTempTable )


::= <DROP> <TABLE> id


::= <CREATE> <LOCAL> <TEMPORARY> <TABLE> id <LPAREN> createElementsWithTypes <RPAREN>


::= <ERROR> expression


::= ( ifStatement | loopStatement | whileStatement | delimitedStatement )


::= ( sqlStatement | errorStatement | assignStatement | declareStatement | continueStatement | breakStatement ) <SEMICOLON>


::= ( statement | ( <BEGIN> ( statement )* <END> ) )


::= <BREAK>




::= <WHILE> <LPAREN> criteria <RPAREN> block


::= <LOOP> <ON> <LPAREN> queryExpression <RPAREN> <AS> id block


::= <IF> <LPAREN> criteria <RPAREN> block ( <ELSE> block )?


::= ( ( <EQ> | <NE> | <NE2> | <LE> | <GE> | <LT> | <GT> | <IN> | <LIKE> | ( <IS> <NULL> ) | <BETWEEN> ) )? <CRITERIA> ( <ON> <LPAREN> id ( <COMMA> id )* <RPAREN> )?


::= <HAS> criteriaSelector


::= <DECLARE> dataType id ( <EQ> assignStatementOperand )?


::= id <EQ> assignStatementOperand


::= ( ( insert ) | update | delete | storedProcedure | ( expression ) | queryExpression )


::= ( ( dynamicCommand ) | userCommand )


::= <TRANSLATE> criteriaSelector ( <WITH> <LPAREN> id <EQ> expression ( <COMMA> id <EQ> expression )* <RPAREN> )?


::= <CREATE> ( <VIRTUAL> )? ( <UPDATE> )? <PROCEDURE> block


::= ( <EXECUTE> | <EXEC> ) <STRING> expression ( <AS> createElementsWithTypes ( <INTO> id )? )? ( <USING> setClauseList )? ( <UPDATE> ( ( <INTEGERVAL> ) | ( <STAR> ) ) )?


::= id <EQ> ( <COMMA> id <EQ> )*


::= id dataType ( <COMMA> id dataType )*


::= <LBRACE> ( <QMARK> <EQ> )? <CALL> id ( <LPAREN> ( executeUnnamedParams ) <RPAREN> )? <RBRACE> ( option )?


::= ( ( ( <EXEC> ) | ( <EXECUTE> ) | ( <CALL> ) ) id <LPAREN> ( executeNamedParams | executeUnnamedParams ) <RPAREN> ) ( option )?


::= ( expression ( <COMMA> expression )* )?


::= ( id <EQ> expression ( <COMMA> id <EQ> expression )* )


::= <INSERT> <INTO> id ( <LPAREN> id ( <COMMA> id )* <RPAREN> )? ( ( <VALUES> rowValues ) | ( queryExpression ) ) ( option )?


::= <LPAREN> expression ( <COMMA> expression )* <RPAREN>


::= <UPDATE> id <SET> setClauseList ( where )? ( option )?


::= <DELETE> <FROM> id ( where )? ( option )?


::= queryExpressionBody


::= queryTerm ( ( <UNION> | <EXCEPT> ) ( <ALL> | <DISTINCT> )? queryTerm )* ( orderby )? ( limit )? ( option )?


::= queryPrimary ( <INTERSECT> ( <ALL> | <DISTINCT> )? queryPrimary )*


::= ( query | ( <LPAREN> queryExpressionBody <RPAREN> ) )


::= select ( into )? ( from ( where )? ( groupBy )? ( having )? )?


::= <INTO> ( id )


::= <SELECT> ( <ALL> | ( <DISTINCT> ) )? ( <STAR> | ( selectSymbol ( <COMMA> selectSymbol )* ) )


::= ( selectExpression | allInGroupSymbol )


::= ( expression ( ( <AS> )? id )? )


::= ( expression ( <AS> id )? )




::= <XMLAGG> <LPAREN> expression ( orderby )? <RPAREN>


::= ( ( nonReserved <LPAREN> <STAR> <RPAREN> ) | ( nonReserved <LPAREN> ( <DISTINCT> )? expression <RPAREN> ) )


::= <FROM> ( tableReference ( <COMMA> tableReference )* )


::= ( ( <LBRACE> nonReserved tableReferenceUnescaped <RBRACE> ) | tableReferenceUnescaped )


::= ( joinedTable | tablePrimary )


::= tablePrimary ( ( crossJoin | qualifiedJoin ) )+


::= ( ( <CROSS> | <UNION> ) <JOIN> tablePrimary )


::= ( ( ( <RIGHT> ( <OUTER> )? ) | ( <LEFT> ( <OUTER> )? ) | ( <FULL> ( <OUTER> )? ) | <INNER> )? <JOIN> tableReference <ON> criteria )


::= ( textTable | xmlTable | unaryFromClause | subqueryFromClause | ( <LPAREN> joinedTable <RPAREN> ) ) ( ( <MAKEDEP> ) | ( <MAKENOTDEP> ) )?


::= <XMLSERIALIZE> <LPAREN> nonReserved expression ( <AS> ( <STRING> | <VARCHAR> | <CLOB> ) )? <RPAREN>


::= <ID>


::= <ID> <LPAREN> expression nonReserved textColumn ( <COMMA> textColumn )* ( <ID> charVal )? ( ( <ESCAPE> charVal ) | ( <ID> charVal ) )? ( <ID> ( intVal )? )? ( <ID> intVal )? <RPAREN> ( <AS> )? id


::= id dataType ( <ID> intVal )?


::= <XMLQUERY> <LPAREN> ( xmlNamespaces <COMMA> )? stringVal ( <ID> derivedColumn ( <COMMA> derivedColumn )* )? ( ( <NULL> | nonReserved ) <ON> nonReserved )? <RPAREN>


::= <XMLTABLE> <LPAREN> ( xmlNamespaces <COMMA> )? stringVal ( <ID> derivedColumn ( <COMMA> derivedColumn )* )? ( <ID> xmlColumn ( <COMMA> xmlColumn )* )? <RPAREN> ( <AS> )? id


::= id ( ( <FOR> nonReserved ) | ( dataType ( <DEFAULT_KEYWORD> expression )? ( nonReserved stringVal )? ) )




::= ( <TABLE> )? <LPAREN> ( queryExpression | storedProcedure ) <RPAREN> ( <AS> )? id


::= ( <ID> ( ( <AS> )? id )? )


::= <WHERE> criteria


::= compoundCritOr


::= compoundCritAnd ( <OR> compoundCritAnd )*


::= notCrit ( <AND> notCrit )*


::= ( <NOT> )? primary


::= ( predicate | ( <LPAREN> criteria <RPAREN> ) )


::= ( subqueryCompareCriteria | compareCrit | matchCrit | betweenCrit | setCrit | existsCriteria | hasCriteria | translateCriteria | isNullCrit )


::= expression ( <EQ> | <NE> | <NE2> | <LT> | <LE> | <GT> | <GE> ) expression


::= <LPAREN> ( queryExpression | storedProcedure ) <RPAREN>


::= expression ( <EQ> | <NE> | <NE2> | <LT> | <LE> | <GT> | <GE> ) ( <ANY> | <SOME> | <ALL> ) subquery


::= ( expression ( <NOT> )? <LIKE> expression ( <ESCAPE> charVal | ( <LBRACE> <ESCAPE> charVal <RBRACE> ) )? )


::= stringVal


::= expression ( <NOT> )? <BETWEEN> expression <AND> expression


::= expression <IS> ( <NOT> )? <NULL>


::= expression ( <NOT> )? <IN> ( ( subquery ) | ( <LPAREN> expression ( <COMMA> expression )* <RPAREN> ) )


::= <EXISTS> subquery


::= <GROUP> <BY> ( groupByItem ( <COMMA> groupByItem )* )


::= expression


::= <HAVING> criteria


::= <ORDER> <BY> sortKey ( <ASC> | <DESC> )? ( <COMMA> sortKey ( <ASC> | <DESC> )? )*


::= expression




::= <OPTION> ( <MAKEDEP> id ( <COMMA> id )* | <MAKENOTDEP> id ( <COMMA> id )* | <NOCACHE> ( id ( <COMMA> id )* )? )*


::= concatExpression


::= ( plusExpression ( <CONCAT_OP> plusExpression )* )


::= ( timesExpression ( plusOperator timesExpression )* )


::= ( <PLUS> | <MINUS> )


::= ( basicExpression ( timesOperator basicExpression )* )


::= ( <STAR> | <SLASH> )


::= ( <QMARK> | literal | ( <LBRACE> nonReserved function <RBRACE> ) | ( aggregateSymbol ) | ( xmlAgg ) | ( function ) | ( <ID> ) | ( <LPAREN> expression <RPAREN> ) | subquery | caseExpression | searchedCaseExpression )


::= <CASE> expression ( <WHEN> expression <THEN> expression )+ ( <ELSE> expression )? <END>


::= <CASE> ( <WHEN> criteria <THEN> expression )+ ( <ELSE> expression )? <END>


::= ( ( <CONVERT> <LPAREN> expression <COMMA> dataType <RPAREN> ) | ( <CAST> <LPAREN> expression <AS> dataType <RPAREN> ) | ( nonReserved <LPAREN> intervalType <COMMA> expression <COMMA> expression <RPAREN> ) | queryString | ( ( <LEFT> | <RIGHT> | <CHAR> | <USER> | <YEAR> | <MONTH> | <HOUR> | <MINUTE> | <SECOND> | <XMLCONCAT> | <XMLCOMMENT> ) <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) | ( ( <INSERT> ) <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) | ( ( <TRANSLATE> ) <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) | xmlParse | xmlElement | ( <XMLPI> <LPAREN> ( <ID> idExpression | idExpression ) ( <COMMA> expression )? <RPAREN> ) | xmlForest | xmlSerialize | xmlQuery | ( id <LPAREN> ( expression ( <COMMA> expression )* )? <RPAREN> ) )


::= <XMLPARSE> <LPAREN> nonReserved expression ( nonReserved )? <RPAREN>


::= nonReserved <LPAREN> expression ( <COMMA> derivedColumn )* <RPAREN>


::= <XMLELEMENT> <LPAREN> ( <ID> id | id ) ( <COMMA> xmlNamespaces )? ( <COMMA> xmlAttributes )? ( <COMMA> expression )* <RPAREN>


::= <XMLATTRIBUTES> <LPAREN> derivedColumn ( <COMMA> derivedColumn )* <RPAREN>


::= <XMLFOREST> <LPAREN> ( xmlNamespaces <COMMA> )? derivedColumn ( <COMMA> derivedColumn )* <RPAREN>


::= <XMLNAMESPACES> <LPAREN> namespaceItem ( <COMMA> namespaceItem )* <RPAREN>


::= ( stringVal <AS> id )


::= ( <DEFAULT_KEYWORD> stringVal )


::= id




::= ( nonReserved )