Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

- 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

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

- 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

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

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

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

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

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

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

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.