ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → SELECT clauses → SELECT - INTO, APPENDING

SELECT - INTO target

Quick Reference

Other versions: ~~7.31~~ | ~~7.40~~ | 7.54

Syntax


... {     @dobj       } 

  | {     @DATA(dobj) } 

  | { NEW @dref       } 
  | { NEW @DATA(dref) } ...

Alternatives

1. ... @dobj

2. ... @DATA(dobj)

3. ... NEW @dref
4. ... NEW @DATA(dref)

Effect

Specifies a target area of the INTO clause of an ABAP SQL query. The possible target areas of the INTO clause are as follows:

Elementary data objects elem1, elem2, ... in a comma-separated list.
Individual work areas, wa.
Internal tables itab after TABLE.

Each target area can be specified as follows:

If the addition NEW is not used, dobj specifies the data object directly to which the data from the results set is written. The target area is one of the following:

A previously declared host variable @dobj

A host variable dobj declared inline using @DATA

The addition NEW is used to create an anonymous data object to which data from the results set is written and to which a data reference variable dref points. The data reference variable is one of the following:

A previously declared host variable @dref

A host variable dref declared inline using @DATA

All alternatives can be combined in a comma-separated list for elementary data objects as target areas. One of these alternatives must be selected when a single work area or an internal table is specified.

Note

In the case of the variant NEW @DATA(dref), the SELECT list, the FROM clause, and any indicators can be specified dynamically. This is the only way of combining a dynamically defined results set with inline declarations. The same applies to inline declarations after FETCH.

Alternative 1

... @dobj

Effect

Specifies a previously declared host variable @dobj as a target area of the INTO clause. The data in the results set is written directly to the host variable dobj. Depending on the operand position, the host variable can be one of the following:

In the comma-separated list elem1, elem2, ..., an elementary data object that matches the type of the associated column of the results set or whose data type can be converted to from the type of the associated column of the results set.
As a single work area wa, a data object that meets the prerequisites.
As an internal table, any table category whose row structure meets the prerequisites.

Writes to the host variable are made as described in the INTO clause.

Example

Uses different existing host variables as target areas of SELECT statements.

DATA: 
  carrid   TYPE scarr-carrid, 
  carrname TYPE scarr-carrname, 
  url      TYPE scarr-url, 
  carrier  TYPE scarr, 
  carriers TYPE SORTED TABLE OF scarr WITH UNIQUE KEY carrid. 

SELECT SINGLE carrid, carrname, url 
       FROM scarr 
       WHERE carrid ='UA' 
       INTO (@carrid, @carrname, @url). 

SELECT SINGLE * 
       FROM scarr 
       WHERE carrid ='LH' 
       INTO @carrier. 

SELECT * 
       FROM scarr 
       INTO TABLE @carriers.

Alternative 2

... @DATA(dobj)

Effect

Specifies a host variable @dobj declared inline as a target area of the INTO clause. The data in the results set is written directly to the host variable dobj. The inline declaration is made using the declaration operator DATA, which must be prefixed with the escape character @ here. Depending on the operand position, the host variable is declared as follows:

An elementary variable is declared in the comma-separated list elem1, elem2, ... The data type of the variables is constructed as follows from the associated column of the results set:

The ABAP type assigned the dictionary type of a column of a data source is used for this column.

The ABAP type assigned the result type of an SQL expression is used for this expression.

The ABAP type of a host variable is used directly for this variable specified as a single SQL expression.

A flat data object wa is declared as a single work area in INTO @DATA(wa). A standard table itab with an empty table key is declared as an internal table in INTO TABLE @DATA(itab). The data type of wa or the row type of the internal table are constructed as follows in accordance with the structure of the results set defined after SELECT and the number of data sources specified after FROM:

If the results set in the SELECT list is defined using a single specified column col_spec for which no name can be identified, the data type of wa or the row type of itab is its elementary type.

If the results set in the SELECT list is defined using a single specified column col_spec for which no name can be identified, the data type of wa or the row type of itab is a structure with a component, with its elementary type.

If the results set in the SELECT list is defined using a single data_source~* or a list of multiple specified columns col_spec, the data type of wa or the row type of itab is a structure with elementary components. The data types of the components are the elementary types of the columns in the results set in the order defined there.

