ROWNUM

Translation spec for ROWNUM pseudocolumn

Description

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

Query

IN -> Oracle_01.sql

-- Table with sample data
CREATE TABLE TABLE1(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 select
SELECT ROWNUM FROM TABLE1;

-- Query 2: ROWNUM in DML
UPDATE TABLE1 SET COL2 = ROWNUM;
SELECT * FROM TABLE1;

Result Query 1

|ROWNUM|
|------|
|1     |
|2     |

Result Query 2

|COL1    |COL2|
|--------|----|
|ROWNUM: |1   |
|ROWNUM: |2   |

Snowflake

Query

OUT -> Oracle_01.sql

-- Table with sample data
CREATE OR REPLACE TABLE TABLE1 (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 select
SELECT
seq8() + 1
FROM
TABLE1;

-- Query 2: ROWNUM in DML
UPDATE TABLE1
SET COL2 = seq8() + 1;

SELECT * FROM
TABLE1;

Result Query 1

|SEQ8() + 1|
|----------|
|1         |
|2         |

Result Query 2

|COL1    |COL2|
|--------|----|
|ROWNUM: |1   |
|ROWNUM: |2   |

Known Issues

No issues were found.

Related EWIs

1. SSC-FDM-0006: Number type column may not behave similarly in Snowflake