Migrating Oracle to SQL Server using SSMA - Error O2SS0021 Statement with ROWNUM not converted

  • Article

By Bill Ramos and Ankit Matta, Advaiya Inc.

This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot convert a statement that contains the Oracle ROWNUM pseudocolumn. The Oracle ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1; the second row has a ROWNUM of 2, and so on.

Error O2SS0021 Statement with ROWNUM not converted

Background

When converting the ROWNUM pseudocolumn, SSMA provides two forms of emulation:

  • With the TOP keyword of the SELECT statement if this pseudocolumn is used only to limit the size of the result set.
  • With the ROW_NUMBER() function if the row numbers appear in a SELECT list.

There are two cases where SSMA decides that it cannot convert the ROWNUM pseudocolumn to SQL Server and hence, generates the O2SS0021 error message.

  • Scenario 1: ROWNUM is included as part of a complex WHERE clause
  • Scenario 2: ROWNUM is used in a WHERE clause like this: WHERE ROWNUM > a positive integer.

Possible Remedies

Scenario 1: ROWNUM is included as part of a complex WHERE clause

Consider the following example:

 DELETE FROM employees WHERE ROWNUM-1<=11+1 AND employeeid>10;

The solution is to simplify the expression for the ROWNUM to be ROWNUM <=11. The end result would translate to use the TOP clause. Below is the example of the simplified query along with the screenshot of the converted query:

 DELETE FROM employees WHERE ROWNUM<=11 AND employeeid>10;

clip_image002

In the next example, SSMA doesn’t know how to parse ROWNUM with the IN clause.

 DELETE FROM employees WHERE ROWNUM IN (1,2,3,4);

Rather than attempting to translate this literally, you should consider using a WHERE clause that uses the primary key or a unique identifier, especially when there is no ORDER BY clause. For example, use the primary key column employeeid instead of ROWNUM as follows:

 DELETE FROM employees WHERE employeeid IN (1,2,3,4);

Scenario 2. ROWNUM is used in a WHERE clause like this: WHERE ROWNUM > a positive integer

Consider the following example:

 SELECT employeeid FROM employees WHERE ROWNUM > 2 OR employeeid > 8;

Whenever you are using ROWNUM > Positive integer, the expression is always evaluated to false. SSMA will report the problem using error O2SS0021. Simply remove the ROWNUM > 2 expression to correct the problem.

There are several other errors related to ROWNUM that you may encounter. These include the following:

In most if these situations, consider what the query is trying to accomplish and rewrite it to use the ROW_NUMBER() emulation, TOP clause, or use a unique expression for your WHERE, GROUP BY, or ORDER BY clauses.

References

For more information, check out the following references.

Migrating Oracle to SQL Server 2008 White Paper

ROW_NUMBER() MSDN Help Topic

KB Article 186133 - How to dynamically number rows in a SELECT Transact-SQL statement

ROWNUM Pseudocolumn – Oracle Database SQL Language Reference