FETCH FIRST n ROWS ONLY - IBM DB2 to Oracle Migration

In DB2, you can use FETCH FIRST n ROWS ONLY clause in a SELECT statement to return only n rows, and it is applied after rows are sorted as specified in the ORDER BY clause.

IBM DB2

  -- Sample table
  CREATE TABLE cities (name VARCHAR(70));
 
  -- Sample data
  INSERT INTO cities VALUES ('Paris');
  INSERT INTO cities VALUES ('New York');
  INSERT INTO cities VALUES ('Prague');
  INSERT INTO cities VALUES ('Alanya'); 
  INSERT INTO cities VALUES ('London');

Let's use FETCH FIRST 3 ROWS ONLY without ORDER BY:

  -- Rows are not ordered
  SELECT name FROM cities FETCH FIRST 3 ROWS ONLY;

Result:

Now let's use FETCH FIRST 3 ROWS ONLY with ORDER BY:

  -- Rows are ordered now
  SELECT name FROM cities ORDER BY name FETCH FIRST 3 ROWS ONLY;

Result:

In Oracle, you can use ROWNUM pseudo-column to limit the number of retrieved rows, but it is applied before sorting, so you have to use a sub-query in order to limit the number of rows after sorting.

If there is no ORDER BY clause in the original DB2 query, you can just add ROWNUM condition as follows:

Oracle:

  -- Rows are not ordered
  SELECT name FROM cities WHERE rownum <= 3;

Result:

But if you need to sort rows before applying ROWNUM, you have to use a subquery:

  -- Rows are ordered now
  SELECT * 
  FROM (SELECT name FROM cities ORDER BY name)
  WHERE rownum <= 3;

Result:

• IBM DB2 to Oracle Migration Tools and Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - April 2013.