For All Entries with two tables

[Former Member]

Hi All,

Can we use FOR ALL ENTRIES with two tables. for example

SELECT * FROM MKPF INTO TABLE T_MKPF

WHERE BUDAT IN S_BUDAT.

SELECT * FROM MARA INTO TABLE T_MARA

WHERE MTART IN S_MTART AND

MAKTL IN S_MAKTL.

SELECT * FROM MSEG INTO TABLE T_MSEG

FOR ALL ENTRIES IN "T_MKPF AND T_MARA"

WHERE MBLNR EQ T_MKPF-MBLNR AND

MATNR EQ T_MARA-MATNR.

can we do it like this or any other way to do this plz tell. I waitting for your responce.

Thanks

Jitendra

[Former Member]

Hi,

No we can't do the way you have used here. But you can just use a FAE based on MKPF records and then filter out the records based on MARA entries using a loop.

REWARDS IF HELPFUL!!

[Former Member]

Hi,

If u have code ready chk it with debugging...

[Former Member]

Hi ,

Check this thread you will have lot of idea's.

https://forums.sdn.sap.com/click.jspa?searchID=5402487&messageID=3777639

Thanks,

Reward If Helpful.

[Former Member]

try this: All Entries is possible only for single table.

DATA : T_MSEG1 LIKE STANDARD TABLE OF T_MSEG WITH HEADER LINE.

SELECT * FROM MKPF INTO TABLE T_MKPF

WHERE BUDAT IN S_BUDAT.

SELECT * FROM MARA INTO TABLE T_MARA

WHERE MTART IN S_MTART AND

MAKTL IN S_MAKTL.

IF NOT T_MKPF[] IS INITIAL.

SELECT * FROM MSEG INTO TABLE T_MSEG

FOR ALL ENTRIES IN T_MKPF WHERE MBLNR EQ T_MKPF-MBLNR.

ENDIF.

SORT T_MSEG BY MATNR.

LOOP AT T_MSEG.

READ TABLE T_MARA WHERE MATNR = T_MSEG-MATNR.

IF SY_SUBRC = 0.

MOVE-CORRESPONDING T_MSEG TO T_MSEG1.

APPEND T_MSEG1.

ENDIF.

ENDLOOP.

NOW IN T_MSEG1 YOU HAVE YOUR REQUIRED DATA.

GET BACK TO ME IN CASE OF ANY QUERY.

REWARD IF USEFUL.

aMIT sINGLA

DELETE

[former_member404244]

Hi,

u cannot do like this....chek some documentation on it..

1. duplicate rows are automatically removed

2. if the itab used in the clause is empty , all the rows in the source table will be selected .

3. performance degradation when using the clause on big tables.

Say for example you have the following abap code:

Select * from mara

For all entries in itab

Where matnr = itab-matnr.

If the actual source of the material list (represented here by itab) is actually another database table, like:

select matnr from mseg

into corresponding fields of table itab

where ….

Then you could have used one sql statement that joins both tables.

Select t1.*

From mara t1, mseg t2

Where t1.matnr = t2.matnr

And T2…..

So what are the drawbacks of using the "for all entires" instead of a join ?

At run time , in order to fulfill the "for all entries " request, the abap engine will generate several sql statements (for detailed information on this refer to note 48230). Regardless of which method the engine uses (union all, "or" or "in" predicates) If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource waste because it may lead to re-reading of data pages.

returing to the above example , lets say that our itab contains 500 records and that the abap engine will be forced to run the following sql statement 50 times with a list of 10 values each time.

Select * from mara

Where matnr in ( ...)

Db2 will be able to perform this sql statement cheaply all 50 times, using one of sap standard indexes that contain the matnr column. But in actuality, if you consider the wider picture (all 50 executions of the statement), you will see that some of the data pages, especially the root and middle-tire index pages have been re-read each execution.

Even though db2 has mechanisms like buffer pools and sequential detection to try to minimize the i/o cost of such cases, those mechanisms can only minimize the actual i/o operations , not the cpu cost of re-reading them once they are in memory. Had you coded the join, db2 would have known that you actually need 500 rows from mara, it would have been able to use other access methods, and potentially consume less getpages i/o and cpu.

In other words , when you use the "for all entries " clause instead of coding a join , you are depriving the database of important information needed to select the best access path for your application. Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will see a simple statement that uses an index , he is less likely to realize that this statement is executed in a loop unnecessarily.

Beore using the "for all entries" clause and to evaluate the use of database views as a means to:

a. simplify sql

b. simplify abap code

c. get around open sql limitations.

check the links

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

Regards,

Nagaraj

[Former Member]

hI

SELECT OTYPE

OBJID

RELAT

BEGDA

ENDDA

SCLAS

SOBID FROM HRP1001 INTO TABLE IT_HRP1001

WHERE OTYPE = 'D'

AND OBJID IN S_OBJID

AND BEGDA GE DATE-LOW

AND ENDDA LE DATE-HIGH

AND ( SCLAS = 'E' OR SCLAS = 'ET' ).

IF SY-SUBRC NE 0.

MESSAGE 'NO RECORD FOUND FOR THE GIVEN SELECTION CRITERIA ' TYPE 'E'.

ENDIF.

SELECT OTYPE

OBJID

AEDTM

UNAME

DELET

CANCR

• NCONT

FROM HRP1026

INTO TABLE IT_HRP1026

FOR ALL ENTRIES IN <b>IT_HRP1001</b>

