sequence numbers

[mvnrk]

Hi

How can I create a sequence numbers in Teradata. I like to assign sequence numbers to specific integer column in ascending order. I have a incremental data load using mload.


Thanks
mvnrk

 

[dnoeth]

Sequences have been implemented in V2R5, but they're not guaranteed to be in ascending order.
And you can't use tables with sequences with MultiLoad/FastLoad before V2R5.1.

So the easiest way is probably an INMOD, which maintains the sequence.
Another way is to load to a stage table and then INSERT/SELECT into target table with an OLAP-function assigning the sequence.

When you search the forum you'll probably find some threads on that topic...

Dieter

 

[mvnrk]

Hi dnoeth (Instructor)

Thanks for your reply.
Can you elaborate on generating Sequence numbers in ascending order using Inmode or OLAP functions.

Thanks
mvnrk

 

[dnoeth]

For Fastload there's an example in the manuals:
Appendix C: INMOD and Notify Exit Routine Examples
-> For UNIX -> BLKEXIT.C Sample INMOD

On Windows you'll find BLKEXIT.C in
C:\Program Files\NCR\Teradata Client\FastLoad


Sequence using OLAP functions:

create table seqtest
(
seq int not null,
d date
) unique primary index(seq);

ins into seqtest
sel
csum(1, calendar_date) + (coalesce(dt.maxseq, 0)),
calendar_date
from (select calendar_date from sys_calendar.calendar sample 1000) c
cross join (select max(seq) as maxseq from seqtest) dt
;

Run that Ins/Sel several times...

This works since V2R3, but it's not Standard SQL, so here are some SQL:1999 compliant versions of csum(1, calendar_date):
V2R4+:
sum (1) over (order by calendar_date asc
rows unbounded preceding)

V2R5+:
row_number() over (order by calendar_date asc)


Dieter