Hi Friends,
I want to generate sequence number in Teradata and I came to know that csum(1,1) is used to generate..but I want some more regarding this ..If you have any idea pls let me know .
Similarly Rank() over is used to retrieve the last sequence numer.So I want more info on this also.
How to generate sequence Number in Teradata
Moderators: chulett, rschirm, roy
-
chrisjones
- Participant
- Posts: 194
- Joined: Thu May 11, 2006 9:42 am
How to generate sequence Number in Teradata
Thanks,
Chris Jones
Chris Jones
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
chrisjones
- Participant
- Posts: 194
- Joined: Thu May 11, 2006 9:42 am
How to generate sequence Number in Teradata
Ok..Thanks
ray.wurlod wrote:Posting this question on a Teradata forum might be more fruitful, or looking in your Teradata manuals/help files.
Thanks,
Chris Jones
Chris Jones
-
nishadkapadia
- Charter Member
- Posts: 47
- Joined: Fri Mar 18, 2005 5:59 am
Re: How to generate sequence Number in Teradata
Using Rank() function is better, since sequence number could be discontinous.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I think that the OP wants to generate a sequence number for INSERT, not for SELECT. RANK is a display function, used with SELECT.
If a sequence exists in Teradata, then it should have a CURRVAL and NEXTVAL property (note that these are the Oracle names; the Teradata names may vary slightly). You should be able to invoke the NEXTVAL property of the sequence.
This will, of course, introduce a bottleneck in a parallel job with more than one processing node, unless Teradata has techniques for allocating different blocks of sequence numbers to different processes.
If a sequence exists in Teradata, then it should have a CURRVAL and NEXTVAL property (note that these are the Oracle names; the Teradata names may vary slightly). You should be able to invoke the NEXTVAL property of the sequence.
This will, of course, introduce a bottleneck in a parallel job with more than one processing node, unless Teradata has techniques for allocating different blocks of sequence numbers to different processes.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
nishadkapadia
- Charter Member
- Posts: 47
- Joined: Fri Mar 18, 2005 5:59 am
Hi,
One can do an insert..select statement using the Rank() function.
It is correct that the sequence number is allocated in a batch to the various amps, hence should not be a problem while insertion however the possibility of it being discontinuous.
One can actually do a rank()+1 function so as to start from the last sequence number.
However, if that is not a requirement that the sequence number to be continous, one can go ahead minus the rank function.
IMHO
One can do an insert..select statement using the Rank() function.
It is correct that the sequence number is allocated in a batch to the various amps, hence should not be a problem while insertion however the possibility of it being discontinuous.
One can actually do a rank()+1 function so as to start from the last sequence number.
However, if that is not a requirement that the sequence number to be continous, one can go ahead minus the rank function.
IMHO