Archives of the TeradataForum
Message Posted: Wed, 09 Dec 2009 @ 17:47:04 GMT
A few ideas that are starting points:
1. You could use GENERATED in the table DDL:
CREATE MULTISET TABLE atable ( a1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 .....);
2. use CSUM(1,1) to produce and abstract incrementing number.
3. use CSUM(1,column(s)) to produce an incrementing number in the order of the column(s). Order is 100% guaranteed if the column(s) used are a Unique Key.
As CSUM is generally frowned upon as they are not, strictly speaking, ANSI compliant - it is better to use the following:
4. ROW_NUMBER() OVER (ORDER by column(s))
5. SUM(1) OVER (ORDER BY column(s) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
I hope this helps - generally you may have problems with putting analytical functions straight into Updates (well we do on our box anyway - I get 'Illegal use of Ordered Analytical Function operation in an UPDATE statement').
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|