topic Re: SELECT FOR ALL ENTRIES in Application Development and Automation Discussions

SELECT FOR ALL ENTRIES

Former Member — Wed, 30 Jan 2008 09:30:06 GMT

HI ,

how to write select statement using for all entries between more that 3 or more data base tables ,

can any one pls give some ex,

THX

Re: SELECT FOR ALL ENTRIES

Former Member — Wed, 30 Jan 2008 09:37:23 GMT

FOR ALL ENTRIES usually used to select data from DB based on the values in an internaltable.

for eg:

1)select * from sflight into corresponding fields of table it_sflight.

2)select * from sbook into corresponding fields of table it_sbook

FOR ALL ENTRIES in it_sflight

where carrid = it_sflight-carrid.

Re: SELECT FOR ALL ENTRIES

Former Member — Wed, 30 Jan 2008 09:37:47 GMT

SEN,

To select the data from 3 or more tables you have to use inner joins.

For all entries will use only for internal tables.

EX:

You have internal table ITAB1 with some data.

Based on internal table you have to get the data from 3 or more database tables.

In this situation

use innerjoins in select statement for 3 tables

INTO TABLE ITAB2

for all entries in itab1

wher condition...

Don't forget to reward if useful...

Re: SELECT FOR ALL ENTRIES

Former Member — Wed, 30 Jan 2008 09:43:25 GMT

Hi,

Please go through this link.

http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

Regards,

Harini.S

Re: SELECT FOR ALL ENTRIES

Former Member — Wed, 30 Jan 2008 09:45:43 GMT

SELECT .. FOR ALL ENTRIES was created in OPEN SQL at a time when it was not yet possible to perform database JOINs (this was not supported for all SAP-approved DBMS). The connection between the inner and outer database tables is

created in ABAP. If you want to replace nested SELECT statements with FOR ALL ENTRIES, this only eliminates the nesting and its disadvantages partially. In general,you bundle the inner SELECT statements into packages, for example, of 5 statements each. This reduces the transfer effort required and avoids identical SQL statements. The access sequence is defined in the ABAP coding, like for nested SELECT statements. As mentioned above, if you use FOR ALL ENTRIES, you have to make sure that the driving table is not initial and does not contain any duplicate entries. As in the case of nested SELECT statements, the decision of whether an INNER JOIN or OUTER JOIN is performed is also made in the ABAP program for SELECT ..FOR ALL ENTRIES. If you want to read large data volumes, you should only use

FOR ALL ENTRIES in exceptional cases.

Re: SELECT FOR ALL ENTRIES

Former Member — Mon, 04 Feb 2008 08:33:20 GMT

Please check below code which downloads records from 3 tables into excel file.

if you want other file means you can specify other file type in the fm parameter

REPORT zstemp_qty2_ LINE-SIZE 255 .

DATA:it_vbak LIKE vbak OCCURS 0 WITH HEADER LINE.

DATA:v_file1 LIKE rlgrap-filename.

DATA:v_file2(80) TYPE c.

DATA:it_vbap LIKE vbap OCCURS 0 WITH HEADER LINE.

DATA:it_mara LIKE mara OCCURS 0 WITH HEADER LINE.

START-OF-SELECTION.

SELECT * FROM vbak INTO TABLE it_vbak UP TO 100 ROWS.

IF NOT it_vbak[] IS INITIAL.

SELECT * FROM vbap INTO TABLE it_vbap

FOR ALL ENTRIES IN it_vbak

WHERE vbeln = it_vbak-vbeln.

ENDIF.

LOOP AT it_vbap.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = it_vbap-matnr

IMPORTING

output = it_vbap-matnr.

MODIFY it_vbap TRANSPORTING matnr.CLEAR it_vbap.

ENDLOOP.

IF NOT it_vbap[] IS INITIAL.

SELECT * FROM mara INTO TABLE it_mara

FOR ALL ENTRIES IN it_vbap

WHERE matnr = it_vbap-matnr.

ENDIF.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = sy-cprog

dynpro_number = sy-dynnr

FIELD_NAME = ' '

IMPORTING

file_name = v_file1 .

v_file2 = v_file1.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

BIN_FILESIZE = ' '

CODEPAGE = ' '

filename = v_file2

filetype = 'WK1'

MODE = ' '

WK1_N_FORMAT = ' '

WK1_N_SIZE = ' '

WK1_T_FORMAT = ' '

WK1_T_SIZE = ' '

col_select = '1'

COL_SELECTMASK = ' '

NO_AUTH_CHECK = ' '

IMPORTING

FILELENGTH =

TABLES

data_tab = it_mara

FIELDNAMES =

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_WRITE_ERROR = 2

INVALID_FILESIZE = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

OTHERS = 10

IF sy-subrc 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

LOOP AT it_mara.

WRITE:/ it_mara-matnr.

ENDLOOP.