topic Re: Help reg select statement in Application Development and Automation Discussions

Help reg select statement

Former Member — Mon, 26 Oct 2009 11:15:32 GMT

Hi all,

My internal table itab in my function module contains 3 fields from 3 different tables. Fetching data for the first field is directly fm db table. For the other 2 fields i need to fetch values in such a way that

LOOP AT itab.

SELECT field2 FROM table INTO itab for all entries in itab WHERE fieldx EQ itab-fieldx.

APPEND itab.

ENDSELECT.

ENDLOOP.

When i use such statements, time taken is considerably long enough so i tried replacing with "FOR ALL ENTRIES".

But even then its taking long time to execute.

Moreover i'm not able to get the values of the other 2 fields in my output.

I'm using move statement to fill the final table in the function module.

Pls can anyone help me to solve this issue.

Re: Help reg select statement

Former Member — Mon, 26 Oct 2009 11:19:55 GMT

Hi,

Using FOR ALL ENTRIES is always encouraged compared to writing SELECT within LOOP. If using FOR ALL ENTRIES is also taking time, it depends on the table on which you are querying and if you have used all the primary keys in the where conditions of the select statement.

If all the three tables are linked, its better to use a INNER JOIN select query which will populate the final internal table directly

Vikranth

Re: Help reg select statement

Former Member — Mon, 26 Oct 2009 11:57:39 GMT

hi,

Thanks for your reply. I even tried inner join and even dat s also not populating my final table. Pls can someone guide me to get the values in my final table. I'm using statements like the following to fill my final table tablelist.

LOOP AT itab.

MOVE: itab-fieldx TO tablelist-fieldx.

MOVE: itab-fieldy TO tablelist-fieldy.

APPEND tablelist.

ENDLOOP.

Regards,

Revathi.

Re: Help reg select statement

Former Member — Mon, 26 Oct 2009 12:11:43 GMT

Hi,

You can do one thing: When you are using "For All Entries:", just check in dubugging mode whether the data is coming into your internal table for the second and 3rd table and also check in the 2nd and 3rd table, whether entries exists or not.

And also, if it is taking more time using "For All Entries", tries to sort your 1st internal table based on the field on which you are fetching the data from 2nd and 3rd table.

Hopes, it will solve ur problem, otherwise tell me exactly wht r u doing, might be you require little bit modification

Thanks and Regards

Sachin

Re: Help reg select statement

Former Member — Mon, 26 Oct 2009 12:22:09 GMT

Hi Revathi,

Do the following steps:

a) Select fileld1 field2 from table tablename into table ITAb where field1 in Select-option or parameter

b) sort itab by field1, field2 ( primary keys of the table)

c) if not itab[] is initial .

select field1 field2 from table tablename into table itab1 for all entries in itab where fields eq itab-field1.

select field1 field2 from table tablename into table itab2 for all entries in itab where fields eq itab-field1.

endif.

d) sort itab1 by field1 field2

e) sort itab2 by field1 field2

f) process the data into final table

loop at itab.

move-corresponding itab to it_final(it_final is final intenral table)

read table itab1 with key fieild1 = itab-field1

if sy-subrc eq 0.

move-corresponding itab1 to it_final

endif.

read table itab2 with key fieild1 = itab-field1

if sy-subrc eq 0.

move-corresponding itab2 to it_final

endif.

append it_final.

endloop.

This will take all your values to final table and you can display the same.

IHope it will work for you. Any doubts, please feel free to ask.

If your problem get resolved, please let me know.

Thanks

Sachin

Re: Help reg select statement

Former Member — Fri, 30 Oct 2009 10:24:08 GMT

Hi Sachin,

Thanks for your reply. I'm writing a program with the following code.

LOOP AT itab3.

MOVE-CORRESPONDING itab3 TO itab.

READ TABLE itab6 WITH KEY field1 = itab3-field1.

IF sy-subrc EQ 0.

MOVE-CORRESPONDING itab6 TO itab.

ENDIF.

APPEND itab.

ENDLOOP.

LOOP AT itab.

READ TABLE itab2 WITH KEY field2 = itab-field2.

IF sy-subrc EQ 0.

MOVE-CORRESPONDING itab2 TO itab.

ENDIF.

READ TABLE itab1 WITH KEY field4 = itab-field3.

IF sy-subrc EQ 0.

MOVE-CORRESPONDING itab1 TO itab.

ENDIF.

APPEND itab.

ENDLOOP.

I dint use sort statement. I'm just using select and loop statements. Itab is not getting populated with the second loop in the code.

So i could not populate my final table final_itab fm dis itab.

Pls can anyone help me to get the required output or provide some useful suggestions.

Thanks and Regards,

Revathi.

Re: Help reg select statement

Former Member — Fri, 30 Oct 2009 10:30:49 GMT

Hello


LOOP AT itab.
READ TABLE itab2 WITH KEY field2 = itab-field2.
IF sy-subrc EQ 0.
MOVE-CORRESPONDING itab2 TO it_final. " it_final instead of itab
APPEND it_final. " add this
ENDIF.

READ TABLE itab1 WITH KEY field4 = itab-field3.
IF sy-subrc EQ 0.
MOVE-CORRESPONDING itab1 TO it_final. " it_final instead of itab
APPEND it_final. " add this
ENDIF.

*APPEND itab. " remove this
ENDLOOP.