Select For all Enteries

[Former Member]

Hi ABAP Gurus,

can u explain what is the use of select for all entries in an internal table? points will awarded ...

[venkata_ramisetti]

HI Naveen,

SELECT-OPTION: S_BUDAT FOR MKPF-BUDAT,

S_WERKS FOR MSEG-WERKS.

DATA: BEGIN OF ITAB1 OCCURS 0,

MBLNR LIKE MSEG-MBLNR,

MJAHR LIKE MSEG-MJAHR,

END OF ITAB1.

DATA:BEGIN OF ITAB2 OCCURS 0,

MBLNR LIKE MSEG-MBLNR,

MJAHR LIKE MSEG-MJAHR,

MATNR LIKE MSEG-MATNR,

WERKS LIKE MSEG-WERKS,

END OF ITAB2.

SELECT MBLNR MJAHR INTO TABLE ITAB1

FROM MKPF

WHERE BUDAT IN S_BUDAT.

IF NOT ITAB1[] IS INITIAL.

SELECT MBLNR MJAHR MATNR WERKS INTO TABLE ITAB2

FROM ITAB2

FOR ALL ENTRIES IN ITAB1

WHERE MBLNR = ITAB1-MBLNR AND

MJAHR = ITAB1-MJAHR AND

WERKS IN S_WERKS.

ENDIF.

You must check the table ITAB1 has any entries or not.

Thanks,

Ramakrishna

[Former Member]

Method 1 has the same efficiency with method 2

itab-A = 1,2,3,4,6,9,8,10

itab-B = 1,2,3,5,2,3,7,11

Method 1.

SELECT * INTO TABLE tab

FROM database

FOR ALL ENTRIES IN itab

WHERE A = itab-A

AND B = itab-B.

Method 2.

SELECT * INTO TABLE tab

FROM database

WHERE ( A = 1 AND B = 1 )

OR(A = 2 AND B = 2 )

OR(A = 3 AND B = 3 )

OR(A = 4 AND B = 5 ).

SELECT * INTO TABLE tab

FROM database

WHERE (A = 6 AND B = 2 )

OR(A = 9 AND B = 3 )

OR(A = 8 AND B = 7 )

OR(A = 10 AND B = 11 ).

[Former Member]

HI

The select command is the most fundamental function of writing ABAP programs allowing the retrieval of

data from SAP database tables.

In the <u>Select FOR ALL ENTRIES</u> command, the FOR ALL ENTRIES comand only retrieves data which matches entries within a particular internal table.

Please refer the code given under the link

<a href="http://72.14.203.104/search?q=cache:IEKPWjM470sJ:www.sapdevelopment.co.uk/tips/tips_select.htmSelectForallEntriesINABAP&hl=en&gl=in&ct=clnk&cd=1">Sample Code</a>

Refer the following links for further documentation.

<a href="http://72.14.203.104/search?q=cache:_MECgqfMe_oJ:help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htmSelectForallEntriesINABAP&hl=en&gl=in&ct=clnk&cd=10">Select</a>

<a href="http://72.14.203.104/search?q=cache:Yb_BBeSUmhUJ:www.howforge.com/select-for-all-entries-in-abap-4SelectForallEntriesINABAP&hl=en&gl=in&ct=clnk&cd=9">Select ... For All Entries in ABAP/4</a>

if these are helpful please reward points

REGARDS

ANOOP

[claus_wallacher]

Hi Naveen,

you got the information already in the posts above. Just one addition when you use this statement:

Make sure that your internal table that you check against is not empty, otherwise you get all entries, i.e. in

SELECT *
FROM <db-table>
INTO TABLE <itab>
FOR ALL ENTRIES IN <internal table>

you should check first if <internal table> is not empty. This is usually a favorite cause of error, happening also to the most experienced ABAPers.

Regards,

Claus

[Former Member]

hi Naveen,

some more description for the same...

<b>Performance Tuning</b>

<b>For all entries</b>

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.

<b>The plus</b>

<b>Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast</b> 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 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:

<b> FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.</b>

Regards,

Santosh P

[Former Member]

hi naveen ,

SELECT empfb xblnr bldat dmbtr sknto qbshh zlsch laufd

laufi zbukr vblnr lifnr sknto belnr kunnr blart

FROM regup INTO CORRESPONDING FIELDS OF TABLE t_regup

WHERE laufd EQ p_laufd

AND laufi EQ p_laufi

AND bukrs EQ p_absbu

AND xvorl <> 'X'.

Sort t_regup (by field)

Delete adjacent duplicates from t_regup comparing (field)

*Now ir t_regup has content .

2.

If not t_regup is initial.

SELECT LAUFD LAUFI ZBUKR HBKID WAERS ZALDT

HKTID LIFNR VBLNR CHECT ZLAND RZAWE

UZAWE voidr

FROM payr INTO CORRESPONDING FIELDS OF table t_payr

For all entries in t_regup

WHERE laufd EQ t_regup-laufd

AND laufi EQ t_regup-laufi

AND zbukr wa_regup-zbukr.

Endif.

for all entries will fetch u only those records for which the entries of the first table are met .

in the second select if i dont use for all entries then it will fetch the unwanted records as per teh selection screen pareameters.

first select is running on 10000 records out of which only 5000 are required .

so if i use for all entries then the second select will fetch me the records for 5000 ( correct entries )instead of again fetching for 10000 records.

so my second select will not run for 10000 records .

this greatly improves the performance .

hope this helps .

regards,

vijay.

[madan_ullasa]

Hi,

1. say for eg. u have an internal table itab1 with material no.

2. Now u want to select the descriptions for these mat. no. in itab1. from another data base table in another int. tab itab2.

3. Then u can use 'for all entries'.

This will help u to select the desciption for all entries in itab1. use the where condition in itab1 such that the fields are common in both the db table.

regarsd,

Madan...

[Former Member]

Hi Naveen,

The use of the for all entries in a way of selecting records more efficiently.

Small example:

Instead of:

LOOP AT <internal table> INTO <table_line>.

SELECT SINGLE *

FROM <db-table>

INTO <db-record>

WHERE .... = <table_line>-field.

ENDLOOP.

you could do:

SELECT *

FROM <db-table>

INTO TABLE <itab>

FOR ALL ENTRIES IN <internal table>

WHERE .... = <internal table>-field.

which will select all relevant records in one go.

Regards,

John.

[Former Member]

hi naveen,

<b>FOR ALL ENTRIES WHERE</b>

Syntax

... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

<b>Effect</b>

If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

The internal table itab must have a structured line type and the component comp must be compatible with the column col.

The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if <b>FOR ALL ENTRIES</b> is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.

<b>Notes</b>

In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.

The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.

Example

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom.

TYPES: BEGIN OF entry_tab_type,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

END OF entry_tab_type.

DATA: entry_tab TYPE TABLE OF entry_tab_type,

sflight_tab TYPE SORTED TABLE OF sflight

WITH UNIQUE KEY carrid connid fldate.

SELECT carrid connid

FROM spfli

INTO CORRESPONDING FIELDS OF TABLE entry_tab

WHERE cityfrom = p_city.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

FOR ALL ENTRIES IN entry_tab

WHERE carrid = entry_tab-carrid AND

connid = entry_tab-connid.

Message was edited by: Ashok Kumar Prithiviraj