How to get all child nodes of a hierarchical structure in ABAP?

Question

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

• ID
• predecessor_id
• name

Starting with this identifier, I should be able to retrieve all the child nodes (and not just the direct children). Since general table expressions (WITH RECURSIVE) are not available in ABAP, what would be the best way to solve this problem?

A possible solution that I thought of is to iterate through a result set (LOOP or with a cursor) and recursively call a function that retrieves 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 into the SQL dialect of the base database. If the underlying database does not support WITH or WITH RECURSIVE , and from what I see from the next article , not every database does this, then adding it to OpenSQL makes no sense, since in many cases there would be nothing to display his.

So, the first solution will be what you suggested by writing a separate recursive function / method / subroutine or if you really want to use the basic database functions, you can use the ADBC interface. If you are familiar with JDBC , then the concept should not be new to you. If you are doing this for productive purposes, however, you must make sure that in the future there is a possibility or inability to migrate the database.

A solution with ADBC that works for me on an SAP system with an Oracle base 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( ).