Generating Sequence Number

Database

Generating Sequence Number



I have a table with four columns as below:

col1 col2 col3 col4

a ab abc de
b bc bcd fg
....

.....

Column 1 has five different possible values
column 2 has 22 different possible values
column 3 has 47 different possible values
column 4 has 188 different possible values

All 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.

Thanks

Satish
9 REPLIES
Enthusiast

Re: Generating Sequence Number

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
Enthusiast

Re: Generating Sequence Number


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

Re: Generating Sequence Number

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.g
select empno, csum(1,1,1) from stumark
Enthusiast

Re: Generating Sequence Number

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;
Enthusiast

Re: Generating Sequence Number

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
Enthusiast

Re: Generating Sequence Number

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

Enthusiast

Re: Generating Sequence Number

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 ?

Supporter

Re: Generating Sequence Number

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.

Supporter

Re: Generating Sequence Number

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