Select From @internal table
We all are using subqueries or the FOR ALL ENTRIES to fulfill business requirements. Sometimes a few of them are result in long or inefficient codes.
Few of the scenarios are –
- FOR ALL ENTRIES cannot be combined with the SQL Expressions like aggregate expressions.
- GROUP BY clause is not allowed to use with FOR ALL ENTRIES statement.
ABAP NW 7.52 has come up with new syntax to select the data directly from the internal table as a data source. There is no need to use FOR ALL ENTRIES or split up into multiple select statements.
These can be achieved through new syntax SELECT FROM @ITAB
New syntax will select the data from an internal table rather than a database table as a data source.
There are 2 uses cases for the scenario
- Data in the internal table is not required in the database
In this case, 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.
In the below code, method USE_FEATURES of class CL_ABAP_DBFEATURES being used to validate if the system supports an internal table as the data source.
TYPES: BEGIN OF ts_table, id TYPE i, name TYPE char5, END OF ts_table. DATA itab TYPE SORTED TABLE OF ts_table WITH UNIQUE KEY id. itab = VALUE #( ( id = 1 name = 'one' ) ( id = 2 name = 'two') ( id = 3 name = 'three' ) ). DATA result1 LIKE itab. 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. "First Query with Where clause SELECT id , name FROM @itab AS numbers WHERE id = 2 INTO TABLE @result1. cl_demo_output=>write( result1 ). "Selection of few columns SELECT id AS number FROM @itab AS numbers INTO TABLE @DATA(result2). cl_demo_output=>display( result2 ).
- 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 required/accessed columns are transported to the database.
This option does not support all databases. A syntax check warning occurs which can be hidden by pragma ##itab_db_select , if data is required at the database.
In below code, we need total seat occupied by Airline with connection ( cityfrom -> cityto)
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. "Get total seat occupied for current year SELECT carrid ,connid , SUM( seatsocc ) AS seats_occ FROM sflight INTO TABLE @DATA(lt_sflight) WHERE fldate BETWEEN '20190101' AND @sy-datum GROUP BY carrid, connid. "Get result data with city from and city to and occupied seat. SELECT sflight~carrid, sflight~connid, spfli~cityfrom ,spfli~cityto , sflight~seats_occ FROM @lt_sflight AS sflight INNER JOIN spfli ON sflight~carrid = spfli~carrid AND sflight~connid = spfli~connid INTO TABLE @DATA(result) ##db_feature_mode[itabs_in_from_clause] ##itab_db_select. cl_demo_output=>display( result )
Following conditions are applied to the new syntax in an Internal table.
- The select statement must be prefixed with the @ character
- Only one internal table as a source can be specified in the select statement
- There must have an alias using AS Statement
- It should not have deep structures or contains type string
- It should not contain any obsolete data type
- Data in the internal table should only be transported to the database system if it actually needed there. It is mainly applicable to join conditions with other data sources from the database. There are other alternatives available like GTT or CTE if the internal table does not need any data manipulation before using in a SELECT statement
- There are other alternatives like READ TABLE and LOOP_AT to fetch from an internal table where it is not required in a data source. Access using new syntax is relatively slower than the available alternative and should be used in cases which are not covered by them
Reference – Abapselect_itab