How do I retrieve all child nodes of a hierarchical structure in ABAP?

Question

Suppose I have a database table representing a hierachical structure, with the following columns:

• id
• predecessor_id
• name

Starting from a given ID, I have to be able to retrieve all child nodes (not only the direct children). Since Common Table Expressions (WITH RECURSIVE) are not available in ABAP, what would be the best way to solve this?

A possible solution I have thought of is iterating through a result set (LOOP or by using a cursor), and recursively call a function that retrieves the direct child nodes. However, I hope there is a more elegant approach.

Answer 1

First of all you need to know that SAP is not a database and OpenSQL is always translated to the SQL dialect of the underlying database. If the underlying database does not support WITH or WITH RECURSIVE and from what I see from the following article not each and every database does so, then adding it to OpenSQL would not make any sense as in many cases there would not be anything to map it to.

So the first solution would be as you proposed, writing a separate recursive function/method/subroutine or if you really want to use underlying database functionality you can use ADBC interface. If you are familiar with JDBC then the concept shouldn't be new to you. If you are doing that for productive purposes however you should make sure that there is a litte or no probability of a database migration in the future.

The solution with ADBC that works for me on an SAP system with an underlying Oracle database.

REPORT Z_ADBC_TEST.

CLASS lcl_test DEFINITION.
    PUBLIC SECTION.
        CLASS-METHODS:
            main.
ENDCLASS.

CLASS lcl_test IMPLEMENTATION.
    METHOD main.
        DATA lo_sql_connection TYPE REF TO cl_sql_connection.
        DATA lo_sql_statement  TYPE REF TO cl_sql_statement.
        DATA lo_sql_result_set TYPE REF TO cl_sql_result_set.
        TYPES BEGIN OF lt_result_struct,
            n    TYPE i,
            fact TYPE i,
        END OF lt_result_struct.
        DATA lt_result TYPE TABLE OF t_result_struct WITH DEFAULT KEY.
        DATA lr_ref_to_data TYPE REF TO data.
        FIELD-SYMBOLS <fs_result> LIKE LINE OF lt_result.

        lo_sql_connection = cl_sql_connection=>get_connection( ).
        lo_sql_statement = lo_sql_connection->create_statement( ).
        GET REFERENCE OF lt_result INTO lr_ref_to_data.
        lo_sql_result_set = lo_sql_statement->execute_query(
            `WITH temp(n, fact) ` &&
            `AS (SELECT 0,1 FROM dual UNION ALL ` &&
            `SELECT n+1,(n+1)*fact FROM temp ` &&
            `WHERE n < 9) ` &&
            `SELECT * FROM temp`
        ).
        lo_sql_result_set->set_param_table( lr_ref_to_data ).
        WHILE lo_sql_result_set->next_package( ) > 0.
            LOOP AT lt_result ASSIGNING <fs_result>.
                WRITE: / <fs_result>-n, <fs_result>-fact.
            ENDLOOP.
        ENDWHILE.
    ENDMETHOD.
ENDCLASS.

END-OF-SELECTION.
    lcl_test=>main( ).