topic Re: for all in Application Development and Automation Discussions

for all

Former Member — Mon, 18 Jun 2007 13:54:22 GMT

hi experts,

how can we design and use select for all entries.how can it work .explaine me brief.

thanks in advance

Re: for all

Former Member — Mon, 18 Jun 2007 13:56:53 GMT

Hi Surendra,

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)

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

Hope you understand the usage specified.

Thanks,

Sapna.

Re: for all

Former Member — Mon, 18 Jun 2007 13:57:21 GMT

Hi Surendra,

We use for all entries when there is a requirement to fetch values from databse table for the entries present in a internal table.

main condition for "For all entries" is that the internal table using which data is selected should not be empty, otherwise it will select all the data from the database table.

Hope this helps.

award forum points to helpful answers

Re: for all

Former Member — Mon, 18 Jun 2007 13:58:45 GMT

Hello,


... FOR ALL ENTRIES IN itab WHERE cond 


Effect 
Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. 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. 


Example 
Displaying the occupancy of flights on 28.02.1995: 


TYPES: BEGIN OF FTAB_TYPE, 
         CARRID LIKE SFLIGHT-CARRID, 
         CONNID LIKE SFLIGHT-CONNID, 
       END OF FTAB_TYPE. 

DATA:  FTAB TYPE STANDARD TABLE OF FTAB_TYPE WITH 
                 NON-UNIQUE DEFAULT KEY INITIAL SIZE 10, 
       RATIO TYPE P DECIMALS 3, 
       WA_SFLIGHT TYPE SFLIGHT. 

* Suppost FTAB is filled as follows: 
* 
* CARRID  CONNID 
* -------------- 
* LH      2415 
* SQ      0026 
* LH      0400 

SELECT * FROM SFLIGHT INTO WA_SFLIGHT 
    FOR ALL ENTRIES IN FTAB 
    WHERE CARRID = FTAB-CARRID AND 
          CONNID = FTAB-CONNID AND 
          FLDATE = '19950228'. 
  RATIO = WA_SFLIGHT-SEATSOCC / WA_SFLIGHT-SEATSMAX. 
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, RATIO. 
ENDSELECT. 

* The statement has the same effect as: 

SELECT DISTINCT * FROM SFLIGHT INTO WA_SFLIGHT 
    WHERE ( CARRID = 'LH'   AND 
            CONNID = '2415' AND 
            FLDATE = '19950228' ) OR 
          ( CARRID = 'SQ'   AND 
            CONNID = '0026' AND 
            FLDATE = '19950228' ) OR 
          ( CARRID = 'LH'   AND 
            CONNID = '0400' AND 
            FLDATE = '19950228' ). 
  RATIO = WA_SFLIGHT-SEATSOCC / WA_SFLIGHT-SEATSMAX. 
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, RATIO. 
ENDSELECT. 


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



In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internaln table itab does not have to have a header line. 



The line structure of the internal table iab must be a structure. Each component of the structure that appears in the WHERE condition as a replacement symbol must have exactly the same type and length as the corresponding component of the table work area (see TABLES). 



You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions. 



If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause. 



You cannot use the internal table itab in the INTO clause as well. 


Notes 
Performance: 



Specify all conditions in the WHERE clause. This means that you do not transport redundant data over the network only to filter it out in your program (using CHECK, for example). 


If you regularly use a SELECT statement, you should create an index. In the WHERE clause, you should use the fields of the index, linked using AND, and checking for equality. Fields of an index that occur after a field for which you do not use an equality comparison in the WHERE clause (EQ or =) cannot be used to restrict the search. 


The logical expression NOT in a WHERE clause is not supported by indexes. For example, WHERE FLDATE >= '19950228' is better than WHERE NOT FLDATE < '19950228'.

If useful reward.

Vasanth

Re: for all

gopi_narendra — Mon, 18 Jun 2007 14:01:40 GMT

For all entries is a substitute for a select statement with in a loop.

It fetches data from a table for a set values for a particular field in the table.

ITAB contains the Sales Order Nos.

You need to get the QTY, PRICE for all the sales orders for its items

select <SONum> <SOItem> <QTY> <PRICE> from VBAP

for all entries in ITAB

where VBELN = ITAB-VBELN.

loop at ITAB.

select <SONum> <SOItem> <QTY> <PRICE> from VBAP

where VBELN = ITAB-VBELN.

endloop.

Both the above statement does the same but peformance wise we prefer FOR ALL ENTRIES

Regards

Gopi

Re: for all

Former Member — Mon, 18 Jun 2007 14:01:42 GMT

Check before using For all entries.

1. The table which is used for all entries should not be empty. If it is empty then query pick all the record( which is wrong selection).

2. Ensure the key fields are included in select query.

3. Ensure the order fields used in select query. It should be in the order as in the table order.

4. Ensure the Where condition given in the for all entries are also in the correct order.

Example for 'FOR ALL ENTRIES :

Consider table t_vbak:

vbeln audat

0001 12.05.07

0002 12.05.07

if u use t_vbak in for all entries :

select vbeln posnr from vbap into t_vbap

for all entries in t_vbap

where vbeln = t_vbak-vbeln.

Then the resullt will be :

vbeln posnr

0001 x

0001 y

0001 z

0002 x

0002 y

it will select the posnr only for 0001 and 0002.

Reward points if useful.

Re: for all

Former Member — Mon, 18 Jun 2007 14:04:36 GMT

Hi,

For all entries is used in order to avoid a select inside a loop.

Suppose you want to select some values from a database table depending upon some valiues in an internal table.One way to do so is to use a select in a loop.

loop at itab1.

select.....where <f1> = itab1-f2.

endloop.

But to avoid this and have better performance we must first select in an internal table values for all entries in the first internal table<itab1>.

select ......into <itab2> for all entries in itab1.

Then in the loop read the table itab2 depending on value of itab1.

loop at itab1.

read itab2 where <f1> = itab2-f2.

endloop.

Hope it is useful.

Thanks,

Sandeep.