Select For All Entries

[Former Member]

Hi All,

I want to know about <b>"Select For All Entries"</b> query...

Plz. give a suitable example of it.....and if possible snapshot also...

Thanks & Regards,

<b>Anil Kumar</b>

[Former Member]

Hi All,

I want to know about <b>"Select For All Entries"</b> query...

Plz. give a suitable example of it.....and if possible snapshot also...

Thanks & Regards,

<b>Anil Kumar</b>

[Former Member]

Hi

i had created one program

in that i had used for all entries see this

<b> SELECT OTYPE <u>" this to retrive data from HRP1001</u>

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. <u>" this to print if there is no data based on the selection screen</u> MESSAGE 'NO RECORD FOUND FOR THE GIVEN SELECTION CRITERIA ' TYPE 'E'.

ENDIF.

SELECT OTYPE <u> " this to retrive data from HRP1026 by useing above internal table</u> OBJID

AEDTM

UNAME

DELET

CANCR

• NCONT

FROM HRP1026

INTO TABLE IT_HRP1026

FOR ALL ENTRIES IN IT_SOBID

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 IT_SOBID

WHERE OBJID = IT_SOBID-SOBID AND

BEGDA GE DATE-LOW AND

ENDDA LE DATE-HIGH.</b>

reward if usefull

[Former Member]

Hi,

select for all entries means

if you have some records in a table and if you wnat to select related data

from another dbtable then you haveto go for the select for all entries

chek this code

data: itab like ekko occurs 0 with header line.

data: itab like ekpo occurs 0 with header line.

select * from ekko into table itab up to 10 rows.

if sy-subrc eq 0.

select * from ekpo into table itab1 for all entries in itab where

ebeln = itab-ebeln.

end if.

loop at itab.

write:/ itab-ebeln

loop at itab1 where ebeln = itab-ebeln.

write:/ itab1-ebeln, itab1- ebelp.

endloop.

endloop.

thanks & regards,

Venkatesh

[Former Member]

Hi Anil,

For al entries addition in select statement is used to select corresponding values for all the entries present in the internal table we are passing.

Select aufnr from aufk into it_aufk where auart eq 'PP04'.

so for all the entries selected in it_aufk i want select basic start date.

select gstrp into it_afko from afko for all entries in it_aufk where aufnr = it_aufnr.

Reward if helpful.

Regards,

Ravi G

[Former Member]

Hi

just try this code

select objnr pspnr posid stufe fakkz zbillt psphi zend ztermcd "pspnr is unique

from prps into table t_prps "place row into header of t_prps

for all entries in t_proj "all billable projects in internal table

where psphi eq t_proj-pspnr "prps project = project rsn

and zbillt eq p_billt. "billing type input param (01)

thanks

vivekanand

[Former Member]

Hi Anil,

Apart from all solution above, always check whether the table is initial or not, otherwise it will take all entries & result into wrong output.

I mean if you are goin to query on some table VBAP for all entries in i_itab, write like this:

if NOT i_itab[] is initial.

select *

from vbap

for all entries in i_itab

where vbeln eq i_itab-vbeln.

endif.

Reward helpful answers.

Regards,

Siddhesh Sanghvi.

[Former Member]

*Internal table for mara

DATA : BEGIN OF itab_mara OCCURS 0,

matnr LIKE mara-matnr,

mtart LIKE mara-mtart,

meins LIKE mara-meins,

matkl LIKE mara-matkl,

ernam LIKE mara-ernam,

END OF itab_mara.

*Internal table for makt

DATA : BEGIN OF itab_makt OCCURS 0,

matnr LIKE makt-maktx,

maktx LIKE makt-maktx,

END OF itab_makt.

*Internal table for t023t

DATA : BEGIN OF itab_t023t OCCURS 0,

matkl LIKE t023t-matkl,

wgbez LIKE t023t-wgbez,

END OF itab_t023t.

*Internal table for marc

DATA : BEGIN OF itab_marc OCCURS 0,

matnr LIKE marc-matnr,

werks LIKE marc-werks,

END OF itab_marc.

&----

---

*& Form GET_DATA

&----

---

FORM get_data.

SELECT matnr

werks

FROM marc INTO TABLE itab_marc WHERE werks IN s_werks AND

matnr IN s_matnr.

