All Forums Database
satish123 3 posts Joined 11/06
09 Nov 2006
Generating Sequence Number

I have a table with four columns as below:col1 col2 col3 col4a ab abc deb bc bcd fg.........Column 1 has five different possible valuescolumn 2 has 22 different possible valuescolumn 3 has 47 different possible valuescolumn 4 has 188 different possible valuesAll columns are related I was just wondering if I could create a sequence number for values in each column on the fly. I tried to use row number but in vain. Could some one let me know how this could be done in Teradata.ThanksSatish

leo.issac 184 posts Joined 07/06
10 Nov 2006

can you try using csum? I remember similar question was posed earlier and I hope someone suggested csum as an option.Please read previous threads and find out if csum helps in your case

SarathyG 31 posts Joined 09/06
11 Nov 2006

could you pls post, a sample of your espected output?i beleive, with rownum itself, we could generate this.

- Sarathy G

SarathyG 31 posts Joined 09/06
11 Nov 2006

try csum(1,1,1) as a separate column along with your other columns. it would generate the seq nums. starting from 1...incremented by 1.e.gselect empno, csum(1,1,1) from stumark

- Sarathy G

GogulM 32 posts Joined 08/06
13 Nov 2006

Hi,If u want to generate a sequence number u can try like thi SELECT RANK() OVER (ORDER BY col1,col2,col3,col4 ) FROM TABLEX;

~Gogul

BBR2 96 posts Joined 12/04
13 Nov 2006

CSUM has been retained for backward compatibility.You may want to use ROW_NUMBER function.sel row_number() over(order by databasename, tablename),databasename,tablename,tablekind from dbc.tables Hope the above SQL will help.Vinay

15 May 2013

Hi Team,
  I’m just inserting ‘9925203’ records into backup tables which is having SEQ_NUM

SEQ_NUM decimal(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 2147483647 
            NO CYCLE)

minimum(SEQ_NUM)	   Maximum(SEQ_NUM)
1	                   20,322,127

 
 
 
1                                                                                                       20,322,127

Thanx,
Mahesh

15 May 2013

 
Why it is showing maxvalue is greather than actuall row count?
select count(1) from prod_olap_bkp.w_plrevn_op_a--9,925,203
Why it is weird behaving ?

Thanx,
Mahesh

ulrich 816 posts Joined 09/09
15 May 2013

becaus the increment by 1 is handled vproc local.
Identity columns are not guarantee a sequence - they only quarantee unique ids.
Each vproc request a range of numbers he can assign next. The size of the range is controlled via a dbs controll field - and don't even think of setting this to 1 as you would not have a MPP process any longer.
So assumumg the dbs controll field is stateing 1000 and you have 2 vprocs.
so the first vproc will start with 1 and the second with 1001. 
If all numbers of the range assigned to the vproc are used he is requesting a new range (next would be 2000) etc.
So if you add only 100 rows per vproc you would see (in perferct world) values between 1 and 100 and 1001 and 1100.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
15 May 2013

P.S. read the documentation carefully before using identity columns. There are other issues as well which need to be considered...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.