AS SELECT clause

This syntax closely resembles in its functionality the INTO STANDARD and INTO RAW Clauses of the SELECT statement.

Usage

When using the CREATE TABLE . . . AS SELECT statement to create a new permanent table to store the result of a query, you can specify the logging mode of the table as STANDARD or RAW. If you omit both of these keywords, the default is STANDARD.

If you omit a storage or locking specification, the database server uses the default value.

If an error occurs while the database server is populating the new table with qualifying rows from the query in the AS SELECT clause, the operation is rolled back, and no new table is created or populated.

The columns that appear in the Projection list of the AS SELECT clause can be from any local table, or view, or from a remote database (which must be referenced in the qualified table name), but the new table must be created in the local database.

In a grid environment, if the tables in the FROM clause of the AS SELECT clause have the same schema in all the participating database servers of the specified grid or region, you can include the AS SELECT clause to create a result table from a grid query.

Supported data types

The CREATE TABLE . . . AS SELECT statement supports user-defined data types and all the built-in Informix® data types.

No more than one serial column, however, is allowed in the new result table. After the first column of type SERIAL, SERIAL8, or BIGSERIAL is included, any subsequent SERIAL, SERIAL8, or BIGSERIAL column is created as an INTEGER, INTEGER8, or BIGINTEGER column.

Column names in the result table

By default, the column names in the new permanent table are the names that are specified in the SELECT list of the Projection clause. If an asterisk ( * ) is the SELECT list of the Projection clause, the asterisk is expanded to all the column names in the corresponding tables or views in the FROM clause of the SELECT statement. Any explicit or implicit shadow columns in table objects specified by the FROM clause are not expanded by the asterisk specification.

On systems that implement Enterprise Replication, you can use the ADD CRCOLS, ADD REPLCHECK, and ADD ERKEY options to the ALTER TABLE statement to add the corresponding shadow columns to a result table that the AS SELECT clause creates.

All expressions in the SELECT list of the Projection clause, other than simple column expressions, must have a display label (also called a column alias). This is used as the identifier of the corresponding column in the new query result table. If a column expression has no display label, the result table uses the column name from the source table in the FROM clause of the query.

If both the SELECT list of the Projection clause and the comma-separated list of aliases that follows the TABLE keyword are present in the CREATE TABLE . . . AS SELECT statement, the comma-separated list of column aliases takes precedence. In this case, any column alias that you declare in the AS SELECT clause is ignored.

The AS SELECT clause can include a column in its ORDER BY clause that is not in the SELECT list of its Projection clause.

Restrictions on result tables

Besides restrictions that the section above identifies for display labels or column aliases and for unsupported column properties in result tables, the SELECT INTO . . . TABLE syntax of the SELECT statement is not valid as a part of a subquery.

As with most DDL statements, attempts to create the new result table in another database using the fully qualified table name fail with a syntax error. It is similarly an error to create a result table with the same name as an existing table, unless the AS SELECT clause includes the IF NOT EXISTS keywords, as described below.

IF NOT EXISTS keywords

If the name that you declare for the query result table in the AS SELECT clause is unique among the names of permanent tables, synonyms, views, and sequences in the database, the database server creates a query result table and populates it with all the qualifying rows that the query returns, whether or not the AS SELECT clause includes the IF NOT EXISTS keywords. (If the query returns no rows, the result table is empty, but its schema is registered in the system catalog of the database as a new permanent table.)

0 row(s) retrieved into table.

If the AS SELECT clause omits the IF NOT EXISTS keywords, and the name that you declare for the query result table is not unique among the names of permanent table objects in the database, no result table is created, and the database server returns an error.

Examples of creating and populating result tables

The following example creates a new raw table called rtabl to store the results of a join query:

CREATE RAW TABLE IF NOT EXISTS rtab1
   AS
   SELECT t1col1, t1col2, t2col1 
      FROM tab1, tab2 
      WHERE t1col1 < 100 and t2col1 > 5;

In the example above, the new query result table rtab1 would contain the columns t1col1, t1col2 and t2col1.

The next example fails with error -249, because it declares no display label for the col1+5 column expression:

CREATE TABLE IF NOT EXISTS qtab1
   AS
   SELECT col1+5, col2
      FROM tab1;    

By declaring the column alias qcol1 for the column expression in the Projection clause that includes the + operator, the following revised query avoids the -249 error that the previous example returns:

CREATE TABLE IF NOT EXISTS qtab1 (qcol1, col2)
   AS 
   SELECT col1+5, col2 
      FROM tab1;

The corrected example above creates the standard qtabl table to store the AS SELECT clause query results.

The next example uses different but equivalent aliasing syntax to declare the same qcol1 alias in the AS SELECT clause, rather than in the list of column aliases:

CREATE TABLE IF NOT EXISTS qtab1
   AS
   SELECT col1+5 qcol1, col2 
      FROM tab1; 

The CREATE TABLE statement above similarly avoids the -249 error, and creates a result table that is identical in schema and in data content to the qtabl table in the previous example. In both of these examples, the result table has two columns, qcol1 and col2. If col1 is of type INTEGER, then qcol1 would be type DECIMAL, the return data type from the expression col1+5.

As the syntax diagram indicates, the Storage and Lock Mode options to the CREATE TABLE statement are valid with the AS SELECT clause. The following example uses the FRAGMENT BY EXPRESSION keywords to define distributed storage for the query result table, where the fcol1 column alias is the fragment key, and ROW is the locking granularity:

CREATE TABLE IF NOT EXISTS permtab (fcol1, col2)
   FRAGMENT BY EXPRESSION
      fcol1 < 300 IN dbs1,
      fcol1 >=300 IN dbs2
   LOCK MODE ROW
   AS SELECT col1::FLOAT, col2
         FROM tab1;  

Any rows with fcol1 values below 300 are inserted into dbspace dbs1. Rows with larger fcol1 values are stored in the dbs2 dbspace.