|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/24/2008 8:31:03 AM
Posts: 109,
Visits: 22
|
|
I have to generata a Sequence of numbers as the primary index.
But , after the insertions of some sample data, i find that it is not completely in sequence. I get numbers from 1 thru 10, then numbers from 1000001 thru 1000010, and so on ...
I understand that this is because the identity column values are generated on an amp-local basis, and hence the result. But, somehow we need the data to be essentially in sequence for more than 500 million rows.
Is there any way to do this?
Thanks.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/27/2006 9:11:00 AM
Posts: 7,
Visits: 1
|
|
I'm a little unsure of using IDENTITY even with DECIMAL(18) because of this parallel implementation, since the 5753 error - Numbering for Identity Column columnname is over its limit - happens if the column is INTEGER when there's still room for lots of values.
In other words, if the upper bound has been reached but there are still gaps between existing values, does TD use those values (as we desire) or can that same error happen?
Other question is if the behavior varies depending if you INSERT with VALUES or INSERT SELECT.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/19/2008 2:41:24 PM
Posts: 15,
Visits: 2
|
|
Try this:
insert into rahulj.partitbl (unique_column,mycolumn)
select
row_number() over (order by mycolumn)
,mycolumn
from
rahulj.partitbl
;
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/24/2008 8:31:03 AM
Posts: 109,
Visits: 22
|
|
This also works:
insert partitbl (k)
SELECT sum(1) over (rows unbounded preceding) As ROW_ID from dbc.databases;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/10/2006 9:01:00 AM
Posts: 2,
Visits: 1
|
|
hi,
In Teradata, is there an equivalent of the Sequence feature such as in Oracle? Identity is good but currently you cannot copy table with identity column, also it's less than ideal that you have gaps in the numbers assigned.
thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/24/2008 8:31:03 AM
Posts: 109,
Visits: 22
|
|
|
No, you dont have anything thats same as Sequence of Oracle.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/10/2006 9:01:00 AM
Posts: 2,
Visits: 1
|
|
thanks,
now in Teradata V2R6 we cannot directly copy a table containing Identity column - is there any recommended workaround that would allow us to copy such a table in 2 or 3 steps? but still preserving its Identity column, AND still preserving the actual ID's assigned.
thank you
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/20/2006 5:11:00 PM
Posts: 2,
Visits: 1
|
|
Hi
We too have spent weeks trying to find a sequence eqivalent in Teradata .Apparently there is none. If the requirement is not sequqnce but unique number which should not be repeated in any case then u can implement this way :
CREATE SET TABLE cfdw2_trr_tbls.davidtest_all_obligors ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
oblgr_num INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MAXVALUE 2147483647
NO CYCLE),
OBLGR_NUM_OLD INTEGER)
UNIQUE PRIMARY INDEX ( oblgr_num );
UPI will ensure that it is never repeated.
If u need sequence solution then u need to create a seperate table : sequence and write trigger (after insert on the table for which u wish to implement it and within this proc u need to implement the logic ). I have done it and if u want i can send u the code as well .
Rgds
Vikas
|
|
|
| | |