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


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

Related Errors

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

Comments (0)

Skip to main content