Generation of IDENTITY values for a column

Database
Enthusiast

Generation of IDENTITY values for a column

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
6 REPLIES
Teradata Employee

Re: Generation of IDENTITY values for a column

Hello,

Can you provide the INSERT SELECT statement?

Regards,

Adeel
Enthusiast

Re: Generation of IDENTITY values for a column

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

Teradata Employee

Re: Generation of IDENTITY values for a column

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
Enthusiast

Re: Generation of IDENTITY values for a column

Thanks,

Is there any solution TD which will give me sequence no like IDENTITY field in SQL?

Teradata Employee

Re: Generation of IDENTITY values for a column

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
Enthusiast

Re: Generation of IDENTITY values for a column

Hi ,

Can you please tell me How to use these ROW_NUMBER and RANK in above case...