Select From @internal table

[former_member564522]

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 )

Output



 

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

Conclusions

• 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