SELECT - FROM @itab
7.31 | 7.40 | 7.54
Specifies an internal table
itab as a
host variable, whose name must be prefixed with the
@ character, as a
data source of a
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.
Data in the internal table is required on the database.
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
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_lineor an alias name defined with
- 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
stringif this is declared using a reference to the built-in dictionary type SSTRING.
If the addition
ORDER BY PRIMARY KEYis 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.
FROMclause 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
FROMclause 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
SELECTlist cannot be
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
stringdeclared 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
MODare not the same as calculations with the identically named ABAP operators.
- Any null values produced
as results of nested expressions (for example in the
COALESCEfunction or in comparisons) are handled as null values.
- The result of a relational expression with operands that contain
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.
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_selectis used to hide the corresponding syntax check warning.
The use of an internal table as a data source of
SELECTis 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
SELECTis also, however, a potential alternative to the statements
LOOP ATand 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
SELECTto 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
- 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.
##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
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_lineof the results set is assigned an alias name using
If an internal table is used as a data source, the syntax check is performed in strict mode from Release 7.52.
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 ).
Uses a table of random numbers as a data source of a
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 ).
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
INTO cannot create an internal table with the column name
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 ).
Internal Table as a Data Source of a
Internal Table as a Data Source of the Hierarchy Generator