SQL Statements › SELECT › Subselect › FROM Clause

FROM Clause

FROM table-ref

Where table-ref can be a table-name, view-name, or alternate-join-type. If table-ref is a table-name or view-name, it names:

• A single table or view.
• Several tables and/or views to produce an intermediate result table.

If table-ref is an alternate-join-type, see the information about joins in Left Outer Joins.

You can reference up to 20 tables in a FROM clause of a query when you are performing a join. For example, if a view is based on five tables, you can name that view in the FROM clause and up to 15 other tables. The names must be separated by commas.

The intermediate result table contains all possible combinations of the rows of the named tables or views. Each row of the result is a row from the first table or view concatenated with a row from the second table or view, concatenated in turn with a row from the third, and so on. The number of rows in the result is the product of the number of rows in all the named tables or views.

The list of names in the FROM clause must conform to these rules:

1. Each table-name and view-name must name a table or view described in the CA Datacom Datadictionary.
2. If the FROM clause is in a subquery of a basic predicate, no view named can use either GROUP BY or HAVING.
3. In other cases, if a named view uses GROUP BY, HAVING, or a column function, no other table or view can be named. The subselect statement is processed as if it contained the GROUP BY, HAVING, or column function used in the definition of the view.

correlation-name

The 1- to 18-byte correlation-name applies to the table or view named by the immediately preceding table-name or view-name. The correlation name can be used elsewhere in the statement to designate that table or view. Use a blank to separate the correlation name from the table-name or view-name.