AS SELECT clause

Use the AS SELECT clause of the CREATE TABLE statement to create a new table and to insert into it the data rows that are the result set of a specified query.

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

Only the following subset of the CREATE TABLE statement syntax is valid when you include the AS SELECT clause to create a new query result table, and to populate that table with the qualifying rows that the specified query returns:
Read syntax diagramSkip visual syntax diagram
           .-STANDARD-.   
>>-CREATE--+-+-----+--+----------------------------------------->
             '-RAW-'      

>--TABLE--+---------------+--+----------+--table---------------->
          '-IF NOT EXISTS-'  '-owner--.-'          

>--+--------------------------------------------+--------------->
   |    .-,--------------------------------.    |   
   |    V                                  |    |   
   '-(----+-column-----------------------+-+--)-'   
          +-column_alias-----------------+          
          '-column--::--target_data_type-'          

>--+-------------------------------+--+-------------------+----->
   |  (1)                      (2) |  |               (3) |   
   '-------| Storage Options |-----'  '-| LOCK MODE |-----'   

    (1)                        (4)   
>--------AS--| SELECT clause |---------------------------------><

Notes:
  1. Informix® extension
  2. See Storage options
  3. See LOCK MODE Options
  4. See SELECT statement
Element Description Restrictions Syntax
column Column in a table in the FROM clause of the query. This will be a column name in the result table. Must exist in the query result set Identifier
column_alias Alias or display label for a column. This declares a column name in the result table. Any nontrivial expression in the Projection clause of the query requires an alias or display label for its column name in the result table. Identifier
owner Authorization identifier of the owner of the result table Without this, the user issuing CREATE TABLE is owner by default Owner name
table Name that you declare here for the result table Must be unique among the names of tables, synonyms, views, and sequences in the database Identifier
target_data_type Data type that is returned by an explicit cast. This will be the data type of a column in the result table. See Rules for the Target Data Type Data Type

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.

You can also optionally specify the following attributes for the query result table:
  • A single storage location, or a distributed storage scheme
  • The storage sizes of its first extent and next extent
  • Its PAGE or ROW locking granularity

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.

In the CREATE TABLE . . . AS SELECT statement, a column alias can be specified in either of two ways:
  • As a comma-separated list of aliases, immediately following the TABLE keyword, similar to the syntax of the INSERT INTO . . . SELECT FROM statement
  • As a part of the SELECT list in the Projection clause, just as in result tables that the SELECT . . . INTO STANDARD or SELECT . . . INTO RAW statements can create.

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 CREATE TABLE . . . AS SELECT statement fails with an error in the following cases:
  • If no display label or column alias is declared for a nontrivial column expression.
  • If a display label or column alias has the same name as another column in the new result table.
  • Except for storage options and LOCK MODE properties, the CREATE TABLE . . . AS SELECT statement cannot define constraints or any other special properties for columns of the new table.
  • If a comma-separated list of aliases follows the TABLE keyword, but that list has fewer aliases than the number of expressions in the SELECT list of the Projection clause.
But if the column alias list that follows the TABLE keyword has more items than the SELECT list of the Projection clause, in this case the database server ignores the excess column aliases, and no exception occurs.

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.)

If the AS SELECT clause includes 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, the query that the AS SELECT clause defines is not executed, no result table is created, and the database server returns the message
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.


Copyright© 2019 HCL Technologies Limited