topic Re: Create Dynamic Table for SQL String in Application Development and Automation Discussions

Create Dynamic Table for SQL String

ben09 — Mon, 09 Sep 2024 14:20:34 GMT

Hello everyone,

I was hoping you could help me in an issue I have been struggling with, how would you create a dynamic structure for an SQL string, I've been having alot of issues with returning my data to a dynamic internal table because I work on abap version 7.20 and dont have the functions/keywords DATA or VALUE, how would you create a dynamic datastructure for this insert to work?

As for why I am doing it, we want to store aset of default queries and do check ups on changes.

    DATA:  sql     TYPE REF TO cl_sql_statement,
           result  TYPE REF TO cl_sql_result_set,
           err     TYPE REF TO cx_sql_exception,
           cols    TYPE adbc_column_tab,
           dref    TYPE REF TO data.
    APPEND 'UNAME' TO cols.
    APPEND 'CHANGE_DAT' TO cols.
    APPEND 'AS4USER' TO cols.
    APPEND 'AS4TIME' TO cols.
    CREATE OBJECT sql.
    GET REFERENCE OF "DYNAMIC_TABLE" INTO dref.
    TRY.
        result = sql->execute_query(
         `SELECT AGR_USERS.UNAME, AGR_USERS.CHANGE_DAT, E070.AS4USER, E070.AS4TIME FROM AGR_ USERS JOIN  E070  ON AGR_USERS.UNAME = E070.AS4USER; ` ).
        result->set_param_table( itab_ref = dref
                                corresponding_fields = cols ).
        IF result->next_package( ) > 0.
          SORT result_tab BY carrid connid fldate.
          display( ).
        ENDIF.
      CATCH cx_sql_exception INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.

Re: Create Dynamic Table for SQL String

Sandra_Rossi — Mon, 09 Sep 2024 14:43:47 GMT

Do you have a syntax error? A runtime error? Functional bug? What are the lines for which you have an issue?

Do you want to create an internal table with a list of columns you only know at runtime? In that case, use RTTS.

Re: Create Dynamic Table for SQL String

ben09 — Mon, 09 Sep 2024 15:24:40 GMT

I didnt really have one, given i was at a bit of loss, on how to proceed, but it worked with the RTTS part, but I found a blog, that I will leave here if others are interested. Thank you the point in the right direction 🙂

If i could trouble with a more precise question, is there a way to do it in a more refined way than giving it the tables like the blog did e.g:
lo_struct ?= cl_abap_typedescr=>describe_by_name( 'AGR_USERS' ).
  lt_comp  = lo_struct->get_components( ).
  APPEND LINES OF lt_comp TO lt_tot_comp.
  lo_struct ?= cl_abap_typedescr=>describe_by_name( 'E070' ).
  lt_comp  = lo_struct->get_components( ).
  APPEND LINES OF lt_comp TO lt_tot_comp.

as that adds quite alot of unused fields, can one dynamicly give it fields from existing tables? Hopefully give it the fields from the select statement?

https://zevolving.com/2008/09/dynamic-internal-table-creation/

Re: Create Dynamic Table for SQL String

giancarla_aguilar — Tue, 10 Sep 2024 07:35:31 GMT

From your example code above, you can build lt_comp with the specific fields that you want. Here is a code snippet:

APPEND INITIAL LINE TO lt_comp ASSIGNING FIELD-SYMBOL(<fs_outcomp>).
CHECK sy-subrc = 0.
<fs_outcomp>-name = fieldname1.
* Field has data element reference
lv_fieldstr = data element.
<fs_outcomp>-type ?= cl_abap_elemdescr=>describe_by_name( lv_fieldstr ).

APPEND INITIAL LINE TO lt_comp ASSIGNING <fs_outcomp>.
CHECK sy-subrc = 0.
<fs_outcomp>-name = fieldname2.

* Field has table reference
lv_fieldstr = |{ tablename }-{ fieldname }|.
<fs_outcomp>-type ?= cl_abap_elemdescr=>describe_by_name( lv_fieldstr ).

* Create dynamic structure from components
      lo_structdescr ?= cl_abap_structdescr=>create( lt_comp ).
      lo_datadescr ?= lo_structdescr.
      lo_tabledescr ?= cl_abap_tabledescr=>create( lo_datadescr ).
* Create dynamic internal table
      CREATE DATA lo_dyntab TYPE HANDLE lo_tabledescr.
      ASSIGN lo_dyntab->* TO <fs_dyntab>.

I hope this answers your query (apologize for the format).

Re: Create Dynamic Table for SQL String

ben09 — Tue, 10 Sep 2024 08:07:12 GMT

thank you the assistance, but I am having some issues, as I am using abap 7.20

APPEND INITIAL LINE TO lt_comp ASSIGNING FIELD-SYMBOL(<fs_outcomp>).

wont work, nor will any use of VALUE or DATA. to fetch dynamic structures/types. So <fs_outcomp> would need to be delclared with some sort of structure I presume, as it otherwise would not be able to say <fs_outcomp>-name,

also I am uncertain about lv_fieldstr = data ELEMENT. Perhabs this is also a version issue, but what element would this refer to ?

Re: Create Dynamic Table for SQL String

Sandra_Rossi — Tue, 10 Sep 2024 08:16:45 GMT

ABAP 7.02 (7.20 is an obsolete name, or one for Kernel or SAP GUI):

FIELD-SYMBOLS <fs_outcomp> TYPE ANY. APPEND INITIAL LINE TO lt_comp ASSIGNING <fs_outcomp>.

Re: Create Dynamic Table for SQL String

ben09 — Tue, 10 Sep 2024 09:42:49 GMT

I should have included that I had tried that, my apologies, but as I stated that declaration wont have structure associated with it, so the approach still wouldnt work.

FIELD-SYMBOLS: <fs_outcomp> TYPE any.
  APPEND INITIAL LINE TO lt_comp ASSIGNING <fs_outcomp>.
  CHECK sy-subrc = 0.
  <fs_outcomp>-name = 'UNAME'

Unless I am misunderstanding something.

Re: Create Dynamic Table for SQL String

Sandra_Rossi — Tue, 10 Sep 2024 10:56:22 GMT

ASSIGN COMPONENT 'UNAME' OF STRUCTURE <fs_outcomp> TO <field>.

Re: Create Dynamic Table for SQL String

giancarla_aguilar — Tue, 10 Sep 2024 12:13:58 GMT

The code I have shared is if you can derive the fields/structure of the dynamic table during runtime. In the blog link that you have shared, the declaration of lt_comp is there. But for the code snippet I have shared, I used a structure from the ABAP type group - DATA lt_comp TYPE abap_component_tab therefore field symbol type is abap_component_tab as well, which has fields NAME and TYPE to store the fieldname and its corresponding data type of the dynamic table that will be built.

I hope this is clear enough.

Re: Create Dynamic Table for SQL String

giancarla_aguilar — Tue, 10 Sep 2024 12:19:34 GMT

If you can determine the field name or reference during runtime:

For data element, lv_fieldstr = 'MATNR'.

For table field reference, lv_fieldstr = 'MARA-MATNR'.