Migrating Oracle to SQL Server using SSMA - Error O2SS0038 Hierarchical query not converted
By Bill Ramos and Ankit Matta, Advaiya Inc.
This blog post covers one of the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot convert a hierarchical query containing complex functions in any sub query. A hierarchical query is a type of SQL query that handles hierarchical model data. For each hierarchical query one has to specify a starting condition with the START WITH clause, which will trickle down to all dependent rows using one or more conditions specified by the CONNECT BY clause.
Error O2SS0038 Hierarchical query not converted
Background
When converting the hierarchical query, SSMA provides emulation. SSMA emulates it to a corresponding solution as a recursive query. Note: A recursive CTE can greatly simplify the code required to run a recursive query within a statement. Oracle's START WITH clause becomes the first nested SELECT, the base case of the recursion, to be UNIONed with the recursive part which is just another SELECT.
Whenaggregatefunction (i.e. MAX, MIN, etc.) is used as a part of sub query in a hierarchical query, SSMA cannot convert the hierarchical query to corresponding SQL Server query and generates the O2SS0038 error message.
Possible Remedies
Consider the following example that has an aggregate function (i.e. MAX, MIN, etc.) used as a part of sub query in a hierarchical query:
CREATE OR REPLACE PROCEDURE Hierarchy_Test AS
BEGIN
FOR c IN (
SELECT p.id, p.NAME, p.parent_id, pp.podr_cod, pp.count_chel, pp.date_start, pp.date_finish, pp.parent_podr_id
FROM podr p,
(SELECT *
FROM podr_param pp
WHERE pp.ROWID = (SELECT SUBSTR(MAX(TO_CHAR(date_start,'YYYYMMDD')||rowid),9)
FROM podr_param
WHERE podr_id=pp.podr_id)) pp
WHERE p.id = pp.podr_id
START WITH p.id = 123
CONNECT BY PRIOR p.id = pp.parent_podr_id)
LOOP
dbms_output.put_line(RPAD(c.id||' ',15) || RPAD(c.podr_cod||' ',15) || RPAD(c.NAME||' ',15) || RPAD(TO_CHAR(c.date_start,'DD.MM.YYYY')||' ',15) || RPAD(TO_CHAR(c.date_finish,'DD.MM.YYYY'),15));
END LOOP;
END;
The solution is to simplify the nested sub query containing any aggregate Function. In our example, the hierarchical query containing aggregate function (MAX) in a sub query is not converted into the equivalent SQL query.
To resolve this error, we’ll create a new function in Oracle and call that function in the nested query. The function will calculate the desired value and pass it into the nested query. Below is the sample Function, for the above query:
CREATE OR REPLACE function max_val(date_start DATE)
RETURN VARCHAR
IS val VARCHAR(20);
BEGIN
SELECT ROWID from podr_param WHERE date_start=(SELECT MAX(date_start)
INTO val
FROM podr_param);
RETURN (val);
END;
You can then call this function in within the modified procedure in the nested query and the error will be resolved. The end result would translate the Oracle's START WITH clause into the first nested SELECT, and UNION it with the recursive part which is just another SELECT.
Below is the example of the simplified procedure along with the screenshot of the converted query:
CREATE OR REPLACE PROCEDURE Hierarchy_Test AS
BEGIN
FOR c IN (
SELECT p.id, p.NAME, p.parent_id, pp.podr_cod, pp.count_chel, pp.date_start, pp.date_finish, pp.parent_podr_id
FROM podr p,
(SELECT *
FROM podr_param pp
WHERE pp.ROWID = max_val(date_start)) pp
WHERE p.id = pp.podr_id
START WITH p.id = 123
CONNECT BY PRIOR p.id = pp.parent_podr_id) LOOP
dbms_output.put_line(RPAD(c.id||' ',15) || RPAD(c.podr_cod||' ',15) || RPAD(c.NAME||' ',15) || RPAD(TO_CHAR(c.date_start,'DD.MM.YYYY')||' ',15) || RPAD(TO_CHAR(c.date_finish,'DD.MM.YYYY'),15));
END LOOP;
END;
You can now use SSMA to convert the result to SQL Server without errors as shown below.
Related Errors
There are several other errors related to Hierarchical queries that you may encounter. These include the following:
- O2SS0047 Hierarchical queries with asterisk not supported
- O2SS0119 Hierarchical queries in sub query factoring clauses are not supported
- O2SS0268 Hierarchical query with outer join cannot be converted
- O2SS0285 Hierarchical query was not converted
In most of these situations, consider what the query is trying to accomplish and rewrite it to use the emulation.
References
For more information, check out the following references.