GoodleAds

SELECT FOR ALL ENTRIES DOCUMENT WITH EXAMPLE



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-KUNNRW_FINAL-NAME1,
        W_FINAL
-LAND1W_FINAL-ORT01,  "DISPLAYING THE OUTPUT
        W_FINAL
-BANKN COLOR 3W_FINAL-BANKL COLOR 4.
ENDLOOP.

No comments:

Post a Comment