Migrating Oracle to SQL Server using SSMA – Error O2SS0099 ROWNUM in ORDER BY clause is not supported

  • 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 in ORDER BY clause. 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 O2SS0099 ROWNUM in ORDER BY clause is not supported

Background

When converting the ROWNUM in ORDER BY clause, SSMA uses the ROW_NUMBER() emulation technique in most cases without error. However, if the ORDER BY clause is used as part of a UNION query, SSMA generates error O2SS0099 that is described in the post.

Possible Remedies

Scenario: ROWNUM is included as part of a complex UNION clause

Consider the following example:

 Select city from customers union select city from employees order by rownum;

The solution is to simplify the expression by creating a sub query using parenthesis. Creating the sub query will separate the ROWNUM in ORDER BY clause with the UNION clause making in a simpler expression. Below is the example of the simplified query along with the screenshot of the converted query:

 select city from
     (select city from customers union select city from employees)
order by rownum;

clip_image002

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