10-19-2017 1:55 PM
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
10-19-2017 6:50 PM
Can you create your target with RTTC?
See example
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?
10-20-2017 7:04 AM
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.
10-20-2017 7:30 AM
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 🙂
10-20-2017 8:06 AM
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.