as-subquery-clause

column-name
Names a column of the table. Do not qualify column-name and do not use the same name for more than one column of the table or for a system-column-name of the table.
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 are to 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 tables or views referenced in the select-statement.

The implicitly defined columns of the table inherit the names of the columns from the result table of the select-statement. Therefore, a column name must be specified in the select-statement or in the column name list for all result columns. For result columns that are derived from expressions, constants, and functions, the select-statement must include the AS column-name clause immediately after the result column or a name must be specified in the column list preceding the select-statement.

The select-statement must not refer to variables or include parameter markers (question marks). The select-statement must not contain a PREVIOUS VALUE or a NEXT VALUE expression.

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 not executed. Therefore, there is no result table with a set of rows with which to automatically populate the table.