If data_source~* is specified in the SELECT list together with other elements, the data type of wa or the row type of itab is a nested structure. For each data source data_source specified in this way, a substructure is generated with the name or alias name of the table or view. The data types of the components of the substructures are the elementary types of the data source in the order defined there. If the data source is a common table expression declared using WITH, the first character + of its name is ignored for the name of the substructure.

If the results set in the SELECT list is defined using *, the data type depends on the number of data sources specified after FROM:

In reads from a single data source data_source, the data type of wa or the row type of itab is the same as in a definition of the results set using a single data_source~* (see above), that is, a structure with elementary components.

In reads from multiple data sources data_source1, data_source2, ... using a join, the data type of wa or the row type of itab is the same as in a definition of the results set using data_source1~*, data_source2~*, .... (see above), that is, a structure with a substructure for each data source.

The names of the elementary components of a structure match the names of the associated columns from the results set. Any alias names defined there are respected.

The elementary data type of an elementary data object or of an elementary component of a structure is constructed (like an elementary data object in a comma-separated list) from the type of the associated column of the results set (see above)

If the addition INDICATORS is used, a substructure called null_ind is added at the end of the structure or row structure declared inline. For each column in the results set, this substructure contains a component of type x and length 1 with the same name as the column and in the same sequence. If preceding components of the structure declared inline are substructures, the substructure null_ind is also structured accordingly.

The prerequisites for an online declaration are as follows:

The results set can be specified dynamically only when combined with the addition NEW. If the addition NEW is not specified, the structure of the results set must be known statically. The SELECT list, the FROM clause, and any indicators must be specified statically.
After FETCH, an inline declaration can only be made together with the addition NEW.
The results set defined in the SELECT list cannot have multiple columns with the same name. This can be bypassed using alias names.
In a results set with multiple rows or if the addition INDICATORS is specified, every SQL expression and every aggregate expression must have an alias name.
Alias names of the SELECT list must meet the naming conventions for internal program names. More specifically, they cannot contain any minus signs, -.
If the obsolete addition CLIENT SPECIFIED is used to access a client-specific CDS entity, a name must be specified for the client column at the same time.
The addition CORRESPONDING FIELDS OF cannot be used.

Notes

Specified columns for which no name can be identified are SQL expressions and aggregate expressions without alias name.
When an inline declaration @DATA(itab) is specified after APPENDING TABLE, this addition usually works like INTO TABLE and is therefore pointless in this combination.
When inline declarations are used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check.

Example

Reads individual columns of a results set into various target areas declared inline. carrname, carrid, and url are elementary data objects. wa is a structure with elementary components. itab is a standard table with an appropriate row type.

SELECT SINGLE 
       FROM scarr 
       FIELDS carrname, 
              carrid, 
              url 
       WHERE carrid = 'LH' 
       INTO (@DATA(carrname),@DATA(carrid),@DATA(url)). 

SELECT SINGLE 
       FROM scarr 
       FIELDS carrname, 
              carrid, 
              url 
       WHERE carrid = 'LH' 
       INTO @DATA(wa). 

SELECT FROM scarr 
       FIELDS carrname, 
              carrid, 
              url 
       INTO TABLE @DATA(itab). 

ASSERT wa-carrname = carrname. 
ASSERT wa-carrid   = carrid. 
ASSERT wa-url      = url. 

cl_demo_output=>new( 
  )->write( wa 
  )->write( itab )->display( ).

Example

In this example, all columns of a results set are read into an inner join in an internal table, the row type of which is declared as a nested structure with the same structure as the results set. The first component of the nested structure is called SCARR and includes all columns of this database table. The second component of the nested structure is called SPFLI and includes all columns of this database table. The content of the columns MANDT and CARRID in both tables is redundant. For the output, the internal table with a nested row type is converted to an output table without substructures.

TYPES BEGIN OF output_wa. 
INCLUDE TYPE scarr AS scarr RENAMING WITH SUFFIX _scarr. 
INCLUDE TYPE spfli AS spfli RENAMING WITH SUFFIX _spfli. 
TYPES END OF output_wa. 
TYPES output TYPE SORTED TABLE OF output_wa 
             WITH NON-UNIQUE KEY carrid_scarr connid_spfli. 

