All Forums Analytics
terasuman 3 posts Joined 07/06
11 Jul 2006
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!!!

DaveC 5 posts Joined 03/06
25 Jul 2006

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?Dave

You must sign in to leave a comment.