ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → SELECT clauses → SELECT - FROM → SELECT - FROM data_source

SELECT - FROM @itab

Quick Reference

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

Syntax


... @itab

Effect

Specifies an internal table itab as a host variable, whose name must be prefixed with the @ character, as a data source of a query. The SELECT statement handles the internal table of the AS ABAP like a database table on the database. The ABAP types of the columns in the internal table are mapped to suitable built-in data types in ABAP Dictionary. If a column is declared with reference to a type in ABAP Dictionary, this type is used directly.

There are two different cases here:

The data in the internal table is not required on the database.

In this case, the data of the internal table is accessed on the AS ABAP and the table is handled like a table in the table buffer. This is possible for all database platforms.

Data in the internal table is required on the database.

In this case, the data must be passed to temporary tables in the database before the query is actually executed. Here, only those columns are transported that need to be accessed. This option is not supported by all databases. If it is known statically that data is required on the database, a syntax check warning occurs that can be hidden by the pragma ##itab_db_select.

The data in the internal table must not be passed to the database (or the statement can be executed on the AS ABAP) in precisely those cases where the SELECT statement meets the same conditions as when the table buffer is accessed in table buffering.

The following conditions apply:

Only one internal table can be specified in an ABAP SQL statement.
An alias name must be assigned to the internal table using AS.
The row type of the internal table can be elementary or structured.

An elementary row type represents a column that can be addressed in the other clauses of the query using the name table_line or an alias name defined with AS in the SELECT list.

A structured row type cannot contain any substructures.

No deep row types are allowed. An elementary row type cannot be a string or a reference type and a structured row type cannot contain any strings, reference types, or internal tables as components, with the following exception: An elementary row type or a component can have the type string if this is declared using a reference to the built-in dictionary type SSTRING.
If the addition ORDER BY PRIMARY KEY is used, the internal table must have a primary table key.
The key fields of a primary table key in the internal table must be contiguous columns that appear in the same order at the start of the row type.
If the FROM clause is specified statically, the internal table cannot be a generically typed formal parameter or a generically typed field symbol. Objects like this can only be specified in a dynamic FROM clause and must represent a matching internal table at runtime.
The internal table cannot contain any columns declared with reference to the obsolete dictionary types DF16_SCL and DF34_SCL.
When an internal table with elementary row type is accessed in a common table expression after WITH, the SELECT list cannot be * or contain data_source~*.
The internal table should have an explicitly defined primary key (which can be empty). Generic primary keys and standard keys are not evaluated when read and a syntax check warning occurs.

The data in the internal table is handled like data on the database even if it is not transported to the database:

Data with the type string declared using a reference to the built-in dictionary type SSTRING is handled like text fields with fixed lengths in which trailing blanks are ignored.
SQL expressions are evaluated in the same way as on the database, for example:

Evaluations of the functions DIV and MOD are not the same as calculations with the identically named ABAP operators.

Any null values produced as results of nested expressions (for example in the COALESCE function or in comparisons) are handled as null values.

The result of a relational expression with operands that contain null values is unknown, except in the case of the expression IS [NOT] NULL.

The internal table itab is always regarded as a cross-client table. The data type of the first column is never regarded as a client column, regardless of its data type.

Notes

The data in internal tables should only be transported to the database system if it is actually needed there. In this case, the pragma ##itab_db_select is used to hide the corresponding syntax check warning.
The use of an internal table as a data source of SELECT is mainly applicable to joins with data sources from the database. In this case, the data of the internal table is transported to the database.
An internal table as a data source of SELECT is also, however, a potential alternative to the statements READ TABLE and LOOP AT and hence makes ABAP SQL syntax possible that cannot otherwise be used.

If this solution is used, it should be ensured that the data is evaluated on the AS ABAP and that no data is transported to the database system to be evaluated. A corresponding syntax check warning is displayed as a reminder.

The use of SELECT to access an internal table is usually slower than the statements for internal tables and should only be used in cases not covered by these statements.