WHERE OBJID = IT_SOBID-SOBID

AND ( OTYPE = 'E' OR OTYPE = 'ET' )

AND DELET = 'X' AND

BEGDA GE DATE-LOW AND

ENDDA LE DATE-HIGH.

IF SY-SUBRC EQ 0.

SELECT OBJID

STEXT

FROM HRP1000

INTO TABLE IT_HRP1000

FOR ALL ENTRIES IN<b> IT_HRP1001</b>

WHERE OBJID = IT_SOBID-SOBID AND

BEGDA GE DATE-LOW AND

ENDDA LE DATE-HIGH.

ENDIF.

YOU CAN WRITE LIKE THIS

[Former Member]

Hi,

No, you couldnt use the FOR ALL ENTRIES statement on reference of two tables, this statement should use only one table.

TIPS for all entries:

--Before using this statement put if condition for i/t is initial or not like

if not itabl[] is initilal

select........for all entries in itab..........

endif

Suppose if itab does not contain any records, but sys fetches all non corresponding records which mean in this it does not cosider the for all entires.

-- Use always key filed in where condition.

-- Use fields in sequence in where conditon.

I am here giving some sample code :

• Get Document header information

SELECT bukrs

belnr

gjahr

blart

bvorg

xblnr

bktxt

FROM bkpf

INTO TABLE tbl_bkpf

WHERE bukrs IN s_bukrs

AND belnr IN s_belnr

AND blart IN s_blart

AND gjahr IN r_gjahr

AND ( cpudt IN s_date

OR aedat IN s_date

OR upddt IN s_date ).

IF sy-subrc = 0.

SORT tbl_bkpf BY bukrs belnr gjahr.

ENDIF.

CLEAR tbl_bkpf_temp.

REFRESH tbl_bkpf_temp.

• cross company

IF NOT tbl_bkpf[] IS INITIAL.

SELECT bukrs gjahr belnr

FROM bvor

INTO TABLE tbl_bvor

FOR ALL ENTRIES IN tbl_bkpf

WHERE bvorg EQ tbl_bkpf-bvorg

AND bukrs IN s_bukrs

AND gjahr EQ tbl_bkpf-gjahr.

IF sy-subrc = 0.

SORT tbl_bvor BY bukrs gjahr belnr.

ENDIF.

IF NOT tbl_bkpf[] IS INITIAL.

SELECT * FROM bsik

INTO TABLE tbl_bsik

FOR ALL ENTRIES IN tbl_bkpf

WHERE bukrs EQ tbl_bkpf-bukrs

AND gjahr EQ tbl_bkpf-gjahr

AND belnr EQ tbl_bkpf-belnr

AND monat EQ tbl_bkpf-monat.

IF sy-subrc = 0.

SORT tbl_bsik BY bukrs lifnr gjahr belnr buzei.

ENDIF.

• When using "Select.. For all Entries". The following 4 rules MUST be followed:

o Check to make sure driver itab is not empty

o Always SORT the itab (driver table) by keys. Specify all keys used in the Where clause

o DELETE Adjacent Duplicates Comparing the keys that were sorted.

o All Primary Key Fields must be in the Select List

<b>Reward with points if helpful.</b>

Regards,

Vijay

[hymavathi_oruganti]

no

[Former Member]

Hi,

You cant. Alternate solution

declare a range and populate all material values into that range say s_matnr

Now

SELECT * FROM MSEG INTO TABLE T_MSEG

FOR ALL ENTRIES IN "T_MKPF

WHERE MBLNR EQ T_MKPF-MBLNR AND

MATNR IN s_matnr.

*reward if helpful*

[Former Member]

hi jitendra

u go f1 help.

ipls see below.

SELECT werks FROM t001w INTO TABLE i_t001w

FOR ALL ENTRIES IN i_upload_file

WHERE werks = i_upload_file-werks.

regards

kk.

[Former Member]

hi,

no that is not proper way. I am giving one example follow this.

tables: it_itab like lfa1 occurs 0 with header line,

it_jtab like ekko occurs 0 with header line .

select * from lfa1 into table it_jtab where kunnr = ?(varaiable name which u defined in parameters)

if sy-dbcnt <>0.

select * from lfa1 into table it_itab for all entries in it_jtab where it_jtab = lifnr.

if it is usefull do not forgot rewad point.

[Former Member]

answered

[so8vishalthakur]

Can anyone suggest if we have inline declared Internal Tables and that two ITs we need to use in for all entries so we can use it .

EX: @LT_VEKP and @LT_VEKP011

SELECT exidv, venum, status from vekp into table @DATA(LT_VEKP012) for all entries

in ___________________ where exidv ne @lt_vekp011-exidv.

Kindly use above given query to provide solution.

Thank You

[moshenaveh]

so8vishalthakur

Hello, While we're happy that you've come to SAP Community to get an answer to your question, you posted your question as an answer in an old thread.
I've converted your answer to a comment, but even so -- posting in older threads is not the best way to get guidance.
If you're looking for help, you should ask a new question: https://answers.sap.com/questions/ask.html.
Here are some tips to help you craft an effective question for our community: https://community.sap.com/resources/questions-and-answers, https://developers.sap.com/tutorials/community-qa.html, https://groups.community.sap.com/t5/welcome-corner-discussions/advice-from-sap-champions-questions-a....
I encourage you to follow this guidance, as I'd really like to see you get a solution to your problem.
I hope you find this advice useful!