FROM Clause

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

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
• FROM LATERAL(subquery) [AS] alias
• 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
• FROM TEXTTABLE...
• FROM XMLTABLE...
• FROM ARRAYTABLE...
• FROM ( SELECT ...
• FROM source_table(subquery) [AS] alias UNPIVOT (value_column FOR pivot_column IN (first_header_name, ..., last_header_name) ) [AS] unpivot_alias

FROM Clause Hints

MAKEIND, MAKEDEP, and MAKENOTDEP are hints used to control dependent join behaviour. They should only be used when the optimizer does not choose the most optimal plan based on query structure, metadata, and costing information. The hints may appear in a comment that proceeds the FROM clause. The hints can be specified against any FROM clause, not just a named table.

• Be sure that there is no whitespace between /* and + when using e.g. /*+ MAKEDEP */;
• Configure your SQL client not to remove multi-line comments ( in Squirrel: Session -> Session Properties -> SQL -> Remove multi-line comment ( /* ... */ )

NO_UNNEST can be specified against a subquery 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 the Data Virtuality Server planning and is not passed to source queries. NO_UNNEST may appear in a comment that proceeds the FROM clause.

Nested Table References and Lateral Joins

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 like any of the other FROM clause projected columns in join criteria, the WHERE clause, etc.

A nested table may have correlated references to the 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.

Here is a valid example:

SELECT * FROM t1, TABLE(CALL proc(t1.x)) t2

And here is an Invalid example, since t1 appears after the nested table in the FROM clause:

SELECT * from TABLE(call proc(t1.x)) t2, t1

Using a correlated nested table may result in multiple executions of the table expression - once for each correlated row.

The Data Virtuality Server also allows the ANSI standard keyword LATERAL in place of the TABLE keyword, which is more typically used with query expression subqueries:

SELECT * FROM t1, LATERAL(SELECT col1 FROM t2 WHERE t2.col2 = t1.x) t2

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 correlated to the preceding FROM clause entries.

Usage

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

Where <COLUMN>

COLUMN := name (FOR ORDINALITY | ([HEADER string] datatype [WIDTH integer [NO TRIM]] [SELECTOR string integer]))

This function has the following parameters:

To view the full table, click the expand button in its top right corner

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, SELECTOR, and HEADER should not be specified;
• If width is not specified, then NO ROW DELIMITER cannot be used;
• The columns names must contain no duplicates;
• QUOTE, DELIMITER, and ROW DELIMITER must all be different characters.

Examples

1. Use of the HEADER parameter, returns 1 row ['b']:

SELECT * FROM TEXTTABLE(UNESCAPE('col1,col2,col3\na,b,c') COLUMNS col2 string HEADER) x

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

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

4. Use of the ESCAPE parameter, returns 1 row ['a,', 'b']:

SELECT * FROM TEXTTABLE('a:,,b' COLUMNS col1 string, col2 string ESCAPE ':') x

5. As a nested table:

SELECT x.* FROM t, TEXTTABLE(t.clobcolumn COLUMNS first string, second date SKIP 1) x

6. Use of SELECTORs, returns 2 rows ['c', 'd', 'b'], ['c', 'f', 'b']:

SELECT * FROM TEXTTABLE(UNESCAPE('a,b\nc,d\nc,f') SELECTOR 'c' COLUMNS col1 string, col2 string, col3 string SELECTOR 'a' 2) x

7. Use of an escaped literal to set specific chars as column and row delimiters (here: carriage return as column delimiter and line feed as row delimiter) whilst reading a CSV file from the local file system:

SELECT
"csv_table".* 
FROM (call "ds_file".getFiles('planets_export.csv')) f,
	TEXTTABLE(to_chars(f.file,'utf-8') 
		COLUMNS 
		"id" STRING
		, "name" STRING
		, "population" STRING
		, "diameter" STRING
		, "gravity" STRING
		ROW DELIMITER E'\n'
		DELIMITER E'\r'
		QUOTE '"' 
		MAXWIDTH 8000
		SKIP 1
	)
"csv_table";;

8. Specifying any Unicode character as a delimiter with referencing their number. Same case as above but different implementation:

SELECT
"csv_table".* 
FROM (call "ds_file".getFiles('planets_export.csv')) f,
	TEXTTABLE(to_chars(f.file,'utf-8') 
		COLUMNS 
		"id" STRING
		, "name" STRING
		, "population" STRING
		, "diameter" STRING
		, "gravity" STRING
		ROW DELIMITER E'\u000A'
		DELIMITER E'\u000D'
		QUOTE '"' 
		MAXWIDTH 8000
		SKIP 1
	)
"csv_table";;

XMLTABLE

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

Usage

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.

See also XMLQUERY.

See also XQuery Optimization.

Parameters

• The optional XMLNAMESPACES clause specifies the namespaces 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;
• A FOR ORDINALITY column is entered as an integer and will return the 1-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, the path will be the same as the column name.

Syntax Rules

• Only one A FOR ORDINALITY column may be specified;

• Column names must contain no duplicates.

Examples

1. Use of passing returns 1 row [1]:

SELECT * FROM XMLTABLE('/a' PASSING XMLPARSE(DOCUMENT '<a id="1"/>') COLUMNS id INTEGER PATH '@id') x;;

2. As a nested table:

SELECT x.* FROM t, XMLTABLE('/x/y' PASSING t.doc COLUMNS first STRING, second FOR ORDINALITY) x;;

Array support

Starting from RELEASE-1.8.35, the Data Virtuality Server supports arrays in XMLTABLE. This enables you to load any amount of serial data, which can be interpreted as an array:

SELECT 
"xml_table.idColumn",
"xml_table.id",
"xml_table.key",
"xml_table.fixVersions"
FROM (
    EXEC "jira".invokeHTTP(
        action => 'GET',
        requestContentType => 'application/json',
        endpoint => 'https://jira.your-domain.com/rest/api/latest/search?jql=sprint%20in%20openSprints()&expands=issues'
    )
) w,
XMLTABLE(XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as "xsi"), '/root/issues' PASSING JSONTOXML('root', to_chars(w.result,'UTF-8'))
    COLUMNS 
    "idColumn" FOR ORDINALITY,
    "id" STRING  PATH 'id',
    "key" STRING  PATH 'key',
    "fixVersions" STRING[] PATH 'fields/fixVersions/name'
) "xml_table";;

Null input

When being passed a null value as input, XMLTABLE returns an empty result:

SELECT *
FROM XMLTABLE(
	'/root'
   PASSING CAST(NULL AS XML)
   COLUMNS i INTEGER
) x;;

See Also

Using TEXTTABLE to Implement SPLIT / SPLIT_PART for a guide on how to split large strings into columns based on a delimiter