IF NOT itab_marc[] IS INITIAL.

SELECT matnr

mtart

meins

matkl

ernam

FROM mara INTO TABLE itab_mara

FOR ALL ENTRIES IN itab_marc

WHERE matnr = itab_marc-matnr

AND mtart IN s_mtart

AND meins IN s_meins.

ENDIF.

IF NOT itab_mara[] IS INITIAL.

SELECT matnr

maktx

FROM makt INTO TABLE itab_makt

FOR ALL ENTRIES IN itab_mara

WHERE matnr = itab_mara-matnr AND

spras = sy-langu.

ENDIF.

IF NOT itab_mara[] IS INITIAL.

SELECT matkl wgbez

FROM t023t

INTO TABLE itab_t023t

FOR ALL ENTRIES IN itab_mara

WHERE matkl = itab_mara-matkl AND

spras = sy-langu.

ENDIF.

ENDFORM. " GET_DATA

&----

---

*& Form fill_data

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM fill_data.

SORT : itab_mara BY matnr,

itab_marc BY matnr,

itab_makt BY matnr,

itab_t023t BY matkl.

• loop at itab_mara.

LOOP AT itab_t023t.

CLEAR : f_itab.

READ TABLE itab_mara WITH KEY matkl = itab_t023t-matkl BINARY SEARCH.

IF sy-subrc = 0.

f_itab-wgbez = itab_t023t-wgbez.

f_itab-mtart = itab_mara-mtart.

f_itab-matnr = itab_mara-matnr .

f_itab-ernam = itab_mara-ernam.

f_itab-matkl = itab_mara-matkl.

ENDIF.

READ TABLE itab_makt WITH KEY matnr = itab_mara-matnr BINARY SEARCH.

IF sy-subrc = 0.

f_itab-maktx = itab_makt-maktx.

ENDIF.

READ TABLE itab_marc WITH KEY matnr = itab_makt-matnr BINARY SEARCH.

IF sy-subrc = 0.

f_itab-werks = itab_marc-werks.

ENDIF.

[Former Member]

Hello Anil,

FOR ALL ENTRIES... is used to get the records from data base for the obnly entries present in the internal table only... not all the records which are satisfing the where conditions.

Reward If Helpful

-

---

Sasi.

[Former Member]

Hi..,

suppose u have some values .. more than one for a filed f1 in an internal table..

and if u want to get all the records from a database table in which a field (same as f1) is having the equal values with f1.. then we will go for SELECT ... FOR ALL ENTRIES..

for example..

u have an internal table in which a field f1 has values 1, 4, 2.

Now u need to get all the records from a database table in which the field ( same as f1 ) has the value equal to field f1 in internal table i.e f1 eq 1 , 2, 4.

in this case we use FOR ALL ENTRIES in itab..

when u use for all entries u need to take care of two things...

1) in where clause there should be a condition on atleast one field of the for all entries table itab.

like

select *

from spfli

into t_spfli

for all entries in itab

where carrid eq itab-carrid.

2)the itab should not be empty.

if itab is empty then all the data from spfli table will be placed in the table t_spfli, by ignoring the where clause..

Thanks & Regards,

Sunil

[Former Member]

hi anil

select for all entries is nothing but selecting all entries from table and placing it into internal table which matches the records in previous table which has been selected....

for eg.

SELECT field1 field2..... fieldn

FROM tablename

INTO CORRESPONDING FIELDS OF TABLE itab_table_name

FOR ALL ENTRIES IN prev table

WHERE field1 = itab_table_name-field1 AND

field2 = itab_table_name-field2 .

REWARD IF USEFUL...!

[Former Member]

Hi,

For all entries

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

The Minus

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

For more information click the following link:

http://sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

Regards

Bhaskar

[Former Member]

hi,

tables : mara, marc.

data : begin of itab_mara occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

end of itab_mara.

data : begin of itab_marc occurs 0,

matnr like marc-matnr,

werks like marc-werks,

end of itab_marc.

while writing select statements.

select matnr ernam from mara

into corresponding fields of itab_final

where matnr = marc-matnr.

if not itab_mara[] is initial.

select matnr werks from marc into corresponding fields of itab_final

for all entries in itab_mara

where matnr = itab_mara-matnr.

endif.

Reward with points if helpful.

Message was edited by:

Vinutha YV