Migrating Oracle to SQL Server using SSMA - Error O2SS0268 Hierarchical query with outer join cannot be converted
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”
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: