Archives of the TeradataForum

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

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

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