SELECT - INTO
Short Reference
ABAP Syntax ... { INTO
{ {[CORRESPONDING FIELDS OF] wa}|(dobj1, dobj2, ...)} }
| { INTO|APPENDING
[CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n] }
[ creating ] ... .
ABAP_ALTERNATIVES:
1 ... INTO [CORRESPONDING FIELDS OF] wa
2 ... INTO (dobj1, dobj2, ... )
3
... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE
SIZE n]
What does it do? target specifies to which data objects the
result set of a SELECT or
FETCH statement is assigned. Either a single
work area wa or a list of data objects dobj1, dobj2, ...
can be specified after INTO , or an internal table itab can
be specified after INTO or APPENDING .
If a LOB of the result set is associated
with a LOB handle in the target
, it may be necessary to use creating
to specify whether a data stream or a
locator is being used.
Latest notes: When specifying the data objects, there are no dynamic
variants that correspond to the other additions. Instead, dynamically
created data objects can be used (see the example for
CREATE DATA ).
Whether data should better be read into an internal table or a work
area depends on the type of further processing: If data is required only
once in a program, it should be imported into a work area, row by row,
by a SELECT . Reading data into an internal table requires more
memory space (without the disadvantage) because of a considerably higher
reading speed. If, on the other hand, data is required many times in a
program, it should be read into an internal table. The disadvantage of
the increased memory requirement is more than compensated for here by
the advantage of a once-only selection.
If data is to be imported into an internal table, it is better to
import it once into an internal table than to put it, row by row, into a
work area and then finally add it to an internal table using
APPEND .
The variants with the addition INTO CORRESPONDING FIELDS
require a greater runtime than the corresponding variants without
CORRESPONDING FIELDS , however the runtime is less dependent on the
volume.
The variants with the addition INTO CORRESPONDING FIELDS run
the risk of having target areas with more components than are actually
filled. This should only be the case if the components that are not
filled are then filled in the program. Otherwise appropriately sized
work areas should be used to avoid a large amount of memory being filled
by initial values (especially in internal tables).
The statement SELECT also has an obsolete
short form , in which INTO or
APPENDING can be omitted.
Reference variables for LOB handle s or LOB handle
structures cannot currently be specified after the statement FETCH
.
ABAP_ALTERNATIVE_1 ... INTO [CORRESPONDING FIELDS OF] wa
What does it do? For wa , a data object can be specified that must
meet certain prerequisites without
CORRESPONDING FIELDS OF being specified. If the result set consists
of a single row, this row is assigned to wa . If the result set
has multiple rows, SELECT must be followed by an ENDSELECT
statement; the result set is assigned to the work area wa
row-by-row and can be evaluated in the loop. After ENDSELECT , the
work area wa contains the row that was assigned last. If used in
the FETCH statement, a row is extracted at the
current cursor position. If the result is empty, the work area remains
unchanged.
The rows of the result set are assigned as follows, based on the
columns specified after SELECT :
* specified without the addition CORRESPONDING FIELDS
If all columns are read using * and CORRESPONDING FIELDS
is not specified, SELECT behaves as follows:
When specifying a work area that does not contain any reference
variables for LOB handle s , the row
is assigned to the result set left-aligned and unconverted in accordance
with the structure of the result set. Unaffected parts of wa
retain their previous content. To be able to access the components of
the result set according to type, the work area wa must be
structured like the result set.
If a LOB handle structure
is specified, it must be constructed (in accordance with the
prerequisites ) exactly like the structure of the
database table. The contents of the columns of the result set that are
not assigned to any LOB handle
components are directly assigned to the corresponding components
of the work area. A LOB handle
is created for each LOB handle component .
All other combinations
If the result set consists of a single column specified explicitly
after SELECT or a single
aggregate expression , then wa can be an elementary data object
or a structure. If the result set consists of multiple columns, it must
be a structure and the following rules apply:
If the CORRESPONDING FIELDS addition is not specified, wa
must contain enough components and the contents of the columns are
assigned to the components of wa from left to right in the order
specified after SELECT .
If the CORRESPONDING FIELDS addition is specified, only those
contents of columns for which there are identically named components in
wa are assigned to them. This respects the alternative column
names. Columns and aggregate expressions that appear multiple times can
only be assigned using alternative column names, if CORRESPONDING
FIELDS is specified. If a column name appears multiple times and no
alternative column name was specified, the last column listed is assig
ned.
Assignment rules apply to the
individual assignments. If a LOB of a
reference variable for LOB handle
is assigned, a LOB handle is
created .
Latest notes: The addition CORRESPONDING FIELDS ignores names
which were only defined using the addition AS name of the
statement INCLUDE or when integrating
the structures in ABAP Dictionary. However, components that were renamed
using the RENAMING WITH SUFFIX addition of the INCLUDE
statement or similarly in ABAP Dictionary are not ignored.
The addition CORRESPONDING FIELDS determines the assignment of
the fields of the same name once for each SELECT statement at
runtime.
Example ABAP Coding In this example, four columns of the result set are read
into four correspondingly named components of a work area.
DATA wa TYPE spfli.
SELECT carrid connid cityfrom cityto
FROM spfli
INTO CORRESPONDING FIELDS OF wa.
cl_demo_output=>write_data( wa ).
ENDSELECT.
cl_demo_output=>display( ).
ABAP_ALTERNATIVE_2 ... INTO (dobj1, dobj2, ... )
What does it do? If the result set consists of
multiple columns or aggregate
expressions specified explicitly after SELECT , a list of
elementary data objects dobj1, dobj2, ... (in parentheses and
separated by commas) can be specified after INTO . The same number
of elementary data objects dobj must be specified as there are
columns in the result set. The contents of the columns in the result set
are assigned to the data objects from left to right, according to the
order specified after SELECT .
Assignment rules apply to the individual assignments. If a
LOB of a reference variable for
LOB handle is assigned, a
LOB handle is created . If the
result set is empty, the data objects remain unchanged.
If the result set consists of one row, the columns are assigned from
that row. If the result set contains multiple rows, SELECT must
be followed by an ENDSELECT statement; the columns of the result
set are assigned to the data objects row-by-row and they can be
evaluated in a loop. If used in the FETCH
statement, the columns of the row are extracted at the current cursor
position.
Example ABAP Coding In this example, four columns of the result set are read
into four individually specified columns of a structure. Unlike in the
previous example, the runtime environment does not compare names here.
DATA wa TYPE spfli.
SELECT carrid connid cityfrom cityto
FROM spfli
INTO (wa-carrid, wa-connid, wa-cityfrom, wa-cityto).
cl_demo_output=>write_data( wa ).
ENDSELECT.
cl_demo_output=>display( ).
ABAP_ALTERNATIVE_3 ... INTO|APPENDING [CORRESPONDING FIELDS OF]
TABLE itab [PACKAGE SIZE n]
ABAP_ADDITION:
... PACKAGE SIZE n
What does it do? If the result set consists of multiple rows, an internal
table itab of any table type can be specified after INTO
or APPENDING . The row type of the internal table must meet the
prerequisites .
The result set is inserted into the internal table itab
row-by-row; a sorting process is executed in the case of a
sorted table . If INTO is used,
the internal table is initialized. Previous rows remain intact if
APPENDING is used.
Before any assignment of a row of the result set, an initial row of
the internal table itab is created and the row of the result set
is assigned to this row. When assigning a row of the result set to a row
of the internal table with or without CORRESPONDING FIELDS , the
same rules apply as when assigning to an individual work area wa
(see above) with the exception that when inserting into internal tables,
LOB handle s can be
created as
locators but not as read streams
.
If the PACKAGE SIZE addition is not used, all rows of the
result set are inserted in the internal table itab and the
ENDSELECT statement must not be specified after SELECT .
If the result set is empty, the internal table is initialized when
INTO is used, and remains unchanged when APPENDING is
used.
Latest notes: When specifying an internal table with a unique
primary or
secondary table key , an exception
is raised that cannot be handled if an attempt is made to create a
duplicate entry.
If the internal table contains more columns than required when using
the addition CORRESPONDING FIELDS , then generally too much memory
is being used by initial fields and a warning is displayed by the syntax
check. In cases where the columns are needed, for example when they are
filled with derived values by the program, a pragma
can be used to suppress the warning.
Since the maximum number of data streams
that can be assigned to an Open SQL statement is limited to
16, it does not make any sense to create read streams when importing
internal tables.
ABAP_ADDITION ... PACKAGE SIZE n
What does it do? If the addition PACKAGE SIZE is specified, all
rows of the result set for SELECT are processed in a loop, which
must be closed with ENDSELECT . They are inserted in packages of
n rows in the internal table itab . n expects a data
object of type i that contains the number of rows. If the value
of n is less than 0, an exception is raised that cannot be
handled. If n is equal to 0, all rows of the result set are
inserted in the internal table itab . If used in the
FETCH statement, n rows are extracted
from the current cursor position.
If INTO is used, the internal table is initialized before each
insertion and, in the SELECT loop, it only contains the rows of
the current package. If APPENDING is used, a further package is
added to the existing rows of the internal table for each SELECT
loop or for each extraction using FETCH .
After ENDSELECT , the content of itab is not defined if
INTO is used. That is, the table can either contain the rows of
the last package or it can be initial. If APPENDING is used, the
content of itab retains the state of the last loop pass.
Latest notes: The addition PACKAGE SIZE can be used after
INTO to limit the amount of data read in one go. Otherwise a runtime
error can occur when reading a data set that is too large into an i
nternal table so that its maximum size
is exceeded. PACKAGE SIZE cannot prevent this runtime error after
APPENDING .
If the addition PACKAGE SIZE is specified with the addition
FOR ALL ENTRIES , all selected rows
are read initially into an internal system table and the packages are
only created when they are passed from the system table to the actual
target table. The addition FOR ALL ENTRIES thereby negates the
effect of PACKAGE SIZE in preventing memory overflow.
The addition PACKAGE SIZE does not influence the size of the
packages (configured in the profile
parameters) used to transport data between the database server and the
application server.
Example ABAP Coding In this example, all columns of a result set are read
into an internal table, the row type of which is a nested structure with
the same structure as the result set. Note that in practice, the column
carrid exists twice in the result set with the same content and,
after the assignment, this content is stored redundantly in the columns
struc1-carrid and struc2-carrid of the internal table.
DATA: BEGIN OF wa,
struc1 TYPE scarr,
struc2 TYPE spfli,
END OF wa.
DATA itab LIKE SORTED TABLE OF wa
WITH UNIQUE KEY table_line.
DATA: BEGIN OF output_wa,
carrid LIKE wa-struc1-carrid,
carrname LIKE wa-struc1-carrname,
connid LIKE wa-struc2-connid,
END OF output_wa,
output LIKE TABLE OF output_wa WITH EMPTY KEY.
SELECT *
FROM scarr
INNER JOIN spfli ON scarr~carrid = spfli~carrid
INTO TABLE itab.
LOOP AT itab INTO wa.
output_wa-carrid = wa-struc1-carrid.
output_wa-carrname = wa-struc1-carrname.
output_wa-connid = wa-struc2-connid.
APPEND output_wa TO output.
ENDLOOP.
cl_demo_output=>display_data( output ).
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved