Skip to Content
Personal Insights

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 )

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

6 Comments
You must be Logged on to comment or reply to a post.
    • Hi Srinivasa,

      The blog you have mentioned gives us information about the new syntax.

      The blog I have written gives more detailed information –

      • different scenarios
      • Disadvantage of usages
      • When to use
      • and other alternatives.

      these are personal insight with more in details.

       

    • Hi Jelena,

       

      I am not totally agree with your views. previous blog gives alternative for all entries without much details and scenarios and their performance outcomes.

      there are so many details that is added as per personal insight.

      • The old blog wasn’t perfect either but, to be honest, there were less problems with it. It was just informing that new features are available and referencing other blogs or SAP Help.

        This blog seems to concentrate on “using internal table as a source”, which is item 3 of the old blog. “FOR ALL ENTRIES and subqueries” that are mentioned in the opening paragraph are used when selecting data from DB only. The first scenario, while demonstrating the use of “internal table as a source”concept has nothing to do with FAE since no DB is involved. In this case new syntax is sort of an alternative for LOOP AT…

        Second scenario is OK, not much different from the example from the old blog IMHO. I suspect any ABAPer with more than a month experience has no problem visualizing a result of SELECT statement. But that’s fine.

        “The select statement must be prefixed with the @ character” – this is actually incorrect. It’s the table name, not SELECT statement that is prefixed. (It’s already clear from the title, anyway.)

        “Many details” pretty much looks like ABAP documentation with worse verbiage choices.

        Blog: “Access using new syntax is relatively slower than the available alternative and should be used in cases which are not covered by them”

        ABAP Help: “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.”

        If you wanted to create a better blog you could’ve created more complex examples (e.g. 1st scenario can be easily done without new syntax, so why even use it?), offered some actual performance evaluation (e.g. ABAP Help says “slower” but by how much?), etc. Lots of opportunities.