Neoview SQL Reference Manual (R2.4 SP2)

Table t has 2 columns named (a,b) and the same data attributes as columns from table t1.
• If the select list item is an expression, it must be renamed with an AS clause. An error is
returned if expressions are not named. For example:
create table t as select a+1 as c from t1
Table t has 1 column named (c) and data attribute of (a+1)
create table t as select a+1 from t1
An error is returned, expression must be renamed.
• If column-attributes are specified and contains datatype-info, then they override
the attributes of the select items in the select query. These data attributes must be compatible
with the corresponding data attributes of the select list items in the select-query.
create table t(a int) as select b from t1
Table t has one column named “a” with datatype “int”.
create table t(a char(10)) as select a+1 b from t1;
An error is returned since the data attribute of column “a”, a char, does not match the data
attribute of the select list item “b” a numeric.
• If column-attributes are specified and they only contain column-name, then the
specified column-name override any name that was derived from the select query.
create table t(c,d) as select a,b from t1
Table t has 2 columns, c and d, which has the data attributes of columns a and b from table
t1.
• If column-attributes are specified, then they must contain attributes corresponding to
all select list items in the select-query. An error is returned, if there is a mismatch.
create table t(a int) as select b,c from t1
An error is returned. Two items need to be specified as part of the table-attributes.
• column-attributes must specify either the column-name datatype-info pair or just
the column-name for all columns. You cannot specify some columns with just the name
and others with name and datatype.
create table t(a int, b) as select c,d from t1
An error is returned.
In the following example, table t1 is created. Table t2 is created using the CREATE TABLE AS
syntax without table attributes:
CREATE TABLE t1 (c1 int not null primary key,
c2 char(50));
CREATE TABLE t2 (c1 int, c2 char (50) UPSHIFT NOT NULL)
AS SELECT * FROM t1;
94 SQL Statements