Equivalent of Oracle-SEQUENCE_NAME.NEXTVAL in Teradata


Equivalent of Oracle-SEQUENCE_NAME.NEXTVAL in Teradata

Hi All,

I want to know how to access the Nextval in a Sequence (say SEQ_NAME.NEXTVAL in Oracle)and its implementation in Teradata... Please suggest!!!

Re: Equivalent of Oracle-SEQUENCE_NAME.NEXTVAL in Teradata

I've only been using Teradata for a few months, so don't take this as gospel, but....

The short answer is that you can't - there is no equivalent.

The more nuanced answer is that you might be able to do something to recreate the specific functionality you're using a sequence for.

1) If you just need a unique number in a table, you can use an identity field. (This won't work if you need a unique number that also increases predictably, say with insert time, since identity values are assigned in blocks to each AMP.)

2) If you need a unique number over a set of n tables, create an identity field that starts at a different number in each table (from 1 to n) and increment them all by n. (Same issue with predictable increments as above.)

3) If you're doing a limited number of inserts from a controlled interface, you could use stored procedures and a queue table, but this could be problematic if there are lots of transactions and rollbacks. My guess is this will have pretty limited usefulness.

4) If it's a one-time bulk insert, add row_number() over (order by [some field or a constant]) as another column in the query. (Ordering by a constant will give you an unpredictable order.)

5) If it's like 4) but into an already-populated table, you can add a select max([counter field]) from the target table to the query, then add that number to the row_number() over(etc). Could be ugly with a table scan to get that max value, but no sweat if there's a secondary index.

More approaches, anyone?