Archives of the TeradataForum

Message Posted: Wed, 09 Dec 2009 @ 17:47:04 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Paul

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

Or

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

Cheers

Jon Downs

	<Prev	Next>		<<First	<Prev	Next>	Last>>