topic Re: for all entries in select clause in Application Development and Automation Discussions

for all entries in select clause

Former Member — Fri, 02 May 2008 08:48:58 GMT

for all entries in select clause,can u tell use of this one?

Re: for all entries in select clause

Sm1tje — Fri, 02 May 2008 08:51:03 GMT

select * from MARA

into table IT_MARA

for all entries in it_matnr

wheren matnr = it_matnr-matnr.

In table IT_MATNR there are for example 10 materials entered.

Now for all these materials (FOR ALL ENTRIES IN IT_MATNR)

we want to retrieve the data from DB TABLE MARA and put them into another internal table IT_MARA.

Edited by: Micky Oestreich on May 2, 2008 10:51 AM

Re: for all entries in select clause

prasanth_kasturi — Fri, 02 May 2008 08:52:02 GMT

Use of FOR ALL Entries

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

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

*Select * from zfligh appending table int_fligh*

For all entries in int_cntry

Where cntry = int_cntry-cntry.

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

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

REWARD IF HELPFUL

PRASANTH

Re: for all entries in select clause

Former Member — Fri, 02 May 2008 08:57:43 GMT

Select * from table into internal_table

for all entries in Itab1

where conditions.

But before using FOR ALL ENTRIES make sure the itab1 which you are using shoul not be blank, otherwise it will fetch all the entries from Database table which can cause the dump or can affect the performance of the program.

Rewards if helps...

Re: for all entries in select clause

vinod_vemuru2 — Fri, 02 May 2008 08:59:05 GMT

HI Vinay,

For all entries is replacement for Joins. What it will do is it will select all the records for all entries in specified table.

Some mandatory checks has to be done before using this. Else u will get unexpected results.

1. Check Driver table is initial or not. If driver table is initial then it will select all the records from the data base. So this is mandatory check.

2. Sort and delete duplicates based on where clause fields to improve performance.

3. Where clause fields of driver and driven tables field type and length must be same. Else u will get syntax error.

4. At least one field from for all entries in table must exist in where clause.

eg:

SORT i_mara BY matnr.

DELETE ADJACENT DUPLICATES FROM i_mara COMPARING matnr.

CHECK NOT i_mara[] IS INITIAL.

SELECT * FROM lips

INTO TABLE i_lips

FOR ALL ENTRIES IN i_mara

WHERE vbeln IN so_vbeln

AND matnr EQ i_mara-matnr.

Thanks,

Vinod.

Re: for all entries in select clause

Former Member — Fri, 02 May 2008 09:08:20 GMT

Hi Vinay,

For all entries is used to retrieve data from two or more tables.

In some cases , one of the table may be a cluster table .So we can't use the inner or outer join.

For example we have invoice details in VBRK and condition values in KONV.

we can fetch invoice details in one internal table.

eg;

Select vbeln fkdat knumv posnr from vbrk

into table it_vbrk where vbeln in so_vbeln.

The corresponding condition values for the invoices can be fetched from KONV table by writing a query with for all entries.

select knumv Kposn kschl kwert from konv

in to table it_konv for all entries in it_vbrk-vbeln

where knumv eq it_vbrk-knumv.

Regards,

Charumathi.B