topic more dynamic select statements with same target itab in Application Development and Automation Discussions

more dynamic select statements with same target itab

Former Member — Thu, 19 Oct 2017 12:55:09 GMT

Hi everyone!

I want to read different tables for material data (MARA, MARC, MARD etc.) dynamically, because the selected fields, tables and the where condition are just known at runtime.

The result should be in one target for all different source tables.

non-dynamic it could be something like that:

SELECT matnr matkl
          FROM mara
          INTO (rt_material-matnr, rt_material-matkl)
          WHERE matkl EQ '123'.


SELECT werks lgort
          FROM mard
          INTO (rt_material-werks, rt_material-lgort)
          FOR ALL ENTRIES IN rt_material
          WHERE matnr EQ rt_material-matnr
            AND werks EQ '01'.

The dynamic select I have already. But I don't know how to make the target field list.

SELECT (<fs_fields>-sfields)
          FROM (<fs_fields>-tabname)
          INTO CORRESPONDING FIELDS OF TABLE rt_material
          WHERE (lv_where).

I have a loop at the different tables, I want to select from, where I call my dynamic select. But each dynamic select deletes the result from the earlier select. I hope you understand me.

Following is my whole code for this.

types:
    BEGIN OF ty_sel_fields,
        tabname TYPE tabname,
        sfields TYPE edpline_t,
      END OF ty_sel_fields .
  types:
    tt_sel_fields TYPE TABLE OF ty_sel_fields .

types:
    BEGIN OF ty_conditions,
      tabname TYPE tabname,
      value   TYPE string,
    END OF ty_conditions .
  types:
    tt_conditions TYPE TABLE OF ty_conditions WITH KEY tabname.


METHOD read_material_dynamic.

    DATA: lt_sel_fields TYPE tt_sel_fields,
          lt_where_cond TYPE tt_conditions,
          lv_where      TYPE string.

    FIELD-SYMBOLS: <fs_conditions> TYPE ty_conditions,
                   <fs_fields>     TYPE ty_sel_fields.

    " convert select-options
    CALL METHOD me->conv_dynamic_conditions
      EXPORTING
        it_conditions = it_conditions
      RECEIVING
        rt_where_cond = lt_where_cond.

    " zu selektierende Felder je Tabelle
    LOOP AT it_sel_fields ASSIGNING <fs_fields>.

      " Where-Bedingung zur Tabelle
      READ TABLE it_where_cond WITH TABLE KEY tabname = <fs_fields>-tabname ASSIGNING <fs_conditions>.

      lv_where = <fs_conditions>-value.

IF <fs_fields>-tabname EQ 'MARA'.
        " MARA is first table for select
        SELECT (<fs_fields>-sfields)
          FROM (<fs_fields>-tabname)
          INTO CORRESPONDING FIELDS OF TABLE rt_material
          WHERE (lv_where).

      ELSE.

        SELECT (<fs_fields>-sfields)
          FROM (<fs_fields>-tabname)
          INTO CORRESPONDING FIELDS OF TABLE rt_material
          FOR ALL ENTRIES IN rt_material
          WHERE matnr EQ rt_material-matnr
            AND (lv_where).

      ENDIF.

    ENDLOOP.

  ENDMETHOD.



  METHOD conv_dynamic_conditions.

    DATA: lt_where      TYPE rsds_twhere,
          ls_where_cond TYPE ty_conditions.

    FIELD-SYMBOLS: <fs_where>     TYPE rsds_where,
                   <fs_where_tab> TYPE rsdswhere.

    CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'
      EXPORTING
        field_ranges  = it_conditions
      IMPORTING
        where_clauses = lt_where.

    LOOP AT lt_where ASSIGNING <fs_where>.
      CLEAR ls_where_cond.
      ls_where_cond-tabname = <fs_where>-tablename.

      LOOP AT <fs_where>-where_tab ASSIGNING <fs_where_tab>.
        CONCATENATE ls_where_cond-value <fs_where_tab> INTO ls_where_cond-value SEPARATED BY space.
      ENDLOOP.

      APPEND ls_where_cond TO rt_where_cond.

    ENDLOOP.

  ENDMETHOD.

I hope you understand my problem and someone can help me.

Best Regards

Carina

Re: more dynamic select statements with same target itab

retired_member — Thu, 19 Oct 2017 17:50:04 GMT

Can you create your target with RTTC?

See example

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abendynamic_sql_abexa.htm

But if it is about "But each dynamic select deletes the result from the earlier select. I hope you understand me." : APPENDING instead of INTO?

Re: more dynamic select statements with same target itab

pokrakam — Fri, 20 Oct 2017 06:04:49 GMT

Interesting. As Horst said, appending will stop the overwrite, but I guess you also want the relevant fields next to each other in the same row(s). This becomes messy due to 1:n and n:n relationships.

The only two options I can see is either doing the SELECTs into individual tables and combining them afterwards, or a big dynamic join. With a join you could include all possible tables and add a dummy condition (matnr = '999999' or something like that) if the table is not relevant. Not sure how well this would work.

Personally I'd go for looping over the tables and doing individual SELECTs using dynamic @data(result) declarations, building your final structure out of the results using RTT*, then merging the lot.

Re: more dynamic select statements with same target itab

Former Member — Fri, 20 Oct 2017 06:30:19 GMT

I tried APPENDING already. And you're right: I want the relevant fields next to each other in the same row. So APPENDING is not the right solution.

I will try your idea with the SELECTs into individual tables and combining them afterwards. I think this is the best solution. As far as I know, a big JOIN can have a negative effect on the performance...

Thank you for your fast answer 🙂

Re: more dynamic select statements with same target itab

SimoneMilesi — Fri, 20 Oct 2017 07:06:06 GMT

i do not know in which release you are and if you can use Horst's suggestion (with a little work you can create the dynamic structure like for the example), but if not, you can always select the table keys (MATNR-WERKS-LGORT), use the APPEND, sort the internal table, LOOP it and use "AT NEW..." statement to build the final one.