Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Populate columns with row number
 
From:   Jonathan Downs

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023