SELECT * 
       FROM scarr 
         INNER JOIN spfli ON scarr~carrid = spfli~carrid 
       INTO TABLE @DATA(itab). 

cl_demo_output=>display( CONV output( itab ) ).

Executable Example

Inline Declarations

Alternative 3

... NEW @dref

Effect

The addition NEW creates an anonymous data object as the target area of the INTO clause. dref expects a previously declared data reference variable that points to the data object after the object is created. The data of the results set is written to the new anonymous data object. The data reference variable dref can be typed in full or generically.

If the data reference variable dref is typed in full, its static type must meet the same rules as apply to a directly specified data object @dobj:

In a comma-separated list (..., dref, ...), it must be an elementary data type to which the type of the associated column of the results set can be converted.

If a single work area wa, the type must meet the prerequisites for work areas.

If an internal table, it must be a table type with any table category whose row structure meets the prerequisites.

The anonymous data object is created with the type of the data reference variable. The static type of the data reference variable matches the dynamic type. Writes to the anonymous data object are made as described in the INTO clause.

If the data reference variable dref is typed generically with data, the data type of the anonymous data object or the dynamic type of dref is constructed (as specified by the operand position) in exactly the same way as the data type of dobj in the inline declaration @DATA(dobj) described above. This means the following:

In a comma-separated list (..., dref, ...), an elementary data object is created whose type is determined by the type of the associated column of the results set.

If a single work area in INTO NEW @dref, a flat data object is created. A standard table with an empty table key is declared as an internal table in INTO TABLE NEW @dref. The type of the new data object or the row type of the internal table is constructed using @DATA(dobj), as in an inline declaration.

The anonymous data object is created with this type. The static type of the data reference variable is more general than the dynamic type. Writes to the anonymous data object are made as described in the INTO clause. The columns of the results set defined in the SELECT list must have unique names.

Unlike in inline declarations with @DATA(dobj), the type of the anonymous data object can also be created at runtime. This means that the addition NEW can also be specified in the following cases:

If the structure of the results set cannot be know statically due to the use of dynamic tokens.
After FETCH.

If possible, the type check is made as part of the syntax check. If not, it is made at runtime. If an error is not detected until runtime, an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS is raised.

The following restrictions apply:

The addition NEW can only be specified after INTO and cannot be specified after APPENDING.
If multiple FETCH statements access a database cursor opened using OPEN CURSOR, a data reference variable used after NEW can be typed generically only if the first of these FETCH statements has the addition NEW with a generically typed data reference variable in the appropriate operand position.

Notes

The addition NEW works in a similar way to creating an anonymous data object with the instance operator NEW directly in front of the ABAP SQL statement and using the dereferenced reference variable as a target area. The addition NEW has the advantage that the data type of the anonymous data object is constructed in a suitable way and, more specifically, this construction works for dynamic tokens too.
If the addition NEW is used, the syntax check is made in strict mode from Release 7.54.

Example

Uses a generically typed and a fully typed data reference variable after NEW. The anonymous data objects created here both have the same type and the same content. The third SELECT statement writes to an anonymous data object created previously using the instance operator NEW and hence demonstrates approximately how the NEW addition works.

TYPES scarr_tab TYPE STANDARD TABLE OF scarr WITH EMPTY KEY. 

DATA dref_data TYPE REF TO data. 
SELECT FROM scarr 
       FIELDS * 
       INTO TABLE NEW @dref_data. 
ASSIGN dref_data->* TO FIELD-SYMBOL(<fs>). 

DATA dref_scarr_tab TYPE REF TO scarr_tab. 
SELECT FROM scarr 
       FIELDS * 
       INTO TABLE NEW @dref_scarr_tab. 

ASSERT <fs> = dref_scarr_tab->*. 

cl_demo_output=>display( <fs> ). 

dref_scarr_tab = NEW #( ). 
SELECT FROM scarr 
       FIELDS * 
       INTO TABLE @dref_scarr_tab->*. 
ASSERT <fs> = dref_scarr_tab->*.

Example

In this example, three anonymous data object of the type string are created. The columns of the results set are converted to string.

DATA: 
  dref1 TYPE REF TO string, 
  dref2 TYPE REF TO string, 
  dref3 TYPE REF TO string. 

SELECT SINGLE carrid, carrname, url 
       FROM scarr 
       WHERE carrid = 'UA' 
       INTO (NEW @dref1, NEW @dref2, NEW @dref3). 

