Add sequential number to create / paste - Teradata

Question

In oracle, we will use rownum on select when creating this table. Now in teradata I can't get it to work. There is no column in which I can sort and have unique values ​​(lots of duplication) unless I use 3 columns together.

The old way would be something like

create table temp1 as 
  select
    rownum as insert_num,
    col1,
    col2,
    col3
  from tables a join b on a.id=b.id
;

Answer 1

Here's how you can do it:

create table temp1 as 
( 
   select
      sum(1) over( rows unbounded preceding ) insert_num
     ,col1
     ,col2
     ,col3
   from a join b on a.id=b.id
) with data ;

Answer 2

Teradata , V2R6.x. Oracle , . Teradata .

:

CREATE MULTISET TABLE MyTable
  (
   ColA INTEGER GENERATED BY DEFAULT AS IDENTITY
       (START WITH 1
        INCREMENT BY 20)
   ColB VARCHAR(20) NOT NULL
  )
UNIQUE PRIMARY INDEX pidx (ColA);

, ColA . , PI .

Answer 3

This also works:

create table temp1 as 
( 
   select
   ROW_NUMBER() over( ORDER BY col1 ) insert_num
   ,col1
   ,col2
   ,col3
   from a join b on a.id=b.id
) with data ;