SELECT FOR ALL ENTRIES
1. Select for all entries statement is used to
replace select with joins.
2. Because joins statement can’t be used for more
than 3 tables, it puts heavy load on Database
because
the data has to be selected by comparing each table in Database server.
3. It takes more time for execution.
4. In such scenarios we go for SELECT FOR ALL
ENTRIES.
SYNTAX
Select
field1
field2 field3
From <DATABASE TABLE1>
Into Table
<ITAB1>
Where<Condition>.
If ITAB1
is not initial.
Select
field1 field2
field3 field4 From <DATABASE TABLE2>
Into Table<ITAB2>
For all
entries in ITAB1
Where
Field1 = ITAB1 - Field1 and Field2 = ITAB1-Field2
Endif.
5. The precondition for
the above statement is, the first ITAB1 should be checked whether it has any
data.
6. Suppose if it has data, then for
all entries statement will execute based on the condition and only matching
records will be selected.
7. Suppose if first
ITAB1 is empty then for all entries statement will fail and all the records
will be selected, irrespective of
condition
EXAMPLE PROGRAM ON
SELECT..FOR ALL ENTRIES
********************************************************************
"STRUCTURE FOR KNA1
********************************************************************
TYPES:
BEGIN OF TY_KNA1,
KUNNR TYPE KNA1-KUNNR,
NAME1 TYPE KNA1-NAME1,
LAND1 TYPE KNA1-LAND1,
ORT01 TYPE KNA1-ORT01,
END OF TY_KNA1,
********************************************************************
"STRUCTURE FOR KNBK
********************************************************************
BEGIN OF TY_KNBK,
KUNNR TYPE KNBK-KUNNR,
BANKN TYPE KNBK-BANKN,
BANKL TYPE KNBK-BANKL,
END OF TY_KNBK,
********************************************************************
"STRUCTURE FOR FINAL OUTPUT
********************************************************************
BEGIN OF TY_FINAL,
KUNNR TYPE KNA1-KUNNR,
NAME1 TYPE KNA1-NAME1,
LAND1 TYPE KNA1-LAND1,
ORT01 TYPE KNA1-ORT01,
BANKN TYPE KNBK-BANKN,
BANKL TYPE KNBK-BANKL,
END OF TY_FINAL.
********************************************************************
"INTERNAL TABLE DECLARATIONS
********************************************************************
DATA:
I_KNA1 TYPE TABLE OF TY_KNA1,
I_KNBK TYPE TABLE OF TY_KNBK,
I_FINAL TYPE TABLE OF TY_FINAL,
********************************************************************
"WORK ARAEA DECLARATIONS
********************************************************************
W_KNA1 TYPE TY_KNA1,
W_KNBK TYPE TY_KNBK,
W_FINAL TYPE TY_FINAL.
SELECT
KUNNR NAME1
LAND1 ORT01 FROM KNA1
INTO TABLE I_KNA1. "FETCHING THE DATA FROM KNA1
IF I_KNA1 IS NOT INITIAL.
SELECT KUNNR BANKN BANKL
FROM KNBK "APPLYING SELECT..FOR ALL ENTRIES BASED ON I_KNA1
INTO TABLE I_KNBK
FOR ALL ENTRIES IN I_KNA1
WHERE KUNNR = I_KNA1-KUNNR.
ENDIF.
SORT I_KNA1 BY KUNNR.
*SORT I_KNBK BY KUNNR.
LOOP AT I_KNA1 INTO W_KNA1.
READ TABLE I_KNBK INTO W_KNBK WITH KEY KUNNR = W_KNA1-KUNNR.
W_FINAL-KUNNR = W_KNA1-KUNNR.
W_FINAL-NAME1 = W_KNA1-NAME1.
W_FINAL-LAND1 = W_KNA1-LAND1. "APPENDING KNA1,KNBK DATA TO I_FINAL
W_FINAL-ORT01 = W_KNA1-ORT01.
W_FINAL-BANKN = W_KNBK-BANKN.
W_FINAL-BANKL = W_KNBK-BANKL.
APPEND W_FINAL TO I_FINAL.
ENDLOOP.
LOOP AT I_FINAL INTO W_FINAL.
WRITE:/ W_FINAL-KUNNR, W_FINAL-NAME1,
W_FINAL-LAND1, W_FINAL-ORT01, "DISPLAYING THE OUTPUT
W_FINAL-BANKN COLOR 3, W_FINAL-BANKL COLOR 4.
ENDLOOP.
