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.

Odious

I see no other way to find out all the descendants than by recursively selecting children - precisely what you thought of. Where this was a performance problem, in some cases we have introduced a column master_id, containing the top level id of the element, and a secondary index on the database for it, – Unicorn

I don't think this is too broad, especially given that @rplantiko's comment is nearly a complete answer. – Osseous

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( ).

Bahadur

I did not state that "SAP is a database". Also, I already have the solution using the recursive approach implemented. The question was if this could be solved in a different way. – Odious

@TudorCiotlos Out of sheer curiosity... What RDBMS is your SAP system built on? – Iconium

@Iconium The RDBMS on the SAP system I am currently working is Oracle 11.2. – Odious

@TudorCiotlos Then the solution using ADBC could be helpful if you run for example into trouble with performance if the hierarchy becomes more complex and gets more levels. If you know that it will remain small, then the solution with an own recursive function is surely better and more robust. – Iconium