O2SS0160: SELECT 语句未转换 (错误) O2SS0160: SELECT statement not converted (Error)

本文介绍了对于 Oracle SQL Server 迁移助手 (SSMA) 不会转换包含格式 SELECT 错误子句的两种情况下的语句的原因 ORDER BYThis 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

有些情况下,SSMA 不能转换复杂 SELECT 的语句。There are cases when SSMA cannot convert complex SELECT statements.

可能的补救措施Possible remedies

在这两种情况下,SSMA 不会转换 select 语句并生成错误消息。There are two scenarios, in which SSMA doesn't convert the select statement and generate an error message.

方案1: SELECT DISTINCT 语句与 CONNECT BY PRIOR 层次结构查询中的语句一起使用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;

当你尝试在 SSMA 中转换上面的代码时,它将生成以下错误消息:When you try to convert the above code in SSMA, it generates the following error message:

O2SS0160: SELECT 语句未转换O2SS0160: SELECT statement not converted

若要解决上述问题,可以 ORDER BY 在 Oracle 源代码中使用子句,如下所示: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;

SSMA 应成功转换上述查询。Above query should be converted successfully by SSMA.

方案2: ORDER BY 子句基于两个列/字段对结果集进行排序,但您在子句中仅指定了一个列/字段 SELECTScenario 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;

当你尝试在 SSMA 中转换上面的代码时,它将生成以下错误消息:When you try to convert the above code in SSMA, it generates the following error message:

O2SS0160: SELECT 语句未转换O2SS0160: SELECT statement not converted

若要解决此错误,请减少子句中字段/列的数目, ORDER BY 或在子句中添加其他字段/列 SELECTTo solve this error either reduce the number of field/column in ORDER BY clause or add additional fields/columns in SELECT clause.

下面是已更新的查询,从此处删除了第二列引用 ORDER BYHere is updated query where we removed second column reference from the ORDER BY:

SELECT Deptno
FROM emp
ORDER BY 1;

SSMA 应成功转换上述查询。Above query should be converted successfully by SSMA.

  • O2SS0268:无法转换具有外部联接的分层查询O2SS0268: Hierarchical query with outer join cannot be converted
  • O2SS0285:分层查询未转换O2SS0285: Hierarchical query was not converted