cl_demo_output=>display( |{ dref1->* }, { dref2->* }, { dref3->* }| ).

Example

This example compares an INTO with an inline declaration of the target area (see above) with an INTO clause with the addition NEW. In both cases, the same data type is constructed, namely an internal table with a nested row structure. If individual components are not accessed, the exact data type does not need to be known in the program.

SELECT * 
       FROM scarr 
         INNER JOIN spfli ON scarr~carrid = spfli~carrid 
       INTO TABLE @DATA(itab). 

DATA dref TYPE REF TO data. 
SELECT * 
     FROM scarr 
       INNER JOIN spfli ON scarr~carrid = spfli~carrid 
     INTO TABLE NEW @dref. 
ASSIGN dref->* TO FIELD-SYMBOL(<fs>). 

ASSERT <fs> =  itab.

Example

As in the previous example, but after FETCH and with a dynamically specified SELECT list and the FROM clause after OPEN CURSOR. In this case, it is not possible to make a direct inline declaration of the target area in the INTO clause. It is, however, possible to use the addition NEW.

DATA(cols) = `*`. 
DATA(from) = `scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid`. 

OPEN CURSOR @DATA(dbcur) FOR 
SELECT (cols) 
     FROM (from). 

DATA dref TYPE REF TO data. 
FETCH NEXT CURSOR @dbcur 
      INTO NEW @dref. 
CLOSE CURSOR @dbcur. 

ASSIGN dref->* TO FIELD-SYMBOL(<fs>).

Example

The program DEMO_SELECT_INTO_NEW_VARIANTS demonstrates how the NEW addition is used in different variants of the INTO clause.

Alternative 4

... NEW @DATA(dref)

Effect

The addition NEW in front of an inline declaration with the declaration operator @DATA works like the previous variant, however the data reference variable dref that points to the new anonymous data object is declared inline. The static type of the data reference variable dref is defined as follows:

If the data type of the new anonymous data object is known statically, dref is also typed with this type.
If the data type of the new anonymous data object cannot be known until runtime, dref is typed with the generic type data. This is the case when the structure of the results set is not known statically due to the use of dynamic tokens and after FETCH.

The columns of the results set defined in the SELECT list must have unique names. If multiple FETCH statements access a database cursor opened usingOPEN CURSOR, NEW and an inline declaration can be used only if this is also the case in the first of these FETCH statements in the appropriate operand position.

Note

The addition NEW can also be used to make an inline declaration together with dynamic tokens and after FETCH. The data reference variable that points to the target area created as an anonymous data object is declared, however, and not the direct target area.

Example

Creates anonymous data objects as target areas together with inline declarations of the data reference variables. After SELECT with static tokens, the data reference variable dref_scarr has the static type of an internal table with the row type SCARR from ABAP Dictionary. dref_data,, on the other hand is typed generically with data after FETCH. This demonstrated by using RTTI methods.

SELECT * 
       FROM scarr 
       INTO TABLE NEW @DATA(dref_scarr). 

OPEN CURSOR @DATA(dbcur) FOR 
  SELECT * 
         FROM scarr. 
FETCH NEXT CURSOR @dbcur 
     INTO TABLE NEW @DATA(dref_data). 
CLOSE CURSOR @dbcur. 

ASSIGN dref_data->* TO FIELD-SYMBOL(<fs>). 
ASSERT dref_scarr->* = <fs>. 

cl_demo_output=>new( 

  )->write( |dref_scarr: { 
            CAST cl_abap_tabledescr( 
            CAST cl_abap_refdescr( 
              cl_abap_typedescr=>describe_by_data( dref_scarr ) 
               )->get_referenced_type( 
               ) )->get_table_line_type( 
               )->absolute_name } | 
  )->write( |dref_data:  { 
            CAST cl_abap_refdescr( 
              cl_abap_typedescr=>describe_by_data( dref_data ) 
              )->get_referenced_type( 
              )->absolute_name }| 

  )->display( dref_scarr->* ).

Executable Examples

Create Structure as Target Area

Create Internal Table as Target Area

Continue

SELECT, Inline Declarations

SELECT, Create Structure as Target Area

SELECT, Create Internal Table as Target Area