as-subquery-clause

column-name
Names a column in the table. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the select-statement. Each column-name must be unique and unqualified. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result table of the select-statement.

A list of column names must be specified if the result table of the select-statement has duplicate column names or an unnamed column. An unnamed column is a column derived from a constant, function, expression, or set operation (UNION or INTERSECT) that is not named using the AS clause of the select list.

FOR COLUMN system-column-name
Provides an i5/OS name for the column. Do not use the same name for more than one column of the table or for a column-name of the table.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see Rules for Column Name Generation.

select-statement
Specifies that the columns of the table have the same name and description as the columns that would appear in the derived result table of the select-statement if the select-statement were to be executed. The use of AS (select-statement) is an implicit definition of n columns for the table, where n is the number of columns that would result from the select-statement.

The implicit definition includes the following attributes of the n columns (if applicable to the data type):

The following attributes are not included (the default value and identity attributes may be included by using the copy-options):

The implicit definition does not include any other optional attributes of the identified table or view. For example, the new table does not automatically include a primary key or foreign key from a table. The new table has these and other optional attributes only if the optional clauses are explicitly specified.

The select-statement must not refer to variables or include parameter markers.

The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression. The UPDATE, READ ONLY, and OPTIMIZE clauses may not be specified.

WITH DATA
Specifies that the select-statement is executed. After the table is created, the result table rows of the select-statement are automatically inserted into the table.
WITH NO DATA
Specifies that the select-statement is used only to define the attributes of the new a table. The table is not populated using the results of the select-statement.
refreshable-table-options
Specifies that the table is a materialized query table and the REFRESH TABLE statement can be used to populate the table with the results of the select-statement.

A materialized query table whose select-statement contains a GROUP BY clause is summarizing data from the tables referenced in the select-statement. Such a materialized query table is also known as a summary table. A summary table is a specialized type of materialized query table.

When a materialized query table is defined, the following select-statement restrictions apply:

When a materialized query table is defined with ENABLE QUERY OPTIMIZATION, the following additional select-statement restrictions apply:

DATA INITIALLY DEFERRED
Specifies that the data is not inserted into the materialized query table when it is created. Use the REFRESH TABLE statement to populate the materialized query table, or use the INSERT statement to insert data into a materialized query table.
DATA INITIALLY IMMEDIATE
Specifies that the data is inserted into the materialized query table when it is created.
REFRESH DEFERRED
Specifies that the data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time when the REFRESH TABLE statement is processed or when it was last updated.
MAINTAINED BY USER
Specifies that the materialized query table is maintained by the user. The user can use INSERT, DELETE, UPDATE, or REFRESH TABLE statements on the table.
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether this materialized query table can be used for optimization. The default is ENABLE QUERY OPTIMIZATION.
ENABLE QUERY OPTIMIZATION
Specifies that the materialized query table can be used for query optimization. If the select-statement specified does not satisfy the restrictions for query optimization, an error is returned.
DISABLE QUERY OPTIMIZATION
Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.