Edit

O2SS0160: SELECT statement not converted (Error)

  • Article

This article describes why SQL Server Migration Assistant (SSMA) for Oracle does not convert the SELECT statement in two scenarios that have ill formed ORDER BY clauses.

Background

There are cases when SSMA cannot convert complex SELECT statements.

Possible remedies

There are two scenarios, in which SSMA doesn't convert the select statement and generate an error message.

Scenario 1: SELECT DISTINCT statement is used with CONNECT BY PRIOR statement in the hierarchical query

Consider the example below:

SELECT DISTINCT
    empno,
    MGR,
    ename
FROM
    emp
START WITH MGR = 7902
CONNECT BY PRIOR MGR = empno;

When you try to convert the above code in SSMA, it generates the following error message:

O2SS0160: SELECT statement not converted

To resolve the above issue, you can use the ORDER BY clause in the source code of Oracle, as shown below:

SELECT DISTINCT
    empno,
    MGR,
    ename
FROM
    emp
START WITH MGR = 7902
CONNECT BY PRIOR MGR = empno
ORDER BY empno;

Above query should be converted successfully by SSMA.

Scenario 2: ORDER BY clause sorts the result set based on the two columns/fields but you have specified only one column/field in SELECT clause

Consider the example below:

SELECT Empno
FROM emp
ORDER BY 1, 2;

When you try to convert the above code in SSMA, it generates the following error message:

O2SS0160: SELECT statement not converted

To solve this error either reduce the number of field/column in ORDER BY clause or add additional fields/columns in SELECT clause.

Here is updated query where we removed second column reference from the ORDER BY:

SELECT Deptno
FROM emp
ORDER BY 1;

Above query should be converted successfully by SSMA.

  • O2SS0268: Hierarchical query with outer join cannot be converted
  • O2SS0285: Hierarchical query was not converted