Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 27 Feb 2004 @ 03:58:50 GMT


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


Subj:   Re: CREATE TABLE...AS and when SIs are created.
 
From:   Frank C. Martinez IV

Hola dudes,

Just remember, you make #2 without specifying a primary index, and the new table will default to the first column in the select as the primary index. So for example, you do the following:

show TABLE p_rbnatp.adj_cond_cd;

CREATE TABLE junk
    AS (SELECT ADJ_CATG_DESC,
               ADJ_COND_CD,
               ADJ_COND_CD_SK,
               ADJ_COND_DESC,
               IA_LVL
          FROM p_rbnatp.adj_cond_cd)
  with data;

show TABLE junk;

And you get:

CREATE SET TABLE A353656.junk ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      ADJ_CATG_DESC VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ADJ_COND_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ADJ_COND_CD_SK INTEGER NOT NULL,
      ADJ_COND_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      IA_LVL VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
PRIMARY INDEX ( ADJ_CATG_DESC );

from the following original table:

CREATE MULTISET TABLE p_rbnatp.adj_cond_cd ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      ADJ_COND_CD_SK INTEGER NOT NULL,
      ADJ_COND_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      IA_LVL VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ADJ_COND_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      ADJ_CATG_DESC VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( ADJ_COND_CD_SK )
INDEX ( ADJ_COND_DESC );

So you end up without the secondaries, but you also can (indavertantly) end up with some other primary index. Could be badness!

iv



     
  <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: 23 Jun 2019