2.6. DML Clauses 2.6.1. DML Clauses DML clauses are used in various SQL commands (see Section 2.5.1, "DML Commands" ) to specify particular relations and how to present them. Nearly all these features follow standard SQL syntax and functionality, so any SQL reference can be used for more information. 2.6.2. WITH Clause JBoss Data Virtualization supports non-recursive common table expressions via the WITH clause. WITH clause items may be referenced as tables in subsequent WITH clause items and in the main query. The WITH clause can be thought of as providing query-scoped temporary tables. Usage: Syntax Rules: All of the projected column names must be unique. If they are not unique, then the column name list must be provided. If the columns of the WITH clause item are declared, then they must match the number of columns projected by the query expression. Each WITH clause item must have a unique name. Note The WITH clause is also subject to optimization and its entries may not be processed if they are not needed in the subsequent query. 2.6.3. Recursive Common Table Expressions A recursive common table expression is a special form of a common table expression that is allowed to refer to itself to build the full common table result in a recursive or iterative fashion. The recursive query expression is allowed to refer to the common table by name. Processing flows with the anchor query expression executed first. The results are added to the common table and are referenced for the execution of the recursive query expression. The process is repeated against the new results until there are no more intermediate results. Important A non-terminating recursive common table expression can lead to excessive processing. To prevent runaway processing of a recursive common table expression, processing is by default limited to 10000 iterations. Recursive common table expressions that are pushed down are not subject to this limit, but may be subject to other source-specific limits. The limit can be modified by setting the session variable teiid.maxRecusion to a larger integer value. Once the maximum has been exceeded, an exception is thrown. This fails because the recursion limit is reached before processing completes: 2.6.4. SELECT Clause SQL queries start with the SELECT keyword and are often referred to as "SELECT statements". JBoss Data Virtualization supports most of the standard SQL query constructs. Usage: Syntax Rules: Aliased expressions are only used as the output column names and in the ORDER BY clause. They cannot be used in other clauses of the query. DISTINCT may only be specified if the SELECT symbols are comparable. 2.6.5. FROM Clause The FROM clause specifies the target table(s) for SELECT, UPDATE, and DELETE statements. Example Syntax: FROM table [[AS] alias] FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria FROM table1 CROSS JOIN table2 FROM (subquery) [AS] alias FROM TABLE(subquery) [AS] alias Note See Section 2.6.7, "Nested Tables" . FROM table1 JOIN /*+ MAKEDEP */ table2 ON join-criteria FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join-criteria FROM /*+ MAKEIND */ table1 JOIN table2 ON join-criteria FROM /*+ NO_UNNEST */ vw1 JOIN table2 ON join-criteria FROM table1 left outer join /*+ optional */ table2 ON join-criteria Note See Section 2.5.10, "Subqueries" . FROM TEXTTABLE... Note See Section 2.6.8, "Nested Tables: TEXTTABLE" . FROM XMLTABLE... Note See Section 2.6.9, "Nested Tables: XMLTABLE" . FROM ARRAYTABLE... Note See Section 2.6.10, "Nested Tables: ARRAYTABLE" . FROM OBJECTTABLE... Note See Section 2.6.11, "Nested Tables: OBJECTTABLE" . FROM (SELECT ...) Note See Section 2.5.10, "Subqueries" . 2.6.6. FROM Clause Hints From clause hints are typically specified in a comment block. If multiple hints apply, they should be placed in the same comment block. For example: Dependent Joins Hints MAKEIND, MAKEDEP, and MAKENOTDEP are hints used to control dependent join behavior (see Section 13.7.3, "Dependent Joins" ). 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 hints may appear in a comment following the FROM keyword. The hints can be specified against any FROM clause, not just a named table. NO_UNNEST NO_UNNEST can be specified against a FROM clause or view to instruct the planner not to merge the nested SQL in the surrounding query - also known as view flattening. This hint only applies to JBoss Data Virtualization planning and is not passed to source queries. NO_UNNEST may appear in a comment following the FROM keyword. PRESERVE The PRESERVE hint can be used against an ANSI join tree to preserve the structure of the join rather than allowing the JBoss Data Virtualization optimizer to reorder the join. This is similar in function to the Oracle ORDERED or MySQL STRAIGHT_JOIN hints. 2.6.7. Nested Tables Nested tables may appear in the FROM clause with the TABLE keyword. They are an alternative to using a view with normal join semantics. The columns projected from the command contained in the nested table may be used just as any of the other FROM clause projected columns in join criteria, the where clause, etc. A nested table may have correlated references to preceding FROM clause column references as long as INNER and LEFT OUTER joins are used. This is especially useful in cases where the nested expression is a procedure or function call. Valid example: select * from t1, TABLE(call proc(t1.x)) t2 Invalid example, since t1 appears after the nested table in the FROM clause: select * from TABLE(call proc(t1.x)) t2, t1 Note The usage of a correlated nested table may result in multiple executions of the table expression - once for each correlated row. 2.6.8. Nested Tables: TEXTTABLE The TEXTTABLE function processes character input to produce tabular output. 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 used within FROM clauses. Parameters expression is the text content to process, which should be convertible to CLOB. SELECTOR specifies that delimited lines should only match if the line begins with the selector string followed by a delimiter. The selector value is a valid column value. If a TEXTTABLE SELECTOR is specified, a SELECTOR may also be specified for column values. A column SELECTOR argument will select the nearest preceding text line with the given SELECTOR prefix and select the value at the given 1-based integer position (which includes the selector itself). If no such text line or position with a given line exists, a null value will be produced. NO ROW DELIMITER indicates that fixed parsing should not assume the presence of newline row delimiters. DELIMITER sets the field delimiter character to use. Defaults to ','. QUOTE sets the quote, or qualifier, character used to wrap field values. Defaults to '"'. ESCAPE sets the escape character to use if no quoting character is in use. This is used in situations where the delimiter or new line characters are escaped with a preceding character, e.g. \, HEADER specifies the text line number (counting every new line) on which the column names occur. All lines prior to the header will be skipped. If HEADER is specified, then the header line will be used to determine the TEXTTABLE column position by case-insensitive name matching. This is especially useful in situations where only a subset of the columns are needed. If the HEADER value is not specified, it defaults to 1. If HEADER is not specified, then columns are expected to match positionally with the text contents. SKIP specifies the number of text lines (counting every new line) to skip before parsing the contents. You can still specify a HEADER with SKIP. A FOR ORDINALITY column is typed as integer and will return the 1-based item number as its value. WIDTH indicates the fixed-width length of a column in characters - not bytes. The CR NL newline value counts as a single character. NO TRIM specifies that the text value should not be trimmed of all leading and trailing white space. Syntax Rules: If width is specified for one column it must be specified for all columns and be a non-negative integer. If width is specified, then fixed width parsing is used and ESCAPE, QUOTE, and HEADER should not be specified. If width is not specified, then NO ROW DELIMITER cannot be used. The column names must not contain duplicates. Examples Use of the HEADER parameter, returns 1 row ['b']: SELECT * FROM TEXTTABLE(UNESCAPE('col1,col2,col3\na,b,c') COLUMNS col2 string HEADER) x Use of fixed width, returns 2 rows ['a', 'b', 'c'], ['d', 'e', 'f']: SELECT * FROM TEXTTABLE(UNESCAPE('abc\ndef') COLUMNS col1 string width 1, col2 string width 1, col3 string width 1) x Use of fixed width without a row delimiter, returns 3 rows ['a'], ['b'], ['c']: SELECT * FROM TEXTTABLE('abc' COLUMNS col1 string width 1 NO ROW DELIMITER) x Use of ESCAPE parameter, returns 1 row ['a,', 'b']: SELECT * FROM TEXTTABLE('a:,,b' COLUMNS col1 string, col2 string ESCAPE ':') x As a nested table: SELECT x.* FROM t, TEXTTABLE(t.clobcolumn COLUMNS first string, second date SKIP 1) x Use of SELECTOR, returns 2 rows ['c', 'd', 'b'], ['c', 'f', 'b']: 2.6.9. Nested Tables: XMLTABLE The XMLTABLE function uses XQuery to produce tabular output. The XMLTABLE function is implicitly a nested table and may be used within FROM clauses. XMLTABLE is part of the SQL/XML 2006 specification. Usage: See XMLELEMENT for the definition of NSP - XMLNAMESPACES. See XMLQUERY for the definition of PASSING. Note See also XQuery Optimization. Parameters The optional XMLNAMESPACES clause specifies the namespaces for use in the XQuery and COLUMN path expressions. The xquery-expression must 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. A FOR ORDINALITY column is typed as integer and will return the one-based item number as its 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. Syntax Rules: Only 1 FOR ORDINALITY column may be specified. The columns names must not contain duplicates. The blob data type is supported, but there is only built-in support for xs:hexBinary values. For xs:base64Binary, use a workaround of a PATH that uses the explicit value constructor "xs:base64Binary(<path>)". Examples Use of passing, returns 1 row [1]: select * from xmltable('/a' PASSING xmlparse(document '<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 2.6.10. Nested Tables: ARRAYTABLE The ARRAYTABLE function processes an array input to produce tabular output. The function itself defines what columns it projects. The ARRAYTABLE function is implicitly a nested table and may be used within FROM clauses. Usage: ARRAYTABLE(expression COLUMNS <COLUMN>, ...) AS name COLUMN := name datatype Parameters expression - the array to process, which should be a java.sql.Array or java array value. Syntax Rules: The columns names must not contain duplicates. Examples As a nested table: ARRAYTABLE is effectively a shortcut for using the array_get function (see Section 2.4.19, "Miscellaneous Functions" ) in a nested table. For example: is the same as 2.6.11. Nested Tables: OBJECTTABLE The OBJECTTABLE function processes an object input to produce tabular output. The function itself defines what columns it projects. The OBJECTTABLE function is implicitly a nested table and may be correlated to preceding FROM clause entries. Usage: Parameters lang - an optional string literal that is the case sensitive language name of the scripts to be processed. The script engine must be available via a JSR-223 ScriptEngineManager lookup. In some instances this may mean making additional modules available to your VDB, which can be done via the same process as adding modules/libraries for UDFs (see Non-Pushdown Support for User-Defined Functions in the Development Guide: Server Development ). If a LANGUAGE is not specified, the default of 'teiid_script' (see below) will be used. name - an identifier that will bind the val expression value into the script context. rowScript is a string literal specifying the script to create the row values. For each non-null item the Iterator produces the columns will be evaluated. colName/colType are the id/data type of the column, which can optionally be defaulted with the DEFAULT clause expression defaultExpr. colScript is a string literal specifying the script that evaluates to the column value. Syntax Rules: The column names must be not contain duplicates. JBoss Data Virtualization will place several special variables in the script execution context. The CommandContext is available as teiid_context. Additionally the colScripts may access teiid_row and teiid_row_number. teiid_row is the current row object produced by the row script. teiid_row_number is the current 1-based row number. rowScript is evaluated to an Iterator. If the results is already an Iterator, it is used directly. If the evaluation result is an Iteratable, then an Iterator will be obtained. Any other Object will be treated as an Iterator of a single item). In all cases null row values will be skipped. Note While there is no restriction what can be used as a PASSING variable names you should choose names that can be referenced as identifiers in the target language. Examples Accessing special variables: The result would be a row with two columns containing the user name and 1 respectively. Note Due to their mostly unrestricted access to Java functionality, usage of languages other than teiid_script is restricted by default. A VDB must declare all allowable languages by name in the allowed-languages VDB property (see Section 6.1, "VDB Definition" ) using a comma separated list. The names are case sensitive names and should be separated without whitespace. Without this property it is not possible to use OBJECTTABLE even from within view definitions that are not subject to normal permission checks. Data Roles are also secured with User Query Permissions. teiid_script teiid_script is a simple scripting expression language that allows access to passing and special variables as well as any non-void 0-argument methods on objects. A teiid_script expression begins by referencing the passing or special variable. Then any number of .method accessors may be chained to evaluate the expression to a different value. Methods may be accessed by their property names, for example foo rather than getFoo. If the object both a getFoo() and foo() method, then the accessor foo references foo() and getFoo should be used to call the getter. teiid_script is effectively dynamically typed as typing is performed at runtime. If a accessor does not exist on the object or if the method is not accessible, then an exception will be raised. Examples To get the VDB description string: 2.6.12. WHERE Clause The WHERE clause defines the criteria to limit the records affected by SELECT, UPDATE, and DELETE statements. Usage: See Also: Section 2.3.11, "Criteria" 2.6.13. GROUP BY Clause The GROUP BY clause denotes that rows should be grouped according to the specified expression values. One row will be returned for each group, after optionally filtering those aggregate rows based on a HAVING clause. Usage: Syntax Rules: Column references in the GROUP BY clause must be unaliased output columns. Expressions used in the GROUP BY clause must appear in the SELECT clause. Column references and expressions in the SELECT clause that are not used in the GROUP BY clause must appear in aggregate functions. If an aggregate function is used in the SELECT clause and no GROUP BY is specified, an implicit GROUP BY will be performed with the entire result set as a single group. In this case, every column in the SELECT must be an aggregate function as no other column value will be fixed across the entire group. The group by columns must be of a comparable type. Just like normal grouping, rollup processing logically occurs before the HAVING clause is processed. A ROLLUP of expressions will produce the same output as a regular grouping with the addition of aggregate values computed at higher aggregation levels. For N expressions in the ROLLUP , aggregates will be provided over (), (expr1), (expr1, expr2) and so on, up to (expr1, ... exprN-1) with the other grouping expressions in the output as null values. Here is an example using the normal aggregation query: This is what is returned: Table 2.10. Returned Data Country City Amount US St Louis 10000 US Raleigh 150000 US Denver 20000 UK Birmingham 50000 UK London 75000 In contrast, here is the rollup query: This is what it returns: Table 2.11. Returned Data from Rollup Country City Amount US St Louis 10000 US Raleigh 150000 US Denver 20000 US Null 180000 UK Birmingham 50000 UK London 75000 UK Null 125000 Note Not all sources support ROLLUPs and some optimizations compared to normal aggregate processing may be inhibited by the use of a ROLLUP. Note Support for ROLLUPs in Red Hat JBoss Data Virtualization is currently limited, compared to the SQL specification. 2.6.14. HAVING Clause The HAVING clause operates exactly as a WHERE clause although it operates on the output of a GROUP BY. It supports the same syntax as the WHERE clause. Syntax Rules: Expressions used in the GROUP BY clause must either contain an aggregate function: COUNT, AVG, SUM, MIN, MAX. or be one of the grouping expressions. 2.6.15. ORDER BY Clause The ORDER BY clause specifies how records should be sorted. The options are ASC (ascending) and DESC (descending). Usage: Syntax Rules: Sort columns may be specified positionally by a 1-based positional integer, by SELECT clause alias name, by SELECT clause expression, or by an unrelated expression. Column references may appear in the SELECT clause as the expression for an aliased column or may reference columns from tables in the FROM clause. If the column reference is not in the SELECT clause the query must not be a set operation, specify SELECT DISTINCT, or contain a GROUP BY clause. Unrelated expressions, expressions not appearing as an aliased expression in the SELECT clause, are allowed in the ORDER BY clause of a non-set QUERY. The columns referenced in the expression must come from the FROM clause table references. The column references cannot be to alias names or positional. The ORDER BY columns must be of a comparable type. If an ORDER BY is used in an inline view or view definition without a LIMIT clause, it will be removed by the JBoss Data Virtualization optimizer. If NULLS FIRST/LAST is specified, then nulls are guaranteed to be sorted either first or last. If the null ordering is not specified, then results will typically be sorted with nulls as low values, which is the JBoss Data Virtualization internal default sorting behavior. However not all sources return results with nulls sorted as low values by default, and JBoss Data Virtualization may return results with different null orderings. Warning The use of positional ordering is no longer supported by the ANSI SQL standard and is a deprecated feature in JBoss Data Virtualization. It is preferable to use alias names in the ORDER BY clause. 2.6.16. LIMIT Clause The LIMIT clause specifies a limit on the number of records returned from the SELECT command. An optional offset (the number of rows to skip) can be specified. The LIMIT clause can also be specified using the SQL 2008 OFFSET/FETCH FIRST clauses. If an ORDER BY is also specified, it will be applied before the OFFSET/LIMIT are applied. If an ORDER BY is not specified there is generally no guarantee what subset of rows will be returned. Usage: Syntax Rules: The limit/offset expressions must be a non-negative integer or a parameter reference (?). An offset of 0 is ignored. A limit of 0 will return no rows. The terms FIRST/ are interchangeable as well as ROW/ROWS. The LIMIT clause may take an optional preceding NON_STRICT hint to indicate that push operations should not be inhibited even if the results will not be consistent with the logical application of the limit. The hint is only needed on unordered limits, e.g. "SELECT * FROM VW /*+ NON_STRICT */ LIMIT 2". Examples: LIMIT 100 - returns the first 100 records (rows 1-100) LIMIT 500, 100 - skips 500 records and returns the 100 records (rows 501-600) OFFSET 500 ROWS - skips 500 records OFFSET 500 ROWS FETCH 100 ROWS ONLY - skips 500 records and returns the 100 records (rows 501-600) FETCH FIRST ROW ONLY - returns only the first record 2.6.17. INTO Clause Warning Usage of the INTO Clause for inserting into a table has been been deprecated. An INSERT with a query command should be used instead. Refer to Section 2.5.3, "INSERT Command" . 2.6.18. OPTION Clause The OPTION keyword denotes options the user can pass in with the command. These options are specific to JBoss Data Virtualization and not covered by any SQL specification. Usage: Supported options: MAKEDEP table [(,table)*] - specifies source tables that will be made dependent in the join MAKENOTDEP table [(,table)*] - prevents a dependent join from being used NOCACHE [table (,table)*] - prevents cache from being used for all tables or for the given tables Examples: OPTION MAKEDEP table1 OPTION NOCACHE All tables specified in the OPTION clause should be fully qualified, however the name may match either an alias name or the fully qualified name. Note versions of JBoss Data Virtualization accepted the PLANONLY, DEBUG, and SHOWPLAN option arguments. These are no longer accepted in the OPTION clause. See Red Hat JBoss Data Virtualization Development Guide: Client Development for replacements to those options.

Commands:
WITH name [(column, ...)] AS (query expression)
WITH name [(column, ...)] AS (anchor query expression UNION [ALL] recursive query expression)
SELECT teiid_session_set('teiid.maxRecursion', 25); WITH n (x) AS (values('a') UNION select chr(ascii(x)+1) from n where x < 'z') select * from n
SELECT [DISTINCT|ALL] ((expression [[AS] name])|(group identifier.STAR))*|STAR
FROM /*+ MAKEDEP PRESERVE */ (tbl1 inner join tbl2 inner join tbl3 on tbl2.col1 = tbl3.col1 on tbl1.col1 = tbl2.col1), tbl3 WHERE tbl1.col1 = tbl2.col1
FROM /*+ PRESERVE */ (tbl1 inner join tbl2 inner join tbl3 on tbl2.col1 = tbl3.col1 on tbl1.col1 = tbl2.col1)
select * from t1, TABLE(call proc(t1.x)) t2
select * from TABLE(call proc(t1.x)) t2, t1
TEXTTABLE(expression [SELECTOR string] COLUMNS <COLUMN>, ... [NO ROW DELIMITER] [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name
COLUMN := name (FOR ORDINALITY | ([HEADER string] datatype [WIDTH integer [NO TRIM]] [SELECTOR string integer]))
SELECT * FROM TEXTTABLE(UNESCAPE('col1,col2,col3\na,b,c') COLUMNS col2 string HEADER) x
SELECT * FROM TEXTTABLE(UNESCAPE('abc\ndef') COLUMNS col1 string width 1, col2 string width 1, col3 string width 1) x
SELECT * FROM TEXTTABLE('abc' COLUMNS col1 string width 1 NO ROW DELIMITER) x
SELECT * FROM TEXTTABLE('a:,,b' COLUMNS col1 string, col2 string ESCAPE ':') x
SELECT x.* FROM t, TEXTTABLE(t.clobcolumn COLUMNS first string, second date SKIP 1) x
SELECT * FROM TEXTTABLE('a,b\nc,d\nc,f' SELECTOR 'c' COLUMNS col1 string, col2 string col3 string SELECTOR 'a' 2) x
XMLTABLE([<NSP>,] xquery-expression [<PASSING>] [COLUMNS <COLUMN>, ... )] AS name
COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))
select * from xmltable('/a' PASSING xmlparse(document '<a id="1"/>') COLUMNS id integer PATH '@id') x
select x.* from t, xmltable('/x/y' PASSING t.doc COLUMNS first string, second FOR ORDINALITY) x
select x.* from (call source.invokeMDX('some query')) r, arraytable(r.tuple COLUMNS first string, second bigdecimal) x
ARRAYTABLE(val COLUMNS col1 string, col2 integer) AS X
TABLE(SELECT cast(array_get(val, 1) AS string) AS col1, cast(array_get(val, 2) AS integer) AS col2) AS X
OBJECTTABLE([LANGUAGE lang] rowScript [PASSING val AS name ...] COLUMNS colName colType colScript [DEFAULT defaultExpr] ...) AS id
SELECT x.* FROM OBJECTTABLE('teiid_context' COLUMNS "user" string 'teiid_row.userName', row_number integer 'teiid_row_number') AS x
teiid_context.session.vdb.description
WHERE criteria
GROUP BY expression (,expression)*
SELECT country, city, sum(amount) from sales group by country, city
SELECT country, city, sum(amount) from sales group by rollup(country, city)
ORDER BY expression [ASC|DESC] [NULLS (FIRST|LAST)],
LIMIT [offset,] limit
[OFFSET offset ROW|ROWS] [FETCH FIRST|NEXT [limit] ROW|ROWS ONLY
OPTION option, (option)*