The fact that, when accessed on the AS ABAP without data transport, the data in the internal table is handled like a table in the table buffer does not mean that the data is actually stored here. The appropriate restrictions do, however, apply to the SELECT statement.
In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports access to internal tables. This requires the constant ITABS_IN_FROM_CLAUSE of this class to be passed to the method in an internal table.
The pragma ##db_feature_mode[itabs_in_from_clause] can be used to hide a syntax warning from the extended program check indicating that data in the internal table is being passed to an invalid database. If this is done at runtime, a handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.
In internal tables with elementary row types, the name of the pseudo component table_line cannot be the name of a structure in ABAP. An inline declaration using @DATA(...) can only be made in the INTO clause if the column table_line of the results set is assigned an alias name using AS.
If an internal table is used as a data source, the syntax check is performed in strict mode from Release 7.52.

Example

Uses an internal table as the data source of an inner join of a SELECT statement. The data is transported to the database for the join and the statement can only be executed in database systems where joins are supported.

DATA itab TYPE HASHED TABLE OF scarr 
          WITH UNIQUE KEY mandt carrid. 

IF NOT cl_abap_dbfeatures=>use_features( 
         EXPORTING 
           requested_features = 
            VALUE #( ( cl_abap_dbfeatures=>itabs_in_from_clause ) ) ). 
  cl_demo_output=>display( 
    `System does not support internal tables as data source` ). 
  RETURN. 
ENDIF. 

itab =  VALUE #( ( carrid = 'LH' carrname = 'L.H.' ) 
                 ( carrid = 'UA' carrname = 'U.A.' ) ). 

SELECT scarr~carrid, scarr~carrname, spfli~connid 
       FROM @itab AS scarr 
         INNER JOIN spfli ON scarr~carrid = spfli~carrid 
       INTO TABLE @DATA(result) 
       ##db_feature_mode[itabs_in_from_clause] ##itab_db_select. 

cl_demo_output=>display( result ).

Example

Uses a table of random numbers as a data source of a SELECT statement. The data is transported to the database for sorting and the statement can only be executed in database systems where sorting is supported.

TYPES: 
  BEGIN OF line, 
    id     TYPE c LENGTH 1, 
    number TYPE i, 
  END OF line. 

DATA itab TYPE HASHED TABLE OF line 
          WITH UNIQUE KEY id. 

IF NOT cl_abap_dbfeatures=>use_features( 
         EXPORTING 
           requested_features = 
             VALUE #( ( cl_abap_dbfeatures=>itabs_in_from_clause ) ) ). 
  cl_demo_output=>display( 
    `System does not support internal tables as data source` ). 
  RETURN. 
ENDIF. 

DATA(rnd) = cl_abap_random_int=>create( 
  seed = CONV i( sy-uzeit ) min = 1 max = 100 ). 
itab =  VALUE #( 
  FOR i = 1 UNTIL i > 25 
  ( id = substring( val = sy-abcde off = i len = 1 ) 
    number = rnd->get_next( ) ) ). 

SELECT * 
       FROM @itab AS numbers 
       WHERE number > 50 
       ORDER BY id 
       INTO TABLE @DATA(result) 
       ##db_feature_mode[itabs_in_from_clause] ##itab_db_select. 

cl_demo_output=>display( result ).

Example

Uses a table with an elementary data type as the data source of two SELECT statements. The data is not needed on the database and the SELECT statements meet the requirements for table buffering. This means that the statements can be executed on all database systems. In the second SELECT statement, a syntax error would occur without the alias name number, since the inline declaration after INTO cannot create an internal table with the column name table_line. If the statements were modified in a such a way that they no longer met the requirements for table buffering (for example by adding the addition DISTINCT), it would not be possible to execute them on all database systems.

DATA itab TYPE SORTED TABLE OF i WITH UNIQUE KEY table_line. 
itab =  VALUE #( ( 1 ) 
                 ( 2 ) 
                 ( 3 ) ). 

DATA result1 LIKE itab. 
SELECT table_line 
       FROM @itab AS numbers 
       INTO TABLE @result1. 
cl_demo_output=>write( result1 ). 

SELECT table_line AS number 
       FROM @itab AS numbers 
       INTO TABLE @DATA(result2). 
cl_demo_output=>display( result2 ).

Executable Examples

Continue

Internal Table as a Data Source of a SELECT Statement

Internal Table as a Data Source of the Hierarchy Generator