We have come across rownum function in oracle . This function gives number for every row/record in Oracle. In teradata there is no direct approach like in oracle. There are various approaches suggested around.
Here Sum function is used over rows preceding in the SOURCE_TABLE
sum(1) over( rows unbounded preceding ),
Here ROW_NUMBER function is used to generate row_number on columnA
ROW_NUMBER() over( ORDER BY columnA ),
If you have to use the row number concept in target table as well, then following approach using "identity column" (from V2R6 onwards ) be used :
CREATE MULTISET TABLE TARGET_TABLE
columnA INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 20) ,
columnB VARCHAR(20) NOT NULL
UNIQUE PRIMARY INDEX pidx (ColA);
P.S: Identity columns does differ from sequence concept in oracle. The numbers assigned in these columns are not guaranteed to be sequenctial. The Identity column in Teradata is used to guaranteed row-uniqueness.
This works without use of Identity approach.
create TABLE TARGET_TABLE as
ROW_NUMBER() over( ORDER BY columnA ) NUMBER_SEQ ,
from a join b on a.id=b.id
) with data ;