Difference between For All Entries and Select End select.

[Former Member]

Please explain the difference between select-option and for all entries .which will be suitable in what conditions.

thanks in advance.

[former_member188685]

Select -endselect will go to each and every time to DB and get the single record.

but for all entries will go one shot and fetch the data for all the records which matches the where condition.

For all entries used to avoid performance issues.

try to avoid select and endselect.

Regards

Vijay

[Former Member]

Hi manish,

1. SELECT-ENDSELECT

and

For all entries,

Their purpose are entirely different.

2. Select-Endselect

is a raw version of normal sql select.

If there are 5 records,

it will loop 5 times, between select.....endselect.

3.

Whereas

For All Entries,

DBTAB1 <----

---

> ITAB1

is not at all related to two DATABASE tables.

It is related to INTERNAL table.

3. If we want to fetch data

from some DBTABLE1

but we want to fetch

for only some records

which are contained in some internal table,

then we use for alll entries.

*----

---

1. simple example of for all entries.

3. use this program (just copy paste)

it will fetch data

from T001

FOR ONLY TWO COMPANIES (as mentioned in itab)

4

REPORT abc.

DATA : BEGIN OF itab OCCURS 0,

bukrs LIKE t001-bukrs,

END OF itab.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

*----

---

itab-bukrs = '1000'.

APPEND itab.

itab-bukrs = '1100'.

APPEND itab.

*----

---

SELECT * FROM t001

INTO TABLE t001

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs.

*----

---

LOOP AT t001.

WRITE 😕 t001-bukrs.

ENDLOOP.

regards,

amit m.

[Former Member]

Hi manish,

Check the following Document for For all Entries with Select Statement

Variant 12

... FOR ALL ENTRIES IN itab WHERE cond

Effect

Selects only those lines of the database table which satisfy the WHERE condition cond where each occurring replacement symbol itab-f is replaced by the value of the component f in the internal table itab for at least one line. Clearly, a SELECT command with ... FOR ALL ENTRIES IN itab WHERE cond forms the union of solution sets for all SELECT commands which result when, for each line of the internal table itab , each symbol itab-f addressed in the WHERE condition is replaced by the relevant value of the component f in this table line. Duplicate lines are eliminated from the result set. If the internal table itab contains no entries, the processing continues as if the WHERE condition cond has failed.

Example

Display a full list of flights on 28.02.1995:

TABLES SFLIGHT.

DATA: BEGIN OF FTAB OCCURS 10,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID,

END OF FTAB,

RATIO TYPE F.

• Let FTAB be filled as follows:

*

• CARRID CONNID

• --------------

• LH 2415

• SQ 0026

• LH 0400

SELECT * FROM SFLIGHT FOR ALL ENTRIES IN FTAB

WHERE CARRID = FTAB-CARRID AND

CONNID = FTAB-CONNID AND

FLDATE = '19950228'.

RATIO = SFLIGHT-SEATSOCC / SFLIGHT-SEATSMAX.

WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID, RATIO.

ENDSELECT.

Notes

... FOR ALL ENTRIES IN itab WHERE cond can only be used with a SELECT command.

In the WHERE condition ... FOR ALL ENTRIES IN itab WHERE cond , the symbol itab-f always has the meaning of a replacement symbol and must not be confused with the component f of the header line in the internal table itab . The internal table itab does not have to have a header line.

The line structure of the internal table itab must be a field string. Each component of this field string which occurs in a replacement symbol in the WHERE condition must be of exactly the same type and length as the corresponding component in the table work area (see TABLES ).

Replacement symbols must not occur in comparisons with the operators LIKE , BETWEEN and IN .

FOR ALL ENTRIES IN itab excludes ORDER BY f1 ... fn in the ORDER-BY clause .

The internal table itab cannot be used at the same time in the INTO clause .

Also Check with the following Code

&----

---

*& Report ZSSSS1 *

*& *

&----

---

*& *

*& *

&----

---

REPORT ZSSSS1 LINE-SIZE 120

LINE-COUNT 25(3)

MESSAGE-ID ZSAN.

********************************************************************

*Program Desc: INTERACTIVE REPORT FOR PURCHASE ORDER DETAILS

• BASIC LIST CONTAINS HEADER DETAILS

• SECONDARY LIST CONTAINS ITEM DETAILS

• AND CONDETION RECORD DETAILS

********************************************************************

• T A B L E S U S E D *

********************************************************************

TABLES: EKKO,EKPO,KONV,LFA1,T001.

********************************************************************

• S E L E C T I O N S C R E E N *

********************************************************************

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-000.

SELECT-OPTIONS:

S_LIFNR FOR EKKO-LIFNR,

S_BUKRS FOR EKKO-BUKRS.

