Edit

Share via

O2SS0038: Hierarchical query not converted (Error)

This article covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle can't convert a hierarchical query containing complex functions in any sub query.

Background

A hierarchical query is a type of SQL query that handles hierarchical model data. For each hierarchical query, you have to specify a starting condition with the START WITH clause, which trickles down to all dependent rows using one or more conditions specified by the CONNECT BY clause.

When SSMA converts the hierarchical query, it 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 UNION-ed with the recursive part which is just another SELECT.

When aggregate function (that is, MAX, MIN, etc.) is used as a part of sub query in a hierarchical query, SSMA can't convert the hierarchical query to corresponding SQL Server query and generates a conversion error.

Example

In the following example, you use an aggregate function (that is, MAX, MIN, etc.) as part of a subquery 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;

When you try to convert the previous code in SSMA, it generates the following error message:

O2SS0038: Hierarchical query not converted

Possible remedies

The solution is to simplify the nested sub query containing any aggregate function.

To resolve this error, create a new function in Oracle and call that function in the nested query. The function calculates the desired value and passes it into the nested query.

Here's the sample function for the previous 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 within the modified procedure in the nested query, and the error is 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.

Here's an example of the simplified procedure:

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.

In most of these situations, consider what the query is trying to accomplish and rewrite it to use the emulation.