select for all entries

[Former Member]

Hi,

I am new in abap reports. Now i want to know why we should use select for all entries in query. We can do retrieve directly by accessing the table in database dictionary.

Experts please give me the reasons I want to know the concepts behind it.It will be better if you kindly explain this with help of code.

With regards,

Abir.

[Former Member]

Hi ,

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

You can only use FOR ALL ENTRIES IN ...WHERE ...in a SELECT statement.

SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT

statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol

itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result

set. 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 (in the current client).

for example:

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

this condition, return all entries of the sflight

<i>Hope This Info Helps YOU.</i>

Regards,

Lakshmi

[Former Member]

Hi,

If you a master table data in a internal table and want to retrieve all the child records then you will have loop at the master table and fire a select on the child table, now this is not the efficient way of doing it.

So, you use FOR ALL ENTRIES, where you tell the system that you need to get values for all the rows of the master table.

Regards,

Ravi

Note : Please mark all the helpful answers

[Former Member]

Hi abir,

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.

4. just copy paste in new program.

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 = '1001'.

append itab.

*----

---

select * from t001 into table t001

for all entries in itab

where bukrs = itab-bukrs.

break-point.

regards,

amit m.

[Former Member]

Hi Abir,

For all entries is a very useful code in certain circumstances...

like say u want description of materials in a certain range..say 10000 to 11000...

so first u ll select all the materials within this range from table MARA using a simple select statement..

then instead of using select in a loop to get the description of all these materials, u can simply say

<b>select maktx from makt into t_makt for all entries in t_mara where matnr = t_mara-matnr</b>

*t_mara->table wher u fetched mara entries..

u need to be careful abt one thing in using this.. if t_mara is empty, then it selects ALL the records from makt.

hope it helps...

[Former Member]

HI

GOOD

SELECT

Basic form

SELECT result [target] FROM source [where] [GROUP BY fields] [ORDER BY order].

Effect

Retrieves an extract and/or a set of data from a database table or view (see Relational database ). SELECT belongs to the OPEN SQL command set.

Each SELECT command consists of a series of clauses specifying different tasks:

The SELECT result clause specifies

whether the result of the selection is a table or a single record,

which columns the result is meant to have and

whether the result is allowed to include identical lines.

The INTO target clause specifies the target area into which the selected data is to be read. If the target area is an internal table, the INTO clause specifies

whether the selected data is to overwrite the contents of the internal table or

whether the selected data is to be appended to the contents and

whether the selected data is to be placed in the internal table all at once or in several packets.

The INTO clause can also follow the FROM clause.

You can omit the INTO clause. The system then makes the data available in the table work area (see TABLES ) dbtab . If the SELECT clause includes a "*", the command is processed like the identical SELECT * INTO dbtab FROM dbtab statement. If the SELECT clause contains a list a1 ... an , the command is executed like SELECT a1 ... an INTO CORRESPONDING FIELDS OF dbtab FROM dbtab .

If the result of the selection is meant to be a table, the data is usually (for further information, see INTO -Klausel ) read line by line within a processing loop introduced by SELECT and concluded by ENDSELECT . For each line read, the processing passes through the loop once. If the result of the selection is meant to be a single record, the closing ENDSELECT is omitted.

The FROM source clause the source (database table or view ) from which the data is to be selected. It also determines

the type of client handling,

the behavior for buffered tables and

the maximum number of lines to be read.

The WHERE where clause specifies the conditions which the result of the selection must satisfy. It thus determines the lines of the result table. Normally - i.e. unless a client field is specified in the WHERE clause - only data of the current client is selected. If you want to select across other clients, the FROM clause must include the addition ... CLIENT SPECIFIED .

The GROUP-BY fields clause combines groups of lines together into single lines. A group is a set of lines which contain the same value for every database field in the GROUP BY clause.

The ORDER-BY order clause stipulates how the lines of the result table are to be ordered.

Each time the SELECT statement is executed, the system field SY-DBCNT contains the number of lines read so far. After ENDSELECT , SY-DBCNT contains the total number of lines read.

The return code value is set as follows:

SY-SUBRC = 0 At least one line was read.

SY_SUBRC = 4 No lines were read.

SY-SUBRC = 8 The search key was not fully qualified.

(nur bei SELECT SINGLE ). The returned single record is any line of the solution set.

Example

Output the passenger list for the Lufthansa flight 0400 on 28.02.1995:

TABLES SBOOK.

SELECT * FROM SBOOK

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'

ORDER BY PRIMARY KEY.

WRITE: / SBOOK-BOOKID, SBOOK-CUSTOMID, SBOOK-CUSTTYPE,

SBOOK-SMOKER, SBOOK-LUGGWEIGHT, SBOOK-WUNIT,

SBOOK-INVOICE.

ENDSELECT.

Note

Performance

