Archives of the TeradataForum

Message Posted: Mon, 09 Apr 2012 @ 23:37:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Consider these three possibilities:

     1. Create table xxx.table1 (...columns...) primary index(aaa);

     2. Create table xxx.table1 as
     xxx.table2
     with data
     primary index (aaa);

     3. Create table xxx.table1 as
     (select ...stuff... from xxx.table2)
     with data
     primary index (aaa);

In #1, the definitions of the columns will be taken from your DDL statement.

In #2, the definitions will be taken from the existing definition of xxx.table2 in the Data Dictionary. In both of these cases, the columns will probably come out with the definitions you are expecting.

In #3, however, the columns will be defined with the data types that are assigned to them when the Select is executed. These may not be quite what you expect. For instance, if you select a character literal (e.g. 'NEW YORK'), it will be assigned a data type of VARCHAR(length of literal) CHARACTER SET UNICODE. If you wanted the column to be CHAR(20) CHARACTER SET LATIN, too bad. Numeric literals act much the same; for instance, 3.45 is created as DECIMAL(3,2) -- probably not what you want.

I therefore recommend avoiding syntax 3.

	<Prev	Next>		<<First	<Prev	Next>	Last>>