topic Re: Dynamic Query in Application Development and Automation Discussions

Dynamic Query

Former Member — Tue, 30 Sep 2014 16:50:35 GMT

Hello, I have a query about internal tables. I have an internal tables as follows.

ZROW CHARAC CHARVAL
1           MATERIAL 100
1           CALYEAR 2013
1           CALMONTH 12
1           AMOUNT 200
2           MATERIAL 200
2           CALYEAR 2013
2           CALMONTH 10
2           AMOUNT 200

I have another internal table which is like this:
Material CalYear CalMonth Amount

What I want to do is use Row number as my key and insert material, calyear, calmonth and amount data in material, calyear, calmonth, amount columns in the second internal table which is possible but the problem i have is to make my ZROW field dynamic. There is a possibility that I may have 1000 rows or 2000 or 2500 rows so i can't do some thing like this.

LOOP AT IT1 INTO WA1.
IF WA1-ZROW = '0000'.

MOVE: WA1-MATERIAL TO WA2-MATERIAL,
WA1-CALYEAR TO WA2-CALYEAR.

I am trying to see if there is a way i can make my zrow dynamic and keep counting up. once it reaches the end of internal table then it should stop.

Please let me know.

Re: Dynamic Query

naveen_inuganti2 — Tue, 30 Sep 2014 17:43:50 GMT

Have you tried something like this?

data:lv_row type wa1-zrow.

do.

lv_row = lv_row + 1.

LOOP AT IT1 INTO WA1 where zrow = lv_row.

MOVE: WA1-MATERIAL TO WA2-MATERIAL,

WA1-CALYEAR TO WA2-CALYEAR.

ENDLOOP.

if sy-subrc <> 0.

exit.

endif.

enddo.

Regards,

Naveen

Re: Dynamic Query

Former Member — Tue, 30 Sep 2014 18:37:48 GMT

As per my understanding you have 3 columns in first table: row no, field name, field value, and in second table 4 fields: material, year, month, amount. Now you want to transfer value from 1st table to second table. For this requirement you can refer below code:

DATA:

BEGIN OF wa_tab1,

row TYPE i,

field TYPE c LENGTH 30,

value TYPE c LENGTH 30,

END OF wa_tab1,

BEGIN OF wa_tab2,

matnr TYPE matnr,

year TYPE n LENGTH 4,

month TYPE n LENGTH 2,

amount TYPE p LENGTH 16 DECIMALS 2,

END OF wa_tab2,

lt_tab1 LIKE TABLE OF wa_tab1,

lt_tab2 LIKE TABLE OF wa_tab2.

" Transfer Value From tab1 to tab2 based on row

SORT lt_tab1.

LOOP AT lt_tab1 INTO wa_tab1.

CASE wa_tab1-field.

WHEN 'MATERIAL'.

wa_tab2-matnr = wa_tab1-value.

WHEN 'CALYEAR'.

wa_tab2-year = wa_tab1-value.

WHEN 'CALMONTH'.

wa_tab2-month = wa_tab1-value.

WHEN 'AMOUNT'.

wa_tab2-amount = wa_tab1-value.

ENDCASE.

AT END OF row.

APPEND wa_tab2 TO lt_tab2.

CLEAR wa_tab2.

ENDAT.

ENDLOOP.

Re: Dynamic Query

Former Member — Wed, 01 Oct 2014 02:07:12 GMT

I tried this code and it worked but it is taking a very long time to execute.Is there a better way to bring data in rows from these columns?

they are all under one column so I have to do like this

IF first internal table's work area-charac = 'MATERIAL'.

MOVE: FIRST INTERNAL TABLE's work area-VAL TO SECOND Internal table's work area-Material.

ENDIF.

and so on.

I have 6 fields like this which means each record will have to loop through 6 times. I am bringing about 100,000 records. so you see it will never finish.

Please let me know if there is a better way of doing this.

Thanks.