Generating row numbers

[craig322]

I am trying to generate a row sequence number for a table.

I have tried the following two sqls, but get an error message about illegal alias on the update statement.

Does anyone have any ideas?

Thanks

Craig

CREATE SET TABLE dssbatch.tmp_test,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
AS ( SELECT a.rowid as tmp_rowid, count(b.rowid) as tmp_count
FROM dssbatch.test b, dssbatch.test a
WHERE b.rowid <= a.rowid
GROUP BY a.rowid)
WITH DATA

update dssbatch.test a
from dssbatch.tmp_test b
set row_count = tmp_count
where b.tmp_rowid = a.rowid

 

[tdgulbra]

I think you need to provide the table alias on the &quot;set&quot; clause (i.e., &quot;set b.row_count = a.tmp_count&quot, but I could be mistaken.

 

[craig322]

Here is a little more info on this problem.

When I run the first query it returns every row in the table (which it should).

When I run the UPDATE statement, it doesn't update anything.
I changed the UPDATE statement slightly to get around the
original error.
Notice the WHERE clauses are identical.

SELECT b.tmp_rowid, a.rowid
FROM dssbatch.tmp_test b, dssbatch.test a
WHERE b.tmp_rowid = a.rowid

UPDATE a
FROM dssbatch.tmp_test b, dssbatch.test a
SET row_count = tmp_count
WHERE b.tmp_rowid = a.rowid

Craig

 

[BillDHS]

You can use either the RANK or CSUM functions to add a sequence number

select name, csum(1,1), rank(name) from DBName.Tname

Be careful with rank, duplicate rank values will get the same rank number. See Teradata reference manuals for additional info.

 

[craig322]

Using CSUM worked great.

Thanks Bill