topic Re: SELECT STATEMENT in Application Development and Automation Discussions

SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 14:52:33 GMT

HI,

can u plz tell me ,

when to use 'for all entries' in select statement,

give one example.

Re: SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 14:53:44 GMT

Hi Pawan,

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 SCARR
  INTO TABLE t_scarr.

LOOP AT t_SCARR INTO wa_scarr.
  SELECT SINGLE *
    FROM sflight
    INTO wa_sflight
   WHERE carrid EQ wa_scarr-carrid.
 
  APPEND wa_sflight TO t_sflight.
ENDLOOP.

Instead of the Above use below code:

SELECT *
  FROM SCARR
  INTO TABLE t_scarr.

SELECT *
  FROM SFLIGHT
  INTO TABLE t_sflight
   FOR ALL ENTRIES IN scARR
 WHERE carrid EQ t_scarr.

this condition, return all entries of the sflight

Refer the Below Links for more Info:

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

Regards,

Sunil

Re: SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 14:54:59 GMT

Refer to the below related thread

/message/5219519#5219519 [original link is broken]

Re: SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 14:56:16 GMT

Hello,

You use the for all entries in a select when you want to do a join.

Follow an example:



SELECT * FROM scarr INTO TABLE lt_scarr.

IF lt_scarr[] IS NOT INITIAL.
  SELECT * FROM spfli INTO TABLE lt_spfli
    FOR ALL ENTRIES IN lt_scarr
    WHERE CARRID = lt_scarr-carrid.
ENDIF.

In the code above will be selected all records from table SPFLI where the CARRID exists in table SCARR.

Regards,

Re: SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 14:56:18 GMT

Hi,

Yo use For all entries when you need to select data with the "Where condition" based in a internal table already filled.

regards,

Fernando

Re: SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 14:59:36 GMT

HI pawan

when u want to join two select statements u can use For all entries and some prerequiste for using for all entries is there should be some values in internal table.

for example

select *

APPENDING CORRESPONDING FIELDS OF TABLE i_a081

from a082

for all entries in T_ESLHL_CONTR

where kont_pack = T_ESLHL_CONTR-packno

and kont_zeile = T_ESLHL_CONTR-introw

and kappl = 'MS'

and kschl = 'PRS'

and datab LE p_pr_dat

and datbi GE p_pr_dat.

if not i_a081 is initial.

sort i_a081.

select *

into table i_konp

from konp

for all entries in i_a081

where knumh = i_a081-knumh

and kappl = i_a081-kappl

and kschl = i_a081-kschl

and loevm_ko = space.

endif.

i_a081and i_a082 are the two internal tables see how they are used.

rewards points if useful please

Thanks

abdul.

Re: SELECT STATEMENT

Former Member — Tue, 29 Apr 2008 15:04:24 GMT

Hi,

You can use For All Entries Statement inorder to fetch the data from more that 1 tables based on the Primary key. i.e here we are joinning more than one tables to extract the required fields.

For all entries is advisable rather than Inner joins because, we are hitting the database at once and putting the data into Internal table and then we perform the For All Entries. Where as the Inner Joins hit the database itself for all the required no.of records..

Here I provide you an example where you can select the data from two tables: vbak and vbap..using For All Entries:

START-OF-SELECTION.

select vbeln

vkorg

vtweg

spart

from vbak

into table it_vbak

where vbeln in s_vbeln.

if not it_vbak[] is initial.

select vbeln

posnr

matnr

netwr

brgew

ntgew

from vbap

into table it_vbap

for all entries in it_vbak

where vbeln = it_vbak-vbeln.

endif.

loop at it_vbap.

read table it_vbak with key vbeln = it_vbap-vbeln.

if sy-subrc = 0.

move: it_vbak-vbeln to it_final-vbeln,

it_vbak-vkorg to it_final-vkorg,

it_vbak-vtweg to it_final-vtweg,

it_vbak-spart to it_final-spart,

it_vbap-posnr to it_final-posnr,

it_vbap-matnr to it_final-matnr,

it_vbap-netwr to it_final-netwr,

it_vbap-brgew to it_final-brgew,

it_vbap-ntgew to it_final-ntgew.

append it_final.

endif.

Regards,

Reena