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 in situations where 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 to not 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 then the nested expression is a procedure or function call.

Here is a valid example:

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

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
SQL

The usage of 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
SQL

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
SQL

Where <COLUMN>

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

This functions has the following parameters:

ParameterDescription
expressionText content to process, should be convertible to CLOB
SELECTORSpecifies 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 DELIMITERIndicates that fixed parsing should not assume the presence of newline row delimiters
ROW DELIMITERSets the row delimiter/new line to an alternate character. Default: new line character with built-in handling for treating the carriage-return new line as a single character. If ROW DELIMITER is specified, carriage return will be given no special treatment
DELIMITERSets the field delimiter character to use. Default: ,
QUOTESets the quote (or qualifier) character used to wrap field values. Default: ". May be set to nothing by specifying QUOTE ''
ESCAPESets the escape character to use if no quoting character is in use. This is used in situations where the delimiter or newline characters are escaped with a preceding character, e.g. \
HEADERSet for the TEXTTABLE 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 is 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. If the HEADER option for a column is specified, then that will be used as the expected header name.
SKIPSpecifies the number of text lines (counting every new line) to skip before parsing the contents. HEADER may still be specified with SKIP
A FOR ORDINALITYThis  column is typed as an integer and will return the 1-based item number as its value
WIDTHIndicates the fixed-width length of a column in characters, not bytes. With the default ROW DELIMITER, a CR NL sequence counts as a single character
NO TRIM When specified on the TEXTTABLE, will affect all column and header values. When specified on a column, the fixed or unqualified text value not be trimmed of a leading and trailing whitespace
SKIPERRORS[maxerrors]
ERRORLOG tablename

Failed lines are written into the named table using the following format:

tstamp (timestamp)RequestIDSessionIDlinenumber (integer)data (string)errorMessage (string)
timestamp of the failureRequestIDSessionIDrow nr starting from 1the row data as stringthe error message

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
SQL

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
CODE

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
SQL

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

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

5. As a nested table:

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

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
SQL

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";;
SQL

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";;
SQL

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
SQL
COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))
SQL

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, then the path will be the same as the column name.

Syntax Rules

  • Only one A FOR ORDINALITY column may be specified;
  • The columns 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;;
SQL

2. As a nested table:

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

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()&amp;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";;
SQL

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;;
SQL