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.
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.
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;
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.
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