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
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’
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
select e.empno, e.ename, e.MGR
from emp e
on (e1.mgr = e.deptno)
SELECT e2.EMPNO, e1.EMPNO AS empno$2, e1.mgr
EMPTEMP AS e2
LEFT OUTER JOIN EMP1 AS e1
ON e2.EMPNO = e1.EMPNO
There are several other errors related to “Hierarchical query” that you may encounter. These include the following:
· Error O2SS0285: Hierarchical query was not converted
For more information, check out the following references: