>>-+--+--+-nickname---+---------------------+---+-------------------------+---------------+->
| | +-table-name-+ | '-| correlation-clause |--' |
| | '-view-name--' | |
| '--+-ONLY--+---(--+-table-name-+---)--' |
| '-OUTER-' '-view-name--' |
+-TABLE--(--function-name--(--+--------------------+---)----)--| correlation-clause |--+
| | .-,------------. | |
| | V | | |
| '----expression---+--' |
+-+-------+--(fullselect)--| correlation-clause |--------------------------------------+
| '-TABLE-' |
'-joined-table-------------------------------------------------------------------------'
>--------------------------------------------------------------><
correlation-clause
.-AS-.
|---+----+--correlation-name--+----------------------------+----|
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
Each table-name, view-name or nickname specified as a table-reference must identify an existing table, view or nickname at the application server or the table-name of a common table expression (see common-table-expression) defined preceding the fullselect containing the table-reference. If the table-name references a typed table, the name denotes the UNION ALL of the table with all its subtables, with only the columns of the table-name. Similarly, if the view-name references a typed view, the name denotes the UNION ALL of the view with all its subviews, with only the columns of the view-name.
The use of ONLY(table-name) or ONLY(view-name) means that the rows of the proper subtables or subviews are not included. If the table-name used with ONLY does not have subtables, then ONLY(table-name) is equivalent to specifying table-name. If the view-name used with ONLY does not have subviews, then ONLY(view-name) is equivalent to specifying view-name.
The use of OUTER(table-name) or OUTER(view-name) represents a virtual table. If the table-name or view-name used with OUTER does not have subtables or subviews, then specifying OUTER is equivalent to not specifying OUTER. OUTER(table-name) is derived from table-name as follows:
The previous points also apply to OUTER(view-name), substituting view-name for table-name and subview for subtable.
The use of ONLY or OUTER requires the SELECT privilege on every subtable of table-name or subview of view-name.
Each function-name together with the types of its arguments, specified as a table reference must resolve to an existing table function at the application server.
A fullselect in parentheses followed by a correlation name is called a nested table expression.
A joined-table specifies an intermediate result set that is the result of one or more join operations. For more information, see joined-table.
The exposed names of all table references should be unique. An exposed name is:
Each correlation-name is defined as a designator of the immediately preceding table-name, view-name, nickname, function-name reference or nested table expression. Any qualified reference to a column for a table, view, table function or nested table expression must use the exposed name. If the same table name, view or nickname name is specified twice, at least one specification should be followed by a correlation-name. The correlation-name is used to qualify references to the columns of the table, view or nickname. When a correlation-name is specified, column-names can also be specified to give names to the columns of the table-name, view-name, nickname, function-name reference or nested table expression. For more information, see Correlation Names.
In general, table functions and nested table expressions can be specified on any from-clause. Columns from the table functions and nested table expressions can be referenced in the select list and in the rest of the subselect using the correlation name which must be specified. The scope of this correlation name is the same as correlation names for other table, view or nickname in the FROM clause. A nested table expression can be used:
In general, a table function together with its argument values can be referenced in the FROM clause of a SELECT in exactly the same way as a table or view. There are, however, some special considerations which apply.
Unless alternate column names are provided following the correlation-name, the column names for the table function are those specified in the RETURNS clause of the CREATE FUNCTION statement . This is analogous to the names of the columns of a table, which are of course defined in the CREATE TABLE statement. See CREATE FUNCTION (External Table) for details about creating a table function.
The arguments specified in a table function reference, together with the function name, are used by an algorithm called function resolution to determine the exact function to be used. This is no different from what happens with other functions (such as scalar functions), used in a statement. Function resolution is covered in Function Resolution.
As with scalar function arguments, table function arguments can in general be any valid SQL expression. So the following examples are valid syntax:
Example 1: SELECT c1
FROM TABLE( tf1('Zachary') ) AS z
WHERE c2 = 'FLORIDA';
Example 2: SELECT c1
FROM TABLE( tf2 (:hostvar1, CURRENT DATE) ) AS z;
Example 3: SELECT c1
FROM t
WHERE c2 IN
(SELECT c3 FROM
TABLE( tf5(t.c4) ) AS z -- correlated reference
) -- to previous FROM clause
Correlated references can be used in nested table expressions or as arguments to table functions. The basic rule that applies for both these cases is that the correlated reference must be from a table-reference at a higher level in the hierarchy of subqueries. This hierarchy includes the table-references that have already been resolved in the left-to-right processing of the FROM clause. For nested table expressions, the TABLE keyword must appear before the fullselect. So the following examples are valid syntax:
Example 1: SELECT t.c1, z.c5
FROM t, TABLE( tf3(t.c2) ) AS z -- t precedes tf3 in FROM
WHERE t.c3 = z.c4; -- so t.c2 is known
Example 2: SELECT t.c1, z.c5
FROM t, TABLE( tf4(2 * t.c2) ) AS z -- t precedes tf3 in FROM
WHERE t.c3 = z.c4; -- so t.c2 is known
Example 3: SELECT d.deptno, d.deptname,
empinfo.avgsal, empinfo.empcount
FROM department d,
TABLE (SELECT AVG(e.salary) AS avgsal,
COUNT(*) AS empcount
FROM employee e -- department precedes and
WHERE e.workdept=d.deptno -- TABLE is specified
) AS empinfo; -- so d.deptno is known
But the following examples are not valid:
Example 4: SELECT t.c1, z.c5
FROM TABLE( tf6(t.c2) ) AS z, t -- cannot resolve t in t.c2!
WHERE t.c3 = z.c4; -- compare to Example 1 above.
Example 5: SELECT a.c1, b.c5
FROM TABLE( tf7a(b.c2) ) AS a, TABLE( tf7b(a.c6) ) AS b
WHERE a.c3 = b.c4; -- cannot resolve b in b.c2!
Example 6: SELECT d.deptno, d.deptname,
empinfo.avgsal, empinfo.empcount
FROM department d,
(SELECT AVG(e.salary) AS avgsal,
COUNT(*) AS empcount
FROM employee e -- department precedes but
WHERE e.workdept=d.deptno -- TABLE is not specified
) AS empinfo; -- so d.deptno is unknown