InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comThu, 20 Nov 2008 10:50:58 GMT20http://www.teradata.com/teradataforum/Topic11800-9-1.aspxHi ,Can you please tell me How to use these ROW_NUMBER and RANK in above case...Thu, 12 Jun 2008 04:02:45 GMTmeetsatishg@gmail.comhttp://www.teradata.com/teradataforum/Topic11800-9-1.aspxI guess no!If you want to have sequence number using SQL you can use ROW_NUMBER or RANK (depends on your requirement).HTH.Regards,AdeelThu, 12 Jun 2008 03:19:16 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11800-9-1.aspxThanks,Is there any solution TD which will give me sequence no like IDENTITY field in SQL?Thu, 12 Jun 2008 02:04:09 GMTmeetsatishg@gmail.comhttp://www.teradata.com/teradataforum/Topic11800-9-1.aspxThe 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,AdeelThu, 12 Jun 2008 01:50:43 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11800-9-1.aspxHi,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 ISInsert 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 TEInner Join alent_plan TEPLAN On TE.Filteredobjid = TEPLAN.planidLeft Outer Join ALOM_ASSOENTITYFACET assm1 On TEPLAN.planid = assm1.entityid And assm1.relationshiptypeid = 43297593141162Left Outer Join ALFCT_FINANCIALSUMMARY FS1 On assm1.Facetid = FS1.FINANCIALSUMMARYid Left Outer Join alom_assembly assm2 On TEPLAN.planid = assm2.containerid And assm2.relationshiptypeid = 43297593141162Left Outer Join alom_assembly assm3 On TEPLAN.planid = assm3.containerid And assm3.relationshiptypeid = 141014623932230Left Outer Join ALENT_BUSINESSUNIT BU ON assm3.CONTAINSID = BU.BUSINESSUNITIDSELECT STATEMENT FOR RPT_PLANDETAILSSELECT * FROM RPT_PLANDETAILS;And the select returns the result set as (only first two columns taken)rptid PLANID5 129338552046971100002 649205810960873 2020249970337061 240189122709774100003 132172892550504100001 2232981095640096 1361890198653284 645646754775282 82377621204850Expected result set is rptid PLANID5 1293385520469719 649205810960873 2020249970337061 2401891227097748 1321728925505047 2232981095640096 1361890198653284 645646754775282 82377621204850Thu, 12 Jun 2008 01:16:00 GMTmeetsatishg@gmail.comhttp://www.teradata.com/teradataforum/Topic11800-9-1.aspxHello,Can you provide the INSERT SELECT statement?Regards,AdeelWed, 11 Jun 2008 23:12:48 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11800-9-1.aspxHi ,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 PLANID1 240189122709774100001 649205810960872 645646754775284 136189019865328100003 2232981095640096 82377621204850100001 1321728925505043 1293385520469715 202024997033706When i am taking union of two select sets and inserting into this table then i am getting following errorCode = 2616.Statement 1 - 2616: USP_RPT_PLAN_DETAILS:Numeric overflow occurred during computation. Output directed to Answerset windowWed, 11 Jun 2008 08:56:42 GMTmeetsatishg@gmail.com