By Bill Ramos and Mayank Bhanawat, Advaiya Inc.

This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert the PL/SQL block when outer joins are used in hierarchical queries.

In Oracle, CONNECT BY PRIOR is a condition that identifies the relationship between parent rows and child rows of the hierarchy. Outer Join includes data rows in the result set that do not have a match in the joined table and returns these missing columns as NULL values.

Error O2SS0268: Hierarchical query with outer join cannot be converted

Background

When an Outer Join statement is used with CONNECT BY PRIOR in hierarchical queries, SSMA doesn’t convert the statement and generate the following error ‘Error O2SS0268: Hierarchical query with outer join cannot be converted’

Possible Remedies

Consider the example below in which LEFT OUTER JOIN statement is used with CONNECT BY PRIOR statement in the hierarchical query.

Select e.empno, e1.empno, e1.mgr

From emp e LEFT OUTER JOIN emp1 e1

ON e.empno = e1.empno

Connect by prior e1.mgr = e.deptno;

 

When SSMA tries to convert the above code, it generates following error:Error O2SS0268”

 

Error_O2SS0268

 

To resolve the above issue, rewrite the SQL code by using recursive CTE method as shown below: 

With EMPTEMP (empno, ename, mgr) as

(

select empno, ename, MGR

  from   emp

  union all

  select e.empno, e.ename, e.MGR

  from   emp e

         join    

         emp1 e1

         on (e1.mgr = e.deptno)

)

 

SELECT e2.EMPNO, e1.EMPNO AS empno$2, e1.mgr

FROM

   EMPTEMP AS e2

      LEFT OUTER JOIN EMP1 AS e1

      ON e2.EMPNO = e1.EMPNO

GO

 

Related Errors

There are several other errors related to “Hierarchical query” that you may encounter. These include the following:

·         Error O2SS0160: SELECT statement not converted

·         Error O2SS0285: Hierarchical query was not converted

References

For more information, check out the following references:

·         Migrating Oracle to SQL Server 2008 White Paper