SELECTION-SCREEN END OF BLOCK B1.

********************************************************************

• D A T A D E C L A R A T I O N S *

********************************************************************

DATA: BEGIN OF IT_EKKO OCCURS 0,

BUKRS LIKE EKKO-BUKRS,

LIFNR LIKE EKKO-LIFNR,

EBELN LIKE EKKO-EBELN,

KNUMV LIKE EKKO-KNUMV,

END OF IT_EKKO.

DATA: BEGIN OF IT_EKPO OCCURS 0,

EBELN LIKE EKPO-EBELN,

EBELP LIKE EKPO-EBELP,

MATNR LIKE EKPO-MATNR,

INFNR LIKE EKPO-INFNR,

MENGE LIKE EKPO-MENGE,

MEINS LIKE EKPO-MEINS,

NETPR LIKE EKPO-NETPR,

END OF IT_EKPO.

DATA: BEGIN OF IT_KONV OCCURS 0,

KNUMV LIKE KONV-KNUMV,

KSCHL LIKE KONV-KSCHL,

KAWRT LIKE KONV-KAWRT,

KBETR LIKE KONV-KBETR,

KPOSN LIKE KONV-KPOSN,

END OF IT_KONV.

DATA: BEGIN OF IT_LFA1 OCCURS 0,

LIFNR LIKE LFA1-LIFNR,

NAME1 LIKE LFA1-NAME1,

STRAS LIKE LFA1-STRAS,

ORT01 LIKE LFA1-ORT01,

PSTLZ LIKE LFA1-PSTLZ,

LAND1 LIKE LFA1-LAND1,

END OF IT_LFA1.

DATA: V_BUTXT LIKE T001-BUTXT,

FNAM(20) TYPE C,

FVAL(20) TYPE C.

********************************************************************

• A T S E L E C T I O N - S C R E E N O N <FIELD> *

********************************************************************

AT SELECTION-SCREEN ON S_BUKRS.

PERFORM VALIDATE.

********************************************************************

• S T A R T - O F - S E L E C T I O N *

********************************************************************

START-OF-SELECTION.

PERFORM SELECT_DATA. " SELECTION OF ALL DATA

PERFORM DISPLAY_DATA. " DISPLAY OF BASIC LIST

********************************************************************

• A T L I N E S E L E C T I O N *

********************************************************************

AT LINE-SELECTION.

GET CURSOR FIELD FNAM VALUE FVAL.

CASE FNAM.

WHEN 'IT_EKKO-EBELN'.

PERFORM DISPLAY_SEC1.

WHEN 'IT_EKKO-LIFNR'.

PERFORM DISPLAY_SEC2.

WHEN OTHERS.

PERFORM DISPLAY_SEC3.

ENDCASE.

********************************************************************

• T O P - O F - P A G E *

********************************************************************

TOP-OF-PAGE.

PERFORM WRITE_HEADER.

********************************************************************

• T O P - O F - P A G E D U R I N G L I N E S E L *

********************************************************************

TOP-OF-PAGE DURING LINE-SELECTION.

PERFORM WRITE_HEADER2.

********************************************************************

• E N D - O F - P A G E *

********************************************************************

END-OF-PAGE.

PERFORM WRITE_FOOTER.

&----

---

*& Form SELECT_DATA

&----

---

• THIS FORM CONTAINS ALL SELECTION OF DATA STATEMENTS

----

---

form SELECT_DATA.

SELECT BUKRS LIFNR EBELN KNUMV

FROM EKKO

INTO TABLE IT_EKKO

WHERE LIFNR IN S_LIFNR

AND BUKRS IN S_BUKRS.

IF NOT IT_EKKO[] IS INITIAL.

SELECT EBELN EBELP MATNR INFNR MENGE MEINS NETPR

FROM EKPO

INTO TABLE IT_EKPO

FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-EBELN.

IF SY-SUBRC = 0.

LOOP AT IT_EKKO.

LOOP AT IT_EKPO.

SELECT KNUMV KSCHL KAWRT KBETR KPOSN

FROM KONV

INTO IT_KONV

WHERE KNUMV = IT_EKKO-KNUMV

AND KPOSN = IT_EKPO-EBELP.

APPEND IT_KONV.

ENDSELECT.

ENDLOOP.

ENDLOOP.

ENDIF." END OF IF FOR CHECK OF SY-SUBRC

SELECT LIFNR NAME1 STRAS ORT01 PSTLZ LAND1

INTO TABLE IT_LFA1

FROM LFA1

FOR ALL ENTRIES IN IT_EKKO

WHERE LIFNR = IT_EKKO-LIFNR.

ENDIF." END OF IF FOR IT_EKKO IS NOT INITIAL

