SQL Reference

table-reference

>>-+--+--+-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:

Table Function References

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.

Correlated References in table-references

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
 


[ Top of Page | Previous Page | Next Page ]