In client/server environments, storing database tables in local buffers (see SAP buffering ) can save considerable amounts of time because the time required to make an access via the network is much more than that needed to access a locally buffered table.

Notes

A SELECT command on a table for which SAP buffering is defined in the ABAP/4 Dictionary is normally satisfied from the SAP buffer by bypassing the database. This does not apply with

- SELECT SINGLE FOR UPDATE

- SELECT DISTINCT in the SELECT clause ,

- BYPASSING BUFFER in the FROM clause ,

- ORDER BY f1 ... fn in the ORDER-BY clause ,

- aggregate functions in the SELECT clause ,

- when using IS [NOT] NULL WHERE condition ,

or if the generic key part is not qualified in the WHERE-Bedingung for a generically buffered table.

Authorization checks are not supported by the SELECT statement, so you must program these yourself.

In dialog systems, the database system locking mechanism cannot always guarantee to synchronize the simultaneous access of several users to the same dataset. In many cases, it is therefore advisable to use the SAP locking mechanism .

Changes to data in a database are only finalized after a database commit (see LUW ). Prior to this, any database update can be reversed by a database rollback (see Programming transactions ). At the lowest isolation level (see the section on the "uncommitted read" under Locking mechanism ), this can result in the dataset selected by the SELECT command not really being written to the database. While a program is selecting data, a second program can add, change or delete lines at the same time. Then, the changes made by the second program are reversed by rolling back the database system. The selection of the first program thus reflects only a very temporary state of the database. If such "phantom data" is not acceptable for a program, you must either use the SAP locking mechanism or at least set the isolation level of the database system to "committed read" (see Locking mechanism ).

In a SELECT-ENDSELECT loop, the CONTINUE statement terminates the current loop pass prematurely and starts the next.

If one of the statements in a SELECT ... ENDSELECT loop results in a database commit, the cursor belonging to the SELECT ... ENDSELECT loop is lost and the processing terminates with a runtime error. Since each screen change automatically generates a database commit, statements such as CALL SCREEN , CALL DIALOG , CALL TRANSACTION or MESSAGE are not allowed within a SELECT ... ENDSELECT loop.

Related OPEN CURSOR , FETCH und CLOSE CURSOR

GO THROUGH THIS LINK

http://www.geocities.com/SiliconValley/Campus/6345/select.htm

THANKS

MRUTYUN

[Former Member]

Hai Abir

Go through the following Document

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)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

check the following Example

************************************************************************

• Table Declaration *

************************************************************************

TABLES: mara.

************************************************************************

• Types Declaration *

************************************************************************

TYPES: BEGIN OF typ_mara,

matnr TYPE mara-matnr, "Material Number"

mbrsh TYPE mara-mbrsh, "Industrial Sector"

mtart TYPE mara-mtart, "Material Type"

meins TYPE mara-meins, "Base Unit of Measure"

END OF typ_mara.

TYPES: BEGIN OF typ_makt,

matnr TYPE makt-matnr, "Material Number"

maktx TYPE makt-maktx, "Material Description"

END OF typ_makt.

************************************************************************

• Intrnal tables Declaration *

************************************************************************

DATA: it_mara TYPE STANDARD TABLE OF typ_mara WITH HEADER LINE.

DATA: it_makt TYPE STANDARD TABLE OF typ_makt WITH HEADER LINE.

************************************************************************

• Selection Screen *

************************************************************************

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : s_matnr FOR mara-matnr.

SELECTION-SCREEN : SKIP.

PARAMETERS : p_mtart LIKE mara-mtart.

SELECTION-SCREEN : END OF BLOCK b1.

************************************************************************

• Start of Selection *

************************************************************************

START-OF-SELECTION.

***

SELECT matnr

mbrsh

mtart

meins

INTO TABLE it_mara

FROM mara

WHERE matnr IN s_matnr and

mtart = p_mtart.

if sy-subrc = 0.

sort it_mara by matnr.

endif.

IF NOT it_mara[] IS INITIAL.

SELECT matnr

maktx

INTO TABLE it_makt FROM makt

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr

AND spras = sy-langu.

if sy-subrc = 0.

sort it_makt by matnr.

endif.

DATA: a TYPE i.

loop at it_mara.

a = sy-tabix MOD 2.

IF a = 1.

FORMAT COLOR 5.

ELSE.

FORMAT COLOR OFF.

ENDIF.

read table it_makt with key matnr = it_mara-matnr

binary search.

if sy-subrc = 0.

WRITE:/ sy-vline.

WRITE: 2 it_mara-matnr,

18 sy-vline, it_mara-mbrsh,

38 sy-vline, it_mara-mtart,

54 sy-vline, it_mara-meins,

78 sy-vline, it_makt-maktx, 155 sy- vline.

clear : it_mara.

endif.

clear : it_makt.

endloop.

WRITE: /(155) sy-uline.

************************************************************************

Thanks & regards

Sreeni