endform. " SELECT_DATA

&----

---

*& Form DISPLAY_DATA

&----

---

• DISPLAY THE BASIC DATA

----

---

form DISPLAY_DATA.

DATA: LINNO TYPE I.

SORT IT_EKKO BY BUKRS LIFNR EBELN.

LOOP AT IT_EKKO.

READ TABLE IT_LFA1 WITH KEY LIFNR = IT_EKKO-LIFNR.

SELECT SINGLE BUTXT

FROM T001

INTO (V_BUTXT)

WHERE BUKRS = IT_EKKO-BUKRS.

WRITE:/1 SY-VLINE,

(10) IT_EKKO-EBELN HOTSPOT ON,

SY-VLINE,

(10) IT_EKKO-LIFNR HOTSPOT ON,

SY-VLINE,

(15) IT_LFA1-NAME1 HOTSPOT ON,

SY-VLINE,

(10) IT_EKKO-BUKRS HOTSPOT ON,

SY-VLINE,

(15) V_BUTXT,

75 SY-VLINE.

HIDE: IT_EKKO-EBELN,

IT_EKKO-LIFNR.

WRITE:/1(75) SY-ULINE.

AT END OF LIFNR.

LINNO = SY-LINCT - SY-LINNO - 1.

SKIP LINNO.

ENDAT.

ENDLOOP.

endform. " DISPLAY_DATA

&----

---

*& Form DISPLAY_SEC3

&----

---

form DISPLAY_SEC3.

LOOP AT IT_EKPO.

CLEAR: IT_EKPO, IT_KONV.

READ TABLE IT_EKPO WITH KEY EBELN = IT_EKKO-EBELN.

IF SY-SUBRC = 0.

FORMAT COLOR 3.

WRITE:/1 SY-VLINE,

(10) IT_EKPO-EBELN,

SY-VLINE,

(10) IT_EKPO-EBELP,

SY-VLINE,

(18) IT_EKPO-MATNR,

SY-VLINE,

(10) IT_EKPO-INFNR,

SY-VLINE,

(15) IT_EKPO-MENGE,

SY-VLINE,

(10) IT_EKPO-MEINS,

SY-VLINE,

(12) IT_EKPO-NETPR,

105 SY-VLINE.

FORMAT COLOR OFF.

WRITE:/1(105) SY-ULINE.

FORMAT COLOR 4.

READ TABLE IT_KONV WITH KEY KPOSN = IT_EKPO-EBELP.

IF SY-SUBRC = 0.

WRITE:/1 SY-VLINE,

(10) IT_KONV-KNUMV,

SY-VLINE,

(10) IT_KONV-KSCHL,

SY-VLINE,

(15) IT_KONV-KAWRT,

SY-VLINE,

(12) IT_KONV-KBETR,

SY-VLINE.

WRITE:/1(95) SY-ULINE.

FORMAT COLOR OFF.

ENDIF.

ENDIF.

ENDLOOP.

endform. " DISPLAY_SEC3

&----

---

*& Form DISPLAY_SEC1

&----

---

• TO CALL TRANSACTION ME23

----

---

form DISPLAY_SEC1.

SET PARAMETER ID 'BES' FIELD IT_EKKO-EBELN.

CALL TRANSACTION 'ME23' AND SKIP FIRST SCREEN.

endform. " DISPLAY_SEC1

&----

---

*& Form WRITE_HEADER

&----

---

• HEADER DISPLAY

----

---

form WRITE_HEADER.

FORMAT COLOR 3.

WRITE:/1 SY-VLINE,

(10) 'PUR.ORDER',

SY-VLINE,

(10) 'VENDOR',

SY-VLINE,

(15) 'NAME',

SY-VLINE,

(10) 'COMP.CODE',

SY-VLINE,

(15) 'COMP.DESC',

75 SY-VLINE.

WRITE:/1(75) SY-ULINE.

FORMAT COLOR OFF.

endform. " WRITE_HEADER

&----

---

*& Form WRITE_FOOTER

&----

---

• FOOTER OF LIST

----

---

form WRITE_FOOTER.

FORMAT COLOR 4.

WRITE: /1 'USER:',SY-UNAME,

45 'DATE:', SY-DATUM.

FORMAT COLOR OFF.

endform. " WRITE_FOOTER

&----

---

*& Form WRITE_HEADER2

&----

---

• HEADER FOR SECONDARY LIST

----

---

form WRITE_HEADER2.

FORMAT COLOR 4.

WRITE: /30 'INTELLIGROUP ASIA PVT LTD' CENTERED.

WRITE:/50 'HYDERABAD' CENTERED.

FORMAT COLOR 5.

WRITE:/1 SY-VLINE,

(10) 'PUR.ORDR',

SY-VLINE,

(10) 'ITEM',

SY-VLINE,

(18) 'MATERIAL NUMBER',

SY-VLINE,

(10) 'INFO.RECD',

SY-VLINE,

(15) 'QUANTITY',

SY-VLINE,

(10) 'UNITS',

SY-VLINE,

(12) 'NETPRICE',

105 SY-VLINE.

FORMAT COLOR OFF.

WRITE:/1(105) SY-ULINE.

FORMAT COLOR 6.

WRITE:/1 SY-VLINE,

(10) 'COND.RECD',

SY-VLINE,

(10) 'TYPE',

SY-VLINE,

(15) 'BASE VALUE',

SY-VLINE,

(12) 'AMOUNT',

SY-VLINE.

FORMAT COLOR OFF.

WRITE:/1(95) SY-ULINE.

endform. " WRITE_HEADER2

&----

---

*& Form DISPLAY_SEC2

&----

---

• TO DISPLAY VENDOR DETAILS IN POP UP WINDOW

----

---

form DISPLAY_SEC2.

READ TABLE IT_LFA1 WITH KEY LIFNR = IT_EKKO-LIFNR.

IF SY-SUBRC = 0.

WRITE:/ 'NAME IS:' , IT_LFA1-NAME1,

/ 'STREET IS:', IT_LFA1-STRAS,

/ 'CITY IS:', IT_LFA1-ORT01,

/ 'POSTEL CODE IS:',IT_LFA1-PSTLZ,

/ 'COUNTRY IS: ', IT_LFA1-LAND1.

WINDOW STARTING AT 1 15

ENDING AT 50 20.

ENDIF.

endform. " DISPLAY_SEC2

&----

---

*& Form VALIDATE

&----

---

• VALIDATE BUKRS

----

---

form VALIDATE.

SELECT SINGLE *

FROM T001

WHERE BUKRS IN S_BUKRS.

IF SY-SUBRC <> 0.

MESSAGE E001.

ENDIF.

endform. " VALIDATE

Please reward if helpful.

Regards,

pankaj singh(syntel).

[Former Member]

Hi manish,

The major difference between select-endselect and for all entries is only because of performance tunning.

In select endselect the loop will get executed as per the where condition and each time the execution will move from application server to the database server.

it takes more time.

But in For all entries the loop will be executed only for the number of records present in the previous level.

example.

SELECT EBELN

BUKRS

ERNAM

AEDAT

LIFNR

EKORG

WAERS

FROM EKKO

INTO CORRESPONDING FIELDS OF

TABLE IT_PO_HEAD

WHERE EBELN IN S_EBELN.

SELECT EBELN

EBELP

MATNR

MENGE

NETPR

NETWR

WERKS

FROM EKPO

INTO CORRESPONDING FIELDS OF

IT_PO_DETAILS

WHERE EBELN = IT_PO_HEAD-EBELN.

APPEND IT_PO_DETAILS.

ENDSELECT.

--

---

FOR ALL ENTRIES--

---

SELECT EBELN

BUKRS

ERNAM

AEDAT

LIFNR

EKORG

WAERS

FROM EKKO

INTO CORRESPONDING FIELDS OF

TABLE IT_PO_HEAD

WHERE EBELN IN S_EBELN.

SELECT EBELN

EBELP

MATNR

MENGE

NETPR

NETWR

WERKS

FROM EKPO

INTO CORRESPONDING FIELDS OF

TABLE IT_PO_DETAILS

FOR ALL ENTRIES IN IT_PO_HEAD

WHERE EBELN = IT_PO_HEAD-EBELN.

[Former Member]

I want to know for what condition select endselect will be better then for all entries related to performance.

Thanks

Manish

[Former Member]

Hi Manish ,

I dont think Seelct - EndSelect will be better than select for all entries , because when you you use select - endselect you are basically accessing the data base a number a times where as in for all entries it is done only once.

Only one disadvantage you have with for all entries is that it does not allow duplicate entries , so it is generally suggested to select all the primary key fields for the table from which you are retreiving the data.

Thanks

Arun

[Former Member]

Hi manish,

Yes there are some conditions .

1. suppose you have 4lakh recodes in your internal table.Based on internal table values you want to extract some more values from other table which is very big interms of recods.

In above situation if you use for all entries the system performance will be down and some times it will go for dump(after exceeding the access time).In this situations you can use select......... endselect by adding extra statement package size 30000.In between select..... endselect you can write your logic .

Here first the system will select 30000 records based on those it will process the given logic again it will go for another 30000 records..

Pls reward if helpful

[Former Member]

Hi muralikrishna ,

You are right,I wanted to confirm the same as i have tested this with one of the program and it happened with me and that point of time i was confused .

Thanks