Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT 

SELECT - target

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { INTO 
      { {[CORRESPONDING FIELDS OF] wa}|(dobj1, dobj2, ...)} }
  | { INTO|APPENDING
         [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n] }
    [ creating ] ...  .

Alternatives

1. ... INTO [CORRESPONDING FIELDS OF] wa

2. ... INTO (dobj1, dobj2 ... )

3. ... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

Effect

target specifies which data objects the result set of a SELECT or FETCH statement is assigned to. You can either specify a single work area wa or a list of data objects dobj1, dobj2, ... after INTO, or you can specify an internal table itab after INTO or APPENDING.

If a LOB of the resulting set is connected with a LOB handle in the target, it must also be specified using creating whether you are dealing with a data stream or a locator.


Notes

  • When specifying the data objects there are no other additions relating to dynamic variants. Instead you can work with dynamically-created data objects (see the example for CREATE DATA).
  • An obsolete short form still exists for the SELECT statement in which you can omit the specification of INTO or APPENDING.
  • After the FETCH statement, you cannot enter any reference variables for LOB handles or LOB handle structures at this time (Release 7.0, EhP2).

Alternative 1

... INTO [CORRESPONDING FIELDS OF] wa

Effect

For wa, you can specify a data object that must meet certain prerequisites without CORRESPONDING FIELDS OF being specified. If the result set consists of a single line, this line is assigned to wa. If the result set has multiple lines, SELECT must be followed by an ENDSELECT statement; the result set is assigned to the work area wa line-by-line and can be evaluated in the loop. After ENDSELECT, the work area wa contains the line that was assigned last. If used in the FETCH statement, a line is extracted at the current cursor position. If the result is empty, the work area remains unchanged.

The lines of the result set are assigned as follows, based on the column specification after SELECT:

Specifying * without the CORRESPONDING FIELDS addition

If all columns are read with * and CORRESPONDING FIELDS is not specified, SELECT behaves as follows:

  • When specifying a work area that does not contain any reference variables for LOB handles, the row is assigned to the result set left-aligned and unconverted according to the structure of the result set. Unaffected parts of the wa contain their previous content. To be able to access the components of the result set according to their type, the work area wa must be structured like the result set.
  • When specifying LOB handle structures this must be constructed according to 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, 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 it; alternative column names are taken into account. 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 assigned.

The assignment rules apply to the individual assignments. If a LOB of a reference variable for LOB handles is assigned, a LOB handle is created.


Notes

  • With the addition CORRESPONDING FIELDS, names which were only defined with the addition AS name of the statement INCLUDE or when integrating the structures in the ABAP Dictionary, are not taken into account. However one with the addition RENAMING WITH SUFFIX of the statement INCLUDE or renamed in the same way in the ABAP Dictionary is taken into account.
  • With the addition CORRESPONDING FIELDS, the assignment of the fields of the same name is determined at runtime once for each SELECT statement.

Example

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. 
  WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto. 
ENDSELECT. 

Alternative 2

... INTO (dobj1, dobj2, ... )

Effect

If the result set consists of several columns or aggregate expressions specified explicitly after SELECT, you can specify a list of elementary data objects dobj1, dobj2, ... (in brackets and separated by commas) after INTO. You must specify the same number of data objects dobj 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. The assignment rules apply to the individual assignments. If a LOB of a reference variable for LOB handles 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 line, the columns are assigned from that line. If the result set contains multiple lines, SELECT must be followed by an ENDSELECT statement; the columns of the result set are assigned to the data objects line-by-line and they can be evaluated in a loop. If used in the FETCH statement, the columns of the line are extracted at the current cursor position.


Example

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). 
  WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto. 
ENDSELECT. 

Alternative 3

... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

Addition

... PACKAGE SIZE n

Effect

If the result set consists of multiple lines, 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 line-by-line; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized. Previous lines remain intact if APPENDING is used.

Before any assignment of a line of the result set, an initial row of the internal table itab is created and the line of the result set is assigned to this row. When assigning a line 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 handles can be created as locators but not as read streams.

If the PACKAGE SIZE addition is not used, all lines 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.


Notes

  • When specifying an internal table with a unique primary or secondary table key, an exception occurs that cannot be handled if an attempt is made to create a duplicate entry.
  • If the internal table contains more columns than needed when the addition CORRESPONDINGFIELDS is used, then too much memory is generally being used by initial fields, and the syntax check raises a warning. In cases where the columns are needed, for example when they are filled with values in the program, the warning can be skipped by using a pragma.
  • 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.

Addition

... PACKAGE SIZE n

Effect

If you specify the PACKAGE SIZE addition, all lines of the result set for SELECT are processed in a loop, which must be closed with ENDSELECT. The lines are inserted in the internal table itab in packages of n lines. n must be a type i data object that contains the number of lines. If the value of n is less than 0, an exception that cannot be handled occurs. If n is equal to 0, all lines of the result set are inserted in the internal table itab. If used in the FETCH statement, n lines 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 lines 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 lines 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.


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 importing a data set that is too large to an internal table so that its maximum size is exceeded. After APPENDING, PACKAGE SIZE cannot prevent this runtime error.
  • If the addition PACKAGE SIZE is specified together with FOR ALL ENTRIES, all rows selected are first imported to an internal system table. The packages are only formed during the transfer from the system table to the actual target table. In this way, the FOR ALL ENTRIES addition overrides the effect of PACKAGE SIZE to prevent memory overflows.
  • The PACKAGE SIZE addition does not affect the size (configured using profile parameters) of the packages in which data is transported between database servers and application servers.

Example

In this example, all columns of the result set are imported to an internal table whose row type is a nested structure with the same construction 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. 

SELECT * 
       FROM scarr 
         INNER JOIN spfli ON scarr~carrid = spfli~carrid 
       INTO TABLE itab. 

LOOP AT itab INTO wa. 
  WRITE: / wa-struc1-carrid, 
           wa-struc1-carrname, 
           wa-struc2-connid. 
ENDLOOP. 

Continue

SELECT - creating

SELECT - Assignment Rules for Individual Columns

SELECT - LOB handles