Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select For ALL entries

Former Member
0 Kudos

Hi friends....

What is the prerequisites for FOR ALL ENTRIES ???

what is the difference between select for all entries and inner join

when will we go for For all entries and inner join???

pls answer these questions... helpful answers should be rewarded

6 REPLIES 6

Former Member
0 Kudos

Hi Sekhar,

inner join used for combing two table.

support u can fetch two table. u can write select query two time .data base fetch time take twotime read from db.

so using primary key we easy write inner join. but both table primary key and both field refer same field.

Inner Join and Outer Join

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

Also,

Select Statements contd…For All Entries

• The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

• Large amount of data

• Mixing processing and reading of data

• Fast internal reprocessing of data

• Fast

The Minus

• Difficult to program/understand

• Memory could be critical (use FREE or PACKAGE size)

Points to be must considered FOR ALL ENTRIES

• Check that data is present in the driver table

• Sorting the driver table

• Removing duplicates from the driver table

Consider the following piece of extract

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

The above mentioned can be more optimized by using the following code.

Sort int_cntry by cntry.

Delete adjacent duplicates from int_cntry.

If NOT int_cntry[] is INITIAL.

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Endif.

kindly reward if found helpful.

cheers,

Hema.

Former Member
0 Kudos

Hi sekhar,

If have a senario like this we have to join 4 tables.Every one use inner join for that senario .If we use inner join for that one.The performance of the report is very very bad.For that reason we have to use FOR ALL ENTRIES instead of INNER JOIN.

WHEN WILL WE USE FOR ALL ENTRIES?

If we want to join more than three table u better to go for FOR ALL ENTRIES instead of INNER JOIN.

Award points if helpful

Kiran Kumar.G.A

Former Member
0 Kudos

Hi sekhar,

For using for all entries your internal table that you are using in For all entries should be filled otherwise you will get wrong output.

if not itab[] is initial.

select *

from Z table

into itab2

fro all entries in itab

where conditions.

endif.

Former Member
0 Kudos

Hi,

for example if u have a select query..

select * from makt into tablei tab_makt

for all entries in itab_mara

where matnr = itab_mara-matnr.

first check itab_mara is not initial...

if itab_mara contains N entries then it will fetch N records from MAKT table...

if it is initial then the full table MAKT will be kept in the Internal table Itab_makt.

seond

Using joins we can implement ineer join, outer join...

for all entries implements INNER join....

We can not use Join statement on cluster tables.

we can use Join statement only on DB tables.

if u have some data in itab and u want to get the details of those records from another table then we use for all entries.

reward if useful.

Regards,

Kiran.

Former Member
0 Kudos

Hi Sekhar,

Select is used for the retrival of required fields form the database tables.

For All Entries is best for joining 2 or more than 2 tables.

But when we are using for all entries the involved table should not be initial.If this condition is not satisfied then total dump will be loaded and error occurs.

i.e., if not itab[] is initial.

Join is best for joining only 2 tables.

if there are more than 2 tables better we go for For All Entries.

bye Sekhar.

Former Member
0 Kudos

Hi,

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

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.

Example:

DATA: BEGIN OF LINE,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

CITYFROM TYPE SPFLI-CITYFROM,

CITYTO TYPE SPFLI-CITYTO,

END OF LINE,

ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.

LINE-CITYTO = 'BERLIN'.

APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.

LINE-CITYTO = 'SAN FRANCISCO'.

APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO

INTO CORRESPONDING FIELDS OF LINE

FROM SPFLI

FOR ALL ENTRIES IN ITAB

WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

Regards,

Renjith Michael.