| < 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.
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 > |
|