Visit Teradata.com Home    Search    Guidelines    Member List

Welcome Guest ( Login | Register )       Recent Posts    Popular Topics

Teradata Discussion Forums » Teradata » Teradata Database » Generation of IDENTITY values for a column

This online forum is for user-to-user discussions of Teradata products, and is not an official customer support channel for Teradata. If you require direct assistance, please contact Teradata support.

Generation of IDENTITY values for a column Rate Topic Display Mode Topic Options Author Message meetsatishg@gmail.com Posted 6/11/2008 8:56:42 AM Junior Member Group: Forum Members Last Login: 7/7/2008 2:17:32 AM Posts: 24, Visits: 90 Hi , I am generating identities values for a column for the following table CREATE RPT_PLANDETAILS ( rptid DECIMAL(18,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 NO CYCLE), PLANID DECIMAL(18,0) ) PRIMARY INDEX ( rptid ); When i am inserting 9 records then i am getting the following rowset rptid PLANID 1 240189122709774 100001 64920581096087 2 64564675477528 4 136189019865328 100003 223298109564009 6 82377621204850 100001 132172892550504 3 129338552046971 5 202024997033706 When i am taking union of two select sets and inserting into this table then i am getting following error Code = 2616. Statement 1 - 2616: USP_RPT_PLAN_DETAILS:Numeric overflow occurred during computation. Output directed to Answerset window Satish Gaikwad Post #11800 Adeel.Chaudhry Posted 6/11/2008 11:12:48 PM Supreme Being Group: Forum Members Last Login: Yesterday @ 7:51:48 AM Posts: 159, Visits: 457 Hello, Can you provide the INSERT SELECT statement? Regards, Adeel Post #11810 meetsatishg@gmail.com Posted 6/12/2008 1:16:00 AM Junior Member Group: Forum Members Last Login: 7/7/2008 2:17:32 AM Posts: 24, Visits: 90 Hi, The CREATE Statement for the table is CREATE RPT_PLANDETAILS ( rptid DECIMAL(18,0) NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 NO CYCLE), PLANID DECIMAL(18,0), PLANTITLE VARCHAR(635) , FISCALYEAR DECIMAL(18,0), PLANSTATUS VARCHAR(255) , ACTIVITYID DECIMAL(18,0), PARENTID DECIMAL(18,0), ACTIVITYLEVEL VARCHAR(50) , ACTIVITYNAME VARCHAR(200) , ACTIVITYSTATUS VARCHAR(510) , ACTIVITYTYPE VARCHAR(400) , BUSINESSUNITNAME VARCHAR(160) , BUSINESSUNITPATH VARCHAR(510) , FINANCIALSUMMARYID DECIMAL(18,0), BUDGETED_C VARCHAR(50) , BUDGETED_A DECIMAL(18,4), PLANNED_C VARCHAR(50) , PLANNED_A DECIMAL(18,4), COMMITTED_C VARCHAR(50) , COMMITTED_A DECIMAL(18,4), SPENT_C VARCHAR(50) , SPENT_A DECIMAL(18,4), AVAILABLE_C VARCHAR(50) , AVAILABLE_A DECIMAL(18,4), UNALLOCATED_C VARCHAR(50) , UNALLOCATED_A DECIMAL(18,4), ALLOCATEDTOSPEND_C VARCHAR(50) , ALLOCATEDTOSPEND_A DECIMAL(18,4), ACTUALS_C VARCHAR(50) , ACTUALS_A DECIMAL(18,4), ALLOCATEDTOSPENDROLLUP_C VARCHAR(50) C, ALLOCATEDTOSPENDROLLUP_A DECIMAL(18,4), COMMITTEDROLLUP_C VARCHAR(50) , COMMITTEDROLLUP_A DECIMAL(18,4), SPENTROLLUP_C VARCHAR(50) , SPENTROLLUP_A DECIMAL(18,4), ACTUALSROLLUP_C VARCHAR(50) , ACTUALSROLLUP_A DECIMAL(18,4), AVAILABLEROLLUP_C VARCHAR(50) , AVAILABLEROLLUP_A DECIMAL(18,4), DESCRIPTION VARCHAR(100) , LASTUPDATEDON TIMESTAMP(6), STARTDATE TIMESTAMP(6), ENDDATE TIMESTAMP(6), COLOR VARCHAR(50) ) PRIMARY INDEX ( rptid ); INSERT STATEMENT FOR RPT_PLANDETAILS IS Insert Into RPT_PLANDETAILS ( PLANID ,PLANTITLE ,FISCALYEAR ,PLANSTATUS ,ACTIVITYID ,PARENTID ,ACTIVITYLEVEL ,ACTIVITYNAME ,ACTIVITYSTATUS ,ACTIVITYTYPE ,BUSINESSUNITNAME ,BUSINESSUNITPATH ,FINANCIALSUMMARYID ,BUDGETED_C ,BUDGETED_A ,PLANNED_C ,PLANNED_A ,COMMITTED_C ,COMMITTED_A ,SPENT_C ,SPENT_A ,AVAILABLE_C ,AVAILABLE_A ,UNALLOCATED_C ,UNALLOCATED_A ,LASTUPDATEDON ,ALLOCATEDTOSPEND_C ,ALLOCATEDTOSPEND_A ,ACTUALS_C ,ACTUALS_A ,ALLOCATEDTOSPENDROLLUP_C ,ALLOCATEDTOSPENDROLLUP_A ,COMMITTEDROLLUP_C ,COMMITTEDROLLUP_A ,SPENTROLLUP_C ,SPENTROLLUP_A ,ACTUALSROLLUP_C ,ACTUALSROLLUP_A ,AVAILABLEROLLUP_C ,AVAILABLEROLLUP_A ,DESCRIPTION ,STARTDATE ,ENDDATE ,COLOR ) Select TEPLAN.PLANID ,TEPLAN."TITLE" ,TEPLAN.FISCALYEAR ,TEPLAN.STATUS ,TEPLAN.PLANID As MARKETINGACTIVITYID ,0 As PARENT_ACTIVITY_ID ,0 As ACTIVITYLEVEL ,TEPLAN."TITLE" As ACTIVITYNAME ,Cast(NULL As VARCHAR(510)) ,Cast(NULL As VARCHAR(400)) ,BU.NAME ,BU.PATH ,FS1.FINANCIALSUMMARYID ,FS1.BUDGETED_C ,FS1.BUDGETED_A ,FS1.PLANNED_C ,FS1.PLANNED_A ,FS1.COMMITTED_C ,FS1.COMMITTED_A ,FS1.SPENT_C ,FS1.SPENT_A ,FS1.AVAILABLE_C ,FS1.AVAILABLE_A ,FS1.UNALLOCATED_C ,FS1.UNALLOCATED_A ,FS1.LASTUPDATEDON ,FS1.ALLOCATEDTOSPEND_C ,FS1.ALLOCATEDTOSPEND_A ,FS1.ACTUALS_C ,FS1.ACTUALS_A ,FS1.ALLOCATEDTOSPENDROLLUP_C ,FS1.ALLOCATEDTOSPENDROLLUP_A ,FS1.COMMITTEDROLLUP_C ,FS1.COMMITTEDROLLUP_A ,FS1.SPENTROLLUP_C ,FS1.SPENTROLLUP_A ,FS1.ACTUALSROLLUP_C ,FS1.ACTUALSROLLUP_A ,FS1.AVAILABLEROLLUP_C ,FS1.AVAILABLEROLLUP_A ,FS1.DESCRIPTION ,TEPLAN.FROMDATE ,TEPLAN.TODATE ,'#3366cc' From TEOBJECT_USP TE Inner Join alent_plan TEPLAN On TE.Filteredobjid = TEPLAN.planid Left Outer Join ALOM_ASSOENTITYFACET assm1 On TEPLAN.planid = assm1.entityid And assm1.relationshiptypeid = 43297593141162 Left Outer Join ALFCT_FINANCIALSUMMARY FS1 On assm1.Facetid = FS1.FINANCIALSUMMARYid Left Outer Join alom_assembly assm2 On TEPLAN.planid = assm2.containerid And assm2.relationshiptypeid = 43297593141162 Left Outer Join alom_assembly assm3 On TEPLAN.planid = assm3.containerid And assm3.relationshiptypeid = 141014623932230 Left Outer Join ALENT_BUSINESSUNIT BU ON assm3.CONTAINSID = BU.BUSINESSUNITID SELECT STATEMENT FOR RPT_PLANDETAILS SELECT * FROM RPT_PLANDETAILS; And the select returns the result set as (only first two columns taken) rptid PLANID 5 129338552046971 100002 64920581096087 3 202024997033706 1 240189122709774 100003 132172892550504 100001 223298109564009 6 136189019865328 4 64564675477528 2 82377621204850 Expected result set is rptid PLANID 5 129338552046971 9 64920581096087 3 202024997033706 1 240189122709774 8 132172892550504 7 223298109564009 6 136189019865328 4 64564675477528 2 82377621204850 Satish Gaikwad Post #11813 Adeel.Chaudhry Posted 6/12/2008 1:50:43 AM Supreme Being Group: Forum Members Last Login: Yesterday @ 7:51:48 AM Posts: 159, Visits: 457 The IDENTITY column is not generated in sequence i.e. you can not expect TD to generate value as 1,2,3,4,5...! The generation of IDENTITY column is AMP dependent. The value will surely be unique, but not is sequence. The error you specified before may be because of overflow in any other field, but i doubt if it has anything to do with the IDENTITY column... HTH. Regards, Adeel Post #11814 meetsatishg@gmail.com Posted 6/12/2008 2:04:09 AM Junior Member Group: Forum Members Last Login: 7/7/2008 2:17:32 AM Posts: 24, Visits: 90 Thanks, Is there any solution TD which will give me sequence no like IDENTITY field in SQL? Satish Gaikwad Post #11815 Adeel.Chaudhry Posted 6/12/2008 3:19:16 AM Supreme Being Group: Forum Members Last Login: Yesterday @ 7:51:48 AM Posts: 159, Visits: 457 I guess no! If you want to have sequence number using SQL you can use ROW_NUMBER or RANK (depends on your requirement). HTH. Regards, Adeel Post #11818 meetsatishg@gmail.com Posted 6/12/2008 4:02:45 AM Junior Member Group: Forum Members Last Login: 7/7/2008 2:17:32 AM Posts: 24, Visits: 90 Hi , Can you please tell me How to use these ROW_NUMBER and RANK in above case... Satish Gaikwad Post #11819 « Prev Topic | Next Topic »

Reading This Topic Active Users: 0 ( 0 guests, 0 members, 0 anonymous members ) No members currently viewing this topic.

Forum Jump... ---------------- Forum Home Search Members List Calendar Who's Online ----------------  Teradata   |-- Analytical Applications   |-- Data Warehousing   |-- Drivers and UDFs   |-- Jobs   |-- Marketing Tips and CRM Best Practices   |-- PARTNERS 2008   |-- Partner software applications/questions   |-- Teradata Advanced Analytics User Group   |-- Teradata Database   |-- Teradata Tools and Utilities   |-- Training  Teradata Discussion Forum Guidelines   |-- Forum Guidelines and Rules All times are GMT -5:00, Time now is 7:55pm

Powered By InstantForum.NET v4.1.4 © 2008 Execution: 0.094. 9 queries. Compression Disabled.