Table specifications

---

table_specification ::= { table [[AS] correlation_name [( column , ...)]] |

                                          subquery [AS] correlation_name [( column , ...)] |

                     TABLE([ catalog .] table_function ) [WITH ORDINALITY]

                          [[AS] correlation_name [( column , ...)]] |

                                          join_expression }

---

table

Name of a base table or view.

The same table can occur several times in a table specification in the query expression. Correlation names are used to distinguish between different instances of the same table.

subquery

The table is the derived table that results from evaluating subquery.

[catalog.]table_function

The (“read-only”) table (see the “ Core manual”) is the result of the table function table_function.

If table function DEE() is specified, no column names may be specified.

The database name catalog must be specified if the containing statement is not to be executed on the database set implicitly (see "Qualified names") (and consequently possibly with another SQL server).

WITH ORDINALITY

Definition of a counting column in the derived table. This specification may only be entered for the table function CSV(), but not for DEE().

The derived table must “at the end” contain one column more than the column specification in each line of the CSV file. The data type of the last column of the derived table must be DECIMAL(31,0). This column is used as the counting column. Beginning with 1 and in ascending order, it is assigned the ordinal number of the line which was read in from the CSV file. The WHERE clause also enables derived rows of particular ordinal numbers to be ignored in a SELECT expression, see the example on the next page.

The data types of each column of the derived table (with the exception of the last column) must match the data types of the column specifications in the CSV file.

WITH ORDINALITY not specified:
The number of columns in the derived table must be the same as the number of column specifications in the CSV file, and the data types of each column must match.

Example

with.3.headers is a CSV file with exactly 3 headers which are not evaluated or are skipped:

SELECT c1, c2,...,cn 
       FROM TABLE(CSV('with.3.headers' DELIMITER ',' QUOTE '?' 
                 ESCAPE '-', CHAR(20), CHAR(20),..., CHAR(20))) 
       WITH ORDINALITY
       AS T(c1, c2,....,cn, counter) 
WHERE counter > 3

correlation_name

Table name used in the query expression as a new name for the table.

The correlation_name must be used to qualify the column name in every column specification that references this instance of the table if the column name is not unambiguous.

The new name must be unique, i.e. correlation_name can only occur once in a table specification of this query expression.

You must give a table a new name if the columns in the table cannot otherwise be identified uniquely in the query expression.
correlation_name must be specified in the case of table_function (exception: DEE()).

In addition, you may give a table a new name in order to formulate the query expression so that it is more easily understood or to abbreviate long names.

Example

Join a table with itself:

SELECT a.company, b.company	-- Query customer
FROM customers AS a, customers AS b
WHERE a.city = b.city	-- who lives in the same city
AND a.cust_num < b.cust_num	-- but avoid duplicates

column

Column name that is used within the query expression as the new name for the column of the corresponding table.

If you rename a column, you must give all the columns in the table a new name.

column is the new name of the column and must be unique within the table specified by correlation name. In this query expression the column may only be addressed with the new name.

The columns of a derived table must be renamed if the column names of the table upon which it is based are not unique, or if the derived columns are to be referenced using names that have been assigned internally.

Example

Give the columns in the WAREHOUSE table new, more informative names:

SELECT * FROM warehouse w (item_number, current_stock, location)

WHERE location = 'Parts warehouse'

column,... omitted:
The column names of the associated table are valid. These could be names that are assigned internally, which cannot be referenced in the query expression.

join_expression

Join expression that determines the tables from which the data is to be selected. Join expressions are described in the section "Join expression".

Underlying base tables

Depending on the specification made in the table specification, the underlying base tables are defined as follows:

Specification in table specification	Underlying base table
Base table	the base table
View	all the base tables which the view references directly or indirectly
Subquery	Base table upon which the subquery is based
TABLE([catalog.]table_function)	no base table

Table 24: Underlying base tables