diff of innerjoin &for all entries

[Former Member]

hi friends.

what is the diffrence b/w inner join and for all entries.

[Former Member]

Hi surekha,

1. INNER JOIN

DBTAB1 <----

---

> DBTAB2

It is used to JOIN two tables

having some COMMON fields.

2. Whereas

For All Entries,

DBTAB1 <----

---

> ITAB1

is not at all related to two 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.

regards,

amit m.

[Former Member]

hi amit.

thanks.

[Former Member]

1. For ALL Entries..

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on

internal tables. This can significantly improve the performance for large sets of selected data.

Eg.

DATA: TAB_SPFLI TYPE TABLE OF SPFLI,

TAB_SFLIGHT TYPE SORTED TABLE OF SFLIGHT

WITH UNIQUE KEY TABLE LINE,

WA LIKE LINE OF TAB_SFLIGHT.

SELECT CARRID CONNID

INTO CORRESPONDING FIELDS OF TABLE TAB_SPFLI

FROM SPFLI

WHERE CITYFROM = 'NEW YORK'.

SELECT CARRID CONNID FLDATE

INTO CORRESPONDING FIELDS OF TABLE TAB_SFLIGHT

FROM SFLIGHT

<b>FOR ALL ENTRIES IN TAB_SPFLI</b>

WHERE CARRID = TAB_SPFLI-CARRID AND

CONNID = TAB_SPFLI-CONNID.

LOOP AT TAB_SFLIGHT INTO WA.

AT NEW CONNID.

WRITE: / WA-CARRID, WA-CONNID.

ENDAT.

WRITE: / WA-FLDATE.

ENDLOOP.

2. Inner Join

You can read from more than one table in a single

SELECT statement, such that the data in the tables all has to meet the same conditions, using

the INNER JOIN .

eg.

DATA: BEGIN OF WA,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

FLDATE TYPE SFLIGHT-FLDATE,

BOOKID TYPE SBOOK-BOOKID,

END OF WA,

ITAB LIKE SORTED TABLE OF WA

WITH UNIQUE KEY CARRID CONNID FLDATE BOOKID.

SELECT P_{CARRID P}CONNID F_{FLDATE B}BOOKID

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM ( ( SPFLI AS P

<b>INNER JOIN SFLIGHT AS F ON P_{CARRID = F}CARRID AND

P~CONNID =

F~CONNID )

INNER JOIN SBOOK AS B ON B_{CARRID = F}CARRID AND

B_{CONNID = F}CONNID AND

B~FLDATE =

F~FLDATE )</b>

WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK' AND

F_{SEATSMAX > F}SEATSOCC.

LOOP AT ITAB INTO WA.

AT NEW FLDATE.

WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.

ENDAT.

WRITE / WA-BOOKID.

ENDLOOP.

Hope this will help you..

Reward points if helpful

[Former Member]

hi again,

1. simple example of for all entries.

2. NOTE THAT

In for all entries,

it is NOT necessary to use TWO DBTABLES.

(as against JOIN)

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_member188685]

hi surekha,

sample code for inner join

DATA: DATE   LIKE SFLIGHT-FLDATE, 
      CARRID LIKE SFLIGHT-CARRID, 
      CONNID LIKE SFLIGHT-CONNID. 

SELECT F~CARRID F~CONNID F~FLDATE 
    INTO (CARRID, CONNID, DATE) 
    FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
           ON F~CARRID = P~CARRID AND 
              F~CONNID = P~CONNID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '20010910' AND '20010920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / DATE, CARRID, CONNID. 
ENDSELECT.

sample code for for all entries..

select vbeln
       from vbak
       into table it_vbak
      where vbeln in s_vbeln.
if sy-subrc = 0.
 "before using for all entries either you should 
 "check sy-subrc or it_vbak table initial condition
select vbeln 
       posnr
       from vbap
       into table it_vbap
       for all entries in it_vbak
      where vbeln = it_vbak-vbeln.

endif.

endif.

you can use joins and for all entries combination also.

select vbak~vbeln
       vbap~posnr
       from vbak join vbap
       on vbak~vbeln = vbap~vbeln
       into table itab
       for all entries in it_vbeln
       where vbak~vbeln = it_vbeln-vbeln
             vbap~uepos = '0'.

regards

vijay

Message was edited by: Vijay Babu Dudla

[Former Member]

hi vijay

thank u

u have cleared my doubt.

regards ,

surekha