Conversion of Microsoft SQL Server TOP clause to Oracle

Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.

You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.

Check out the relevant toolkit documentation.

Ispirer SQL Server to Oracle Migration overview.

Ispirer SQLWays Database Migration Software
Conversion of Microsoft SQL Server TOP clause to Oracle

The Microsoft SQL Server TOP clause limits the number of rows returned by a SELECT statement. TOP allows specifying the number or percentage of rows to return. If the SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set.

The Oracle ROWNUM pseudocolumn returns a number indicating the order in which the row was selected in the result set. ROWNUM can be used to limit rows in Oracle, but ROWNUM is assigned before ordering.

SQLWays converts the Microsoft SQL Server TOP clause to Oracle as follows:

a)SELECT statement with TOP does not contain the ORDER BY clause.

If Microsoft SQL Server does not contain the ORDER BY clause, SQLWays implements the TOP clause using the ROWNUM pseudo column in the WHERE clause of the SELECT statement in Oracle.

If TOP is specified with the PERCENT clause SQLWays calculates the total number of rows retuned by the query and the number of rows corresponding to the specified percentage.
TABLE 36. SELECT statement with TOP does not contain the ORDER BY clause
Microsoft SQL Server

Oracle
create procedure sql_sp_select_top
as
DECLARE @a number
select top 1 @a=col1     from tab1 
CREATE OR REPLACE PROCEDURE sql_sp_select_top
AS
v_a VARCHAR2(255);
BEGIN
select col1 INTO v_a from tab1 WHERE ROWNUM <=1;
END; 
create procedure sql_sp_select_top2
as
DECLARE @a number
select top 1 @a=col1     from tab1 WHERE col2>0 
CREATE OR REPLACE PROCEDURE sql_sp_select_top2
AS
v_a VARCHAR2(255);
BEGIN
select col1 INTO v_a from tab1 WHERE col2>0 and ROWNUM <=1;
END; 
create procedure sql_sp_select_top3
as
DECLARE @a number
select top 30 percent @a=col1     from tab1 
CREATE OR REPLACE PROCEDURE sql_sp_select_top3
AS
v_a VARCHAR2(255);
BEGIN
select col1 INTO v_a from tab1 WHERE ROWNUM <=
30 *(SELECT COUNT(*) from tab1) / 100;
END; 
b) SELECT statement with TOP also contains the ORDER BY clause.

Unlike Microsoft SQL Server, Oracle applies comparison with ROWNUM before ordering the result set. If the Microsoft SQL Server SELECT statement contains the ORDER BY clause, SQLWays converts the source query to the query with the subquery. The subquery performs the ordering, while the query performs row restricting using ROWNUM.
TABLE 37. SELECT statement with TOP also contains the ORDER BY clause
Microsoft SQL Server

Oracle
create procedure sql_sp_select_top4
as
DECLARE @a number
select top 1 @a=col1     from tab1 order by col1 
create or replace procedure sql_sp_select_top4
as
a number;
begin
select * into a from (select col1 from tab1 order by col1) where 
rownum<=1;
end; 
create procedure sql_sp_select_top5
as
DECLARE @a number
select top 15 percent @a=col1     from tab1 order by 
col1 
create or replace procedure sql_sp_select_top5
as
a number;
begin
select * into a from (select col1 from tab1 order by col1) where 
rownum<=15*(select count(*) from tab1 order by col1)/100;
end; 