Archives of the TeradataForum

Message Posted: Thu, 28 Aug 2008 @ 14:23:27 GMT

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

Hi,

I have written 4 set of queries to Drop and Create tables with identity column as primary key. I am able to success individual execution. If I use queries in stored procure getting error "A table may not have more than one identity column." on 2nd, 3rd and 4th set of queries.

In 1st query NO issue. Only 2nd, 3rd and 4th queries are having issue.

Let me know what the cause is.

Find in blow with set of 4 queries and Errors.

     Query 1:
     --------
     Drop table Catalog_Path;
     Create table Catalog_Path (
     ID int GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
     LEAF_LABEL   varchar(255),
     DESCRIPTION   varchar(255),
     HC_ID   smallint ,
     HA_ID   smallint ,
     HCDEPTH   numeric(5,0) ,
     ITEM_ID   int ,
     SEQUENCE   numeric(5,0) ,
     ANCESTOR_01_NATIVE_PK   varchar(255),
     ANCESTOR_02_NATIVE_PK   varchar(255),
     ANCESTOR_03_NATIVE_PK   varchar(255),
     ANCESTOR_04_NATIVE_PK   varchar(255),
     ANCESTOR_05_NATIVE_PK   varchar(255),
     ANCESTOR_06_NATIVE_PK   varchar(255),
     ANCESTOR_07_NATIVE_PK   varchar(255),
     ANCESTOR_08_NATIVE_PK   varchar(255),
     ANCESTOR_09_NATIVE_PK   varchar(255),
     ANCESTOR_10_NATIVE_PK   varchar(255),
     Primary key(Id));

     Query 2:
     --------
     Drop table Tmp_hierarchy_class_attribute;
     CREATE TABLE Tmp_hierarchy_class_attribute(
     ID int GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
     HA_ID SMALLINT,
     HC_ID SMALLINT,
     SEQUENCE DECIMAL(5,0),
     PRIMARY key(Id));

     Query 3:
     --------
     Drop table STAGE_CATALOG_PATH;
     Create table STAGE_CATALOG_PATH(
     ID int GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
     LEAF_LABEL   varchar(100),
     DESCRIPTION   varchar(255),
     HC_ID   smallint,
     HA_ID   smallint,
     HCDEPTH   numeric(5,0),
     ITEM_ID   int,
     I_IS_UPDATE   int,
     IS_UPDATEABLE   int,
     ifoundit   int,
     SEQUENCE   numeric(5,0),
     ANCESTOR_01_NATIVE_PK   varchar(255),
     ANCESTOR_02_NATIVE_PK   varchar(255),
     ANCESTOR_03_NATIVE_PK   varchar(255),
     ANCESTOR_04_NATIVE_PK   varchar(255),
     ANCESTOR_05_NATIVE_PK   varchar(255),
     ANCESTOR_06_NATIVE_PK   varchar(255),
     ANCESTOR_07_NATIVE_PK   varchar(255),
     ANCESTOR_08_NATIVE_PK   varchar(255),
     ANCESTOR_09_NATIVE_PK   varchar(255),
     ANCESTOR_10_NATIVE_PK   varchar(255),
     Primary key(ID));

     Query 4:
     --------
     Drop table Tmp_hierarchy_class_attribute;
     CREATE TABLE Tmp_hierarchy_class_attribute(
     ID int GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
     HA_ID SMALLINT,
     HC_ID SMALLINT,
     SEQUENCE DECIMAL(5,0),
     PRIMARY key(Id));

     Errors:
     ------
     SPL1027:E(L189), Missing/Invalid SQL statement'E(3706):Syntax error: A table
     may not have more than one identity column. '.   ----> Query 2

     SPL1027:E(L328), Missing/Invalid SQL statement'E(3706):Syntax error: A table
     may not have more than one identity column. '.     -----> Query 3

     SPL1027:E(L416), Missing/Invalid SQL statement'E(3706):Syntax error: A table
     may not have more than one identity column. '.      ------> Query 4

Thanks,

Kishore KK

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