Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-09-2006
01:35 PM

11-09-2006
01:35 PM

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 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-10-2006
03:55 AM

11-10-2006
03:55 AM

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

Highlighted
##

could you pls post, a sample of your espected output?

i beleive, with rownum itself, we could generate this.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-11-2006
12:33 AM

11-11-2006
12:33 AM

Re: Generating Sequence Number

could you pls post, a sample of your espected output?

i beleive, with rownum itself, we could generate this.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-11-2006
04:49 AM

11-11-2006
04:49 AM

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

e.g

select empno, csum(1,1,1) from stumark

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-13-2006
12:24 AM

11-13-2006
12:24 AM

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;

If u want to generate a sequence number u can try like thi

SELECT

RANK() OVER (ORDER BY col1,col2,col3,col4 )

FROM

TABLEX;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-13-2006
12:17 PM

11-13-2006
12:17 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-15-2013
03:14 AM

05-15-2013
03:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-15-2013
03:15 AM

05-15-2013
03:15 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-15-2013
04:52 AM

05-15-2013
04:52 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-15-2013
04:54 AM

05-15-2013
04:54 AM

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