For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. (Oracle SQL Language Reference Rownum pseudocolumn)
Snowflake does not have an equivalent for ROWNUM. The approach for the transformation is taking advantage of the Snowflake seq8 function to emulate the functionality.
ROWNUM
Sample Source Patterns
Oracle
IN -> Oracle_01.sql
-- Table with sample dataCREATETABLETABLE1(COL1 VARCHAR(20), COL2 NUMBER);INSERT INTO TABLE1 (COL1, COL2) VALUES('ROWNUM: ', null);INSERT INTO TABLE1 (COL1, COL2) VALUES('ROWNUM: ', null);-- Query 1: ROWNUM in a selectSELECT ROWNUM FROM TABLE1;-- Query 2: ROWNUM in DMLUPDATE TABLE1 SET COL2 = ROWNUM;SELECT*FROM TABLE1;
-- Table with sample dataCREATE OR REPLACETABLETABLE1 (COL1 VARCHAR(20),COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';INSERT INTO TABLE1(COL1, COL2) VALUES('ROWNUM: ', null);INSERT INTO TABLE1(COL1, COL2) VALUES('ROWNUM: ', null);-- Query 1: ROWNUM in a selectSELECTseq8() +1FROMTABLE1;-- Query 2: ROWNUM in DMLUPDATE TABLE1SET COL2 = seq8() +1;SELECT*FROMTABLE1;