5.14 Rownum

< Day Day Up >

Oracle uses ROWNUM pseudo-column to control the number of rows returned from an SQL statement. In DB2 you determine the number of rows to read with the FETCH FIRST n ROWS ONLY statement.

Table 5-5 bellow shows how different statements can be converted.

Table 5-5: Mapping of ROWNUM function
	Oracle	DB2 UDB
SELECT	select * from tab1 where ROWNUM < 2	select * from tab1 FETCH FIRST 1 ROW ONLY;
UPDATE	update tab1 set c1 = v1 where c2 = v2 and ROWNUM <= 10	FOR lv as temp_cur CURSOR FOR SELECT * FROM tab1 WHERE c2 = v2 FETCH FIRST 10 ROWS ONLY FOR UPDATE DO UPDATE tab1 SET c1 = v1 WHERE CURRENT OF temp_cur; END FOR; With FixPak 4 or above: UPDATE (select c1 from tab1where c2=v2 fetch first 10 rows only) set c1=v1
DELETE	delete from tab1 where ROWNUM <= 100	FOR lv as temp_cur CURSOR FOR SELECT * FROM tab1 FETCH FIRST 100 ROWS ONLY FOR UPDATE DO DELETE FROM tab1 WHERE CURRENT OF temp_cur; END FOR; With FixPak 4 or above: DELETE FROM (SELECT 1 FROM tab1 FETCH FIRST 100 ROWS ONLY)

Table 5-5: Mapping of ROWNUM function

Oracle

DB2 UDB

 SELECT

 select * from tab1 where ROWNUM < 2

 select * from tab1 FETCH FIRST 1 ROW ONLY;

 UPDATE

 update tab1 set c1 = v1 where c2 = v2 and ROWNUM <= 10

 FOR lv as temp_cur CURSOR FOR   SELECT *     FROM tab1     WHERE c2 = v2     FETCH FIRST 10 ROWS ONLY   FOR UPDATE DO     UPDATE tab1     SET c1 = v1     WHERE CURRENT OF temp_cur; END FOR; With FixPak 4 or above: UPDATE (select c1 from tab1where c2=v2 fetch first 10 rows only) set c1=v1

 DELETE

 delete from tab1 where ROWNUM <= 100

 FOR lv as temp_cur CURSOR FOR   SELECT * FROM tab1   FETCH FIRST 100 ROWS ONLY   FOR UPDATE DO     DELETE FROM tab1     WHERE CURRENT OF temp_cur; END FOR; With FixPak 4 or above: DELETE FROM (SELECT 1 FROM tab1 FETCH FIRST 100 ROWS ONLY)

< Day Day Up >