Table Expressions

Table expressions are intended to simplify reading and writing access to individual rows and their fields.
They replace READ ... INTO and READ ... ASSIGNING statements and sometimes even LOOP statements.

Table Expressions return a single table row*

*In SQL terms, they would be called “row expressions.”

Examples of Table Expressions

    SELECT *
      FROM zbc_users
      INTO TABLE @DATA(lt_user).

    DATA(ls_first_row) = lt_user[ 1 ].

    DATA(lv_firstname) = lt_user[ 1 ]-firstname.

    DATA(lv_lastname_of_Binky) = lt_user[ firstname = 'Binky' ]-lastname.

    out->write( lv_lastname_of_binky ).

As the examples show: it’s also possible to access structure components.
And if these components are tables themselves, you can even chain table expressions further...

Alternative to Exceptions – Using VALUE Constructor Expressions

If no record is found through the row specification, you can receive an empty line or an alternative line instead of an exception.
For this, wrap the table expression inside a VALUE constructor expression:

...VALUE #( <table>[<row_specification>]
             DEFAULT <alternative_table_expression> | OPTIONAL )

Alternative with DEFAULT

If the original table expression fails, the value of the alternative expression is used.
Of course, that alternative can itself contain another VALUE operator...

Initial Line with OPTIONAL

If the keyword OPTIONAL is placed after the table expression within VALUE, an initial line is returned when no row is found.

Writing Using Table Expressions

A table expression initially points to the row specified by the row selector.
That means it can also be modified.

Which rows are really changed here?

    DATA(lt_data) = get_user_table( ).

    lt_data[ user_id = 1 ]-firstname = 'Olaf'.

    DATA(ls_data) = lt_data[ 2 ].
    ls_data-firstname = 'Teddy'.

    ASSIGN lt_data[ 3 ] TO FIELD-SYMBOL(<ls_data>).
    <ls_data>-firstname = 'Anke'.

    out->write( lt_data ).

Table Functions

LINES( <table> ) returns the number of rows in the table as an expression.
That means it can be used directly as an operator anywhere.

LINE_INDEX( <table_expression> ) returns the line number of the row identified by the table expression.

    SELECT *
      FROM zbc_users
      INTO TABLE @DATA(lt_user).

    out->write( |Binky is in line { line_index( lt_user[ firstname = 'Binky' ] ) }| ).

Example of a Secondary Index

For all tasks: Is there another task with the same summary, and if so, how many?

    TYPES: BEGIN OF ts_tasks,
             task_key TYPE zbc_tasks-task_key,
             cnt      TYPE int4,
             summary  TYPE zbc_tasks-summary,
           END OF ts_tasks.

    DATA: lt_standard TYPE STANDARD TABLE OF ts_tasks DEFAULT KEY
                         WITH NON-UNIQUE SORTED KEY sumKey COMPONENTS summary.
  ...
    LOOP AT lt_standard ASSIGNING FIELD-SYMBOL(<ls_standard>).
      CLEAR cnt.
      LOOP AT lt_standard TRANSPORTING NO FIELDS
                          USING KEY sumKey
                          WHERE summary = <ls_standard>-summary.
        cnt += 1.
      ENDLOOP.
      <ls_standard>-cnt = cnt.
    ENDLOOP.

ITAB – LOOP AT ... GROUP BY – Syntax

LOOP AT <table>
        <line>
        [<condition>]
        GROUP BY <grouping_key>
        [<sorting>]
        [WITHOUT MEMBERS]
        [<grouping_result>]
   ...
   LOOP AT GROUP <grouping_result>
        <result>
        [WHERE <log_expr>]
        [GROUP BY ... ]

    ENDLOOP
   ...
ENDLOOP.

Example: Only Grouping

In the simplest case, grouping is done without looping over the groups.
This shows the nature of the outer loop:

    LOOP AT lt_user INTO DATA(ls_user)
                    GROUP BY ls_user-gender.
        out->write( ls_user ).
    ENDLOOP.

Result:

Structure
CLIENT  USER_ID     FIRSTNAME  LASTNAME  EMAIL               GENDER  DATE_OF_BIRTH
100     0000000001  Emmye      Alywen    ealywen0@alexa.com  F       1976-05-16
Structure
CLIENT  USER_ID     FIRSTNAME  LASTNAME      EMAIL                       GENDER  DATE_OF_BIRTH
100     0000000003  Stavro     Antonopoulos  santonopoulos2@histats.com  M       2000-09-16

LS_USER always contains a full row of the table.
Since the data has exactly two values for the field GENDER, two groups are created, and one row from each is shown in the output.

LOOP Over the Group Structure

In the loop over group data, the group structure represents the respective subset table.

    LOOP AT lt_user INTO DATA(ls_user)
                    GROUP BY ls_user-gender
                    INTO DATA(ls_grouping).

      out->write( |At begin of gender { ls_grouping } | ).

      LOOP AT GROUP ls_grouping INTO DATA(ls_group).
        out->write( ls_group-firstname ).
      ENDLOOP.

      out->write( |At end of gender { ls_grouping } | ).
    ENDLOOP.

At begin of gender F
   Emmye
   Yolande
   Heidi
   Constantina
   Martica
At end of gender F
At begin of gender M
   Stavro
   Peyter
   Gonzalo
   Thibaut
At end of gender M