Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Difference in table creation.
 
From:   Geoffrey Rommel

  Can i know the difference in creating table by DDL(full defination) and by using query "create table DB.TABLENAME as ........"  


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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023