Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Sequences
From:   Geoffrey Rommel

  in oracle there are constructs called sequences. Is there something similar in TD? I have some table and want to count one column up with every insert.  

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

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.)


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023