Archives of the TeradataForum

Message Posted: Thu, 31 Jan 2002 @ 15:24:22 GMT

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

Teradata doesn't have sequences, but you can achieve the same effect with a table of one row and a macro.

Table:
create table my_sequence
(arbitrary_key integer,
seq_number integer )
unique primary index(arbitrary_key);

insert into my_sequence values( 1, 0 );

The arbitrary_key is set to 1 and never changed; in this way, the one row in this table will always stay on the same AMP.

Now you define your macro to return the next number in the sequence. Just be careful to ensure that, if two users are trying to use the macro at the same time, one is forced to wait for the other. The macro will go something like this (UNTESTED):

create macro next_seq_num as (
begin transaction;
update my_sequence
set seq_number = seq_number + 1
where arbitrary_key = 1; /* This is a one-AMP operation. */
select seq_number from my_sequence
where arbitrary_key = 1;
end transaction;
);

This could also be done with a stored procedure, but I think a macro would work just as well or better.

(I proposed this same approach a few weeks ago. Unfortunately, I can't find a copy of my previous memo, so I have repeated myself.)

--wgr

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