topic Re: problem with innor join in Application Development and Automation Discussions

problem with innor join

Former Member — Thu, 23 Nov 2006 15:56:57 GMT

Hi sdn exports,

1) when we go for "for all entries , and when we go for innor join " concept?

Re: problem with innor join

Former Member — Thu, 23 Nov 2006 16:04:43 GMT

hi,

"FOR ALL ENTRIES IN..." (outer join) are very fast but keep in the mind the special features and 3 pitfalls of using it.

(a) Duplicates are removed from the answer set as if you had specified "SELECT DISTINCT"... So unless you intend for duplicates to be deleted include the unique key of the detail line items in your select statement. In the data dictionary (SE11) the fields belonging to the unique key are marked with an "X" in the key column.

(b) If the "one" table (the table that appears in the clause FOR ALL ENTRIES IN) is empty, all rows in the "many" table (the table that appears in the SELECT INTO clause ) are selected. Therefore make sure you check that the "one" table has rows before issuing a select with the "FOR ALL ENTRIES IN..." clause.

(c) If the 'one' table (the table that appears in the clause FOR ALL ENTRIES IN) is very large there is performance degradation Steven Buttiglieri created sample code to illustrate this.

Proper use of Inner Join

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

Regards,

Naveen

Re: problem with innor join

Former Member — Fri, 24 Nov 2006 06:36:40 GMT

comin to ur first question of for all entries ,

for all entries is useful wen u need to avoid nested select statements .

consider the foll example ..

select * from <table1> into wa1 .

select * from <table2> into wa2 where <field1> eq wa1-<field1> .

processing ...

endselect .

now in the above case u will b again n again requesting data from database .

a better practice would be to program it in foll manner .

select * from <table1> into table itab1 .

select * from <table2> into table itab2 for all entries in itab1 where <field1> eq itab1-field1 .

this performs union of all the result set for each entry in itab1 .

so duplicate rows r removed .

and one more thing if the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records.

now inner-join ...

inner join is mostly used when u need certain fields of one database table and other database table which have some fields in common .

u can use it a follows ...

select a_{field1 a}field2 b~field3 into table <itab1> from <db1>

as inner join <db2> as b on a_{field1 eq b}field1 .

if helpful then plz reward points ..

reagards ,

swapnil .

Re: problem with innor join

anversha_s — Fri, 24 Nov 2006 06:41:37 GMT

hi,

FOR ALL ENTRIES is an effective way of doing away with using JOIN on two tables.

You can check the below code -

SELECT BUKRS BELNR GJAHR AUGDT

FROM BSEG

INTO TABLE I_BSEG

WHERE BUKRS = ....

SELECT BUKRS BELNR BLART BLDAT

FROM BKPF

INTO TABLE I_BKPF

FOR ALL ENTRIES IN I_BSEG

WHERE BUKRS = I_BSEG-BUKRS

AND BELNR = I_BSEG-BELNR

AND BLDAT IN SO_BLDAT.

*******************************8

look another example

what is the use of FOR ALL ENTRIES

1. INNER JOIN

DBTAB1 <----

> DBTAB2

It is used to JOIN two DATABASE tables

having some COMMON fields.

2. 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.

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)

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.

Hope this helps!

Regards,

Anver