Working with Complex Internal Tables

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Fill complex internal tables with data
  • Access the content of complex internal tables
  • Use complex internal tables in ABAP SQL

Filling Complex Internal Tables

As you already learned, the simplest way to add a new row to an internal table is the APPEND statement with a data object whose type corresponds to the row type of the internal table. This data object is sometimes referred to as work area.

For simple internal tables the work area used in APPEND can be a scalar variable, constant, or a literal. For complex internal tables, the work area has to be structured.

In the example, structured variable connection is used to fill internal table connections.

In principle, there are two ways to declare work area connection:

  • Reference the row type st_connection directly
  • Reference the row type indirectly using LIKE LINE OF <internal_table>.

Defining work areas with LIKE LINE OF has two advantages:

  • It reveals the purpose of the structured variable as work area for the internal table
  • It ensures that the work area fits to the internal table, even if the definition of the internal table changes

If you do not fill the work area before the APPEND statement, the new row of the internal table will be filled with type-specific initial values.

Hint
You get the same result with the special variant APPEND INITIAL LINE TO <internal_table>. This variant does not even require a work area.

To fill the structured work area, you can either fill the individual components or, as you can see in the example, use a VALUE #( ) expression.

As you can see in the example, you can also use a VALUE #( ) expression directly in the APPEND statement. In this case, you do not need a work area.

Note
This can have a positive effect on the overall memory consumption of your program.

There is a variant of the VALUE #( ) expression that you can assign directly to an internal table. In this variant of VALUE #( ) additional pairs of brackets are used to separate the table rows from each other.

The code example fills internal table carriers with three rows, each with a different value for carrier_id and carrier_name. As a result of this, column currency_code is not mentioned, it is filled with the type specific initial value.

Note
With the assignment above, all existing table rows are removed before the table is filled with the new rows.

To copy data between identically-named fields of two internal tables, use the CORRESPONDING operator. This works similarly to CORRESPONDING for structures: for each row of the source internal table, the system creates a new row in the target internal table and copies data between identically-named fields. Source fields for which there is no identically named field in the target are not copied. Target fields for which there is no identically named field in the source are filled with type-specific initial values.

In the example, the source internal table carriers contains three rows. Therefore, after the value assignment, the target internal table connections also contains three rows.

Fields carrier_id and carrier_name exist in both internal tables. They are copied from source to target. Field currency_code only exists in the source. It is not copied. Fields connection_id, airport_from_id, and airport_to_id exist only in the target. They are filled with initial values.

Note
If the target internal table contains data before the assignment, the system deletes it.

How To Debug Complex Internal Tables

Try It Out: Filling Complex Internal Tables

  1. As it is in the first exercise of this course, create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
        TYPES: BEGIN OF st_connection,
                 carrier_id      TYPE /dmo/carrier_id,
                 connection_id   TYPE /dmo/connection_id,
                 airport_from_id TYPE /dmo/airport_from_id,
                 airport_to_id   TYPE /dmo/airport_to_id,
                 carrier_name    TYPE /dmo/carrier_name,
               END OF st_connection.
    
        TYPES tt_connections TYPE STANDARD TABLE OF   st_connection
                                  WITH NON-UNIQUE KEY carrier_id
                                                      connection_id.
    
        DATA connections TYPE tt_connections.
    
        TYPES: BEGIN OF st_carrier,
                 carrier_id    TYPE /dmo/carrier_id,
                 carrier_name  TYPE /dmo/carrier_name,
                 currency_code TYPE /dmo/currency_code,
               END OF st_carrier.
    
        TYPES tt_carriers TYPE STANDARD TABLE OF st_carrier
                              WITH NON-UNIQUE KEY carrier_id.
    
        DATA carriers TYPE tt_carriers.
    
    * Example 1: APPEND with structured data object (work area)
    **********************************************************************
    
    *    DATA connection  TYPE st_connection.
        " Declare the work area with LIKE LINE OF
        DATA connection LIKE LINE OF connections.
    
    
    *    connection-carrier_id       = 'NN'.
    *    connection-connection_id    = '1234'.
    *    connection-airport_from_id  = 'ABC'.
    *    connection-airport_to_id    = 'XYZ'.
    *    connection-carrier_name     = 'My Airline'.
    
        " Use VALUE #( ) instead assignment to individual components
        connection = VALUE #( carrier_id       = 'NN'
                              connection_id    = '1234'
                              airport_from_id  = 'ABC'
                              airport_to_id    = 'XYZ'
                              carrier_name     = 'My Airline' ).
    
        APPEND connection TO connections.
    
        out->write(  `--------------------------------` ).
        out->write(  `Example 1: APPEND with Work Area` ).
        out->write(  connections ).
    
    * Example 2: APPEND with VALUE #( ) expression
    **********************************************************************
    
        APPEND VALUE #( carrier_id       = 'NN'
                        connection_id    = '1234'
                        airport_from_id  = 'ABC'
                        airport_to_id    = 'XYZ'
                        carrier_name     = 'My Airline'
                      )
           TO connections.
    
        out->write(  `----------------------------` ).
        out->write(  `Example 2: Append with VALUE` ).
        out->write(  connections ).
    
    * Example 3: Filling an Internal Table with Several Rows
    **********************************************************************
    
        carriers = VALUE #(  (  carrier_id = 'AA' carrier_name = 'American Airlines' )
                             (  carrier_id = 'JL' carrier_name = 'Japan Airlines'    )
                             (  carrier_id = 'SQ' carrier_name = 'Singapore Airlines')
                          ).
    
        out->write(  `-----------------------------------------` ).
        out->write(  `Example 3: Fill Internal Table with VALUE` ).
        out->write(  carriers ).
    
    * Example 4: Filling one Internal Table from Another
    **********************************************************************
    
        connections = CORRESPONDING #( carriers ).
    
        out->write(  `--------------------------------------------` ).
        out->write(  `Example 4: CORRESPONDING for Internal Tables` ).
        out->write(  data = carriers
                     name = `Source Table CARRIERS:`).
        out->write(  data = connections
                     name = `Target Table CONNECTIONS:`).
    
    Copy code
  3. Press CTRL + F3 on your keyboard to activate the class and F9 to execute the console app.
  4. Analyze the console output. Debug the program, play around with the source code to get familiar with the concepts.

Accessing the Content of Complex Internal Tables

Earlier in this course, you learned how to retrieve a single row from a simple internal table using an internal table expression. Back then we used an index access, that is, we identified the row through its position in the internal table. This index access works for complex internal tables in just the same way. For complex internal tables, however, internal table expressions with key access become important, where you identify the row through its content.

Note
Even though this is called a key access, you can use any fields for the selection, not only key fields of the internal table. If more than one row fulfills the requirement, the first row is returned, that is, the row with the lowest index.

The example reads a single row from internal table connections. The key of this internal table consists of fields, carrier_id and connection_id, but the key access uses airport_from_id and airport_to_id to identify the row. The Internal table contains two connections from airport SFO to SIN, so the first of them is returned.

Remember that the ABAP runtime raises exception CX_SY_ITAB_LINE_NOT_FOUND if none of the rows fulfills the requirement. Handle this exception in a TRY … CATCH … ENDTRY structure to avoid runtime errors

To process multiple lines of an internal table by specifying fields, you use LOOP AT <internal table> INTO <target> WHERE <condition>. The WHERE condition can contain any number of constituent expressions joined using AND and OR. Within the expressions, you can use not just the equals operator (=) but also operators >, >=, <, <=, <> and BETWEEN.

After reading the content of a table row into a work area, you sometimes want to write changes from the work area back into the internal table. One way to do this is the MODIFY TABLE statement.

This statement is a key access because the system uses the content of the key fields in the work area to identify the table row that needs to be modified. It then overwrites this table row with the contents of the work area.

In the example, the work area carrier contains value 'JL' in key field, carrier_id. Based on this value, the system identifies the second row to be updated. This row is then updated with the values from the work area.

Note

You can only change non-key fields with MODIFY TABLE. The statement does not support changes to key fields.

The MODIFY statement (without keyword TABLE!) does not distinguish between key fields and non-key fields. It overwrites the entire table row with new values from the work area. This statement is considered an index access because the row to be updated is identified by its position in the internal table. Usually, the index is specified explicitly using addition INDEX followed by an integer argument (literal, constant, variable, expression).

Note
There is also a special variant without addition INDEX. We will discuss this variant next.

In the example, the MODIFY statement uses the INDEX addition to address the first table row. In this row, all fields are overwritten with the values from the work area, even key field carrier_id.

There will often be times when you need to modify the contents of multiple rows of an internal table, or maybe even all of them. To do this, you implement a loop over the table, which places each row you need to change successively into a work area. Within the loop, you first change the contents of the work area and then write the changes back into the internal table using the MODIFY statement.

Note
If you do not write your changes back into the table, the changes will be lost when the work area is filled with the data from next row.

In the example, the loop reads all rows of internal table carriers for which field currency_code is not yet filled. This is the case for the last two rows. For each of these rows the program replaces the initial value in field currency_code with the new value 'USD'. Finally, it uses the MODIFY statement to overwrite the current row with the updated values.

Instead of specifying the index explicitly, the code example uses a short form of the MODIFY statement where the INDEX addition is missing. This short form is only allowed between LOOP … ENDLOOP. Only there the system can implicitly update the row it is currently working on.

If you use MODIFY without INDEX outside of LOOP…ENDLOOP, the system does not know which row to modify and triggers a non-catchable runtime error. To avoid such runtime errors, make sure not to ignore the related warning from the syntax check!

Try It Out: Accessing Complex Internal Tables

  1. As with the first exercise of this course, create a new global class that implements the interface, IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
      TYPES: BEGIN OF st_connection,
                 carrier_id      TYPE /dmo/carrier_id,
                 connection_id   TYPE /dmo/connection_id,
                 airport_from_id TYPE /dmo/airport_from_id,
                 airport_to_id   TYPE /dmo/airport_to_id,
                 carrier_name    TYPE /dmo/carrier_name,
               END OF st_connection.
    
        TYPES tt_connections TYPE SORTED TABLE OF   st_connection
                                  WITH NON-UNIQUE KEY carrier_id
                                                      connection_id.
    
        DATA connections TYPE tt_connections.
        DATA connection  LIKE LINE OF connections.
    
        TYPES: BEGIN OF st_carrier,
                 carrier_id    TYPE /dmo/carrier_id,
                 currency_code TYPE /dmo/currency_code,
               END OF st_carrier.
    
        DATA carriers TYPE STANDARD TABLE OF st_carrier
                           WITH NON-UNIQUE KEY carrier_id.
    
        DATA carrier LIKE LINE OF carriers.
    
    * Preparation: Fill internal tables with data
    **********************************************************************
        connections = VALUE #(  ( carrier_id      = 'JL'
                                  connection_id   = '0408'
                                  airport_from_id = 'FRA'
                                  airport_to_id   = 'NRT'
                                  carrier_name    = 'Japan Airlines'
                                )
                                ( carrier_id      = 'AA'
                                  connection_id   = '0017'
                                  airport_from_id = 'MIA'
                                  airport_to_id   = 'HAV'
                                  carrier_name    = 'American Airlines'
                                )
                                ( carrier_id      = 'SQ'
                                  connection_id   = '0001'
                                  airport_from_id = 'SFO'
                                  airport_to_id   = 'SIN'
                                  carrier_name    = 'Singapore Airlines'
                                )
                                ( carrier_id      = 'UA'
                                  connection_id   = '0078'
                                  airport_from_id = 'SFO'
                                  airport_to_id   = 'SIN'
                                  carrier_name    = 'United Airlines'
                                )
                               ).
    
        carriers = VALUE #(  (  carrier_id    = 'SQ'
                                currency_code = ' '
                             )
                             (  carrier_id    = 'JL'
                                currency_code = ' '
                             )
                             (  carrier_id    = 'AA'
                                currency_code = ' '
                             )
                             (  carrier_id    = 'UA'
                                currency_code = ' '
                             )
                          ).
    
    * Example 1: Table Expression with Key Access
    **********************************************************************
        out->write(  `--------------------------------------------` ).
        out->write(  `Example 1: Table Expressions with Key Access` ).
    
        out->write(  data = connections
                     name = `Internal Table CONNECTIONS: ` ).
    
        " with key fields
        connection = connections[ carrier_id    = 'SQ'
                                  connection_id = '0001' ].
    
        out->write(  data = connection
                     name = `CARRIER_ID = 'SQ' AND CONNECTION_ID = '001':` ).
    
        " with non-key fields
        connection = connections[ airport_from_id = 'SFO'
                                  airport_to_id   = 'SIN' ].
        out->write(  data = connection
                     name = `AIRPORT_FROM_ID = 'SFO' AND AIRPORT_TO_ID = 'SIN':` ).
    
    * Example 2: LOOP with key access
    **********************************************************************
    
        out->write(  `-------------------------------` ).
        out->write(  `Example 2: LOOP with Key Access` ).
    
        LOOP AT connections INTO connection
                           WHERE airport_from_id <> 'MIA'.
    
          "do something with the content of connection
          out->write( data = connection
                      name = |This is row number { sy-tabix }: | ).
    
        ENDLOOP.
    
    * Example 3: MODIFY TABLE (key access)
    **********************************************************************
        out->write(  `-----------------------------------` ).
        out->write(  `Example 3: MODIFY TABLE (key access` ).
    
        out->write(  data = carriers
                     name = `Table CARRRIERS before MODIFY TABLE:`).
    
        carrier = carriers[  carrier_id = 'JL' ].
        carrier-currency_code = 'JPY'.
        MODIFY TABLE carriers FROM carrier.
    
        out->write(  data = carriers
                     name = `Table CARRRIERS after MODIFY TABLE:`).
    
    * Example 4: MODIFY (index access)
    **********************************************************************
        out->write(  `--------------------------------` ).
        out->write(  `Example 4: MODIFY (index access)` ).
    
        carrier-carrier_id    = 'LH'.
        carrier-currency_code = 'EUR'.
        MODIFY carriers FROM carrier INDEX 1.
    
        out->write(  data = carriers
                     name = `Table CARRRIERS after MODIFY:`).
    
    * Example 5: MODIFY in a LOOP
    **********************************************************************
        out->write(  `----------------------------` ).
        out->write(  `Example 5: MODIFY  in a LOOP` ).
    
        LOOP AT carriers INTO carrier
                        WHERE currency_code IS INITIAL.
    
          carrier-currency_code = 'USD'.
          MODIFY carriers FROM carrier.
    
        ENDLOOP.
    
        out->write(  data = carriers
                     name = `Table CARRRIERS after the LOOP:`).
    
    Copy code
  3. Press CTRL + F3 on your keyboard to activate the class and F9 to execute the console app.
  4. Analyze the console output. Debug the program, play around with the source code to get familiar with the concepts.

Complex Internal Tables in ABAP SQL

The ABAP SQL statement, SELECT, reads data from a database table or a CDS View. When you use the SINGLE option, exactly one record is read from the database, even if more data exist that meets the conditions in the WHERE clause.

As you learned earlier, one way to receive this single record result is structured variable after keyword INTO.

If you use SELECT without SINGLE, you indicate that you are interested in all records that match the conditions in the WHERE clause. You then have to make sure that you can actually receive and store multiple records. The obvious way to do this is the usage of a complex internal table as target of the SELECT statement. This is possible but it requires addition TABLE between keyword INTO and the name of the internal table.

In the example, we want to read all three airports related to London and not just a single one of them. Therefore, we leave out the keyword SINGLE after SELECT, add keyword TABLE after INTO and use internal table airports_full as target of the SELECT statement.

The example uses an explicit field list after FIELDS that matches the columns of internal table airports_full. Of course, you can also use FIELDS *, INTO CORRESPONDING FIELDS OF TABLE, and alias names in the field list.

This example uses FIELDS * instead of an explicit field list and INTO CORRESPONDING FIELDS OF TABLE instead of INTO TABLE.

As the row type of internal table airports contains only two components AirportID and Name, only the fields with the same name are read from the database.

If you use DATA( ) in a SELECT statement after addition INTO TABLE, you inline declare an internal table. The row type of this internal table is derived from the FIELDS clause. For table fields and view elements an alias name is optional. For expressions in the FIELDS clause, an alias name is mandatory if the INTO clause contains an inline declaration.

Note
Inline declarations of internal tables are only supported after INTO TABLE. You cannot use inline declarations after INTO CORRESPONDING FIELDS OF TABLE.
Inline-declared internal tables are always standard tables without a key. You cannot declare sorted or hashed tables using inline declarations. This can cause performance problems if you fill the internal table with many rows and use key access a lot.

When you are reading multiple records from the database, some special SQL techniques become particularly interesting. One of these techniques is the UNION directive to combine the results of several SELECT statements.

The figure illustrates the combination of two SELECT results:

The first SELECT result reads ID and NAME of all carriers with CURRENCY_CODE = 'GBP'. The second SELECT reads ID and NAME of all airports with CITY = 'London'. The first SELECT returns one record, the second SELECT returns three records. Instead of retrieving these results separately, they are combined into one result with four records. It is important to point out that this happens inside the database.

A prerequisite for this technique is, of course, that the two results are compatible with each other, that is, that they have the same number of fields, the same field names. It is beneficial, though not necessary, that the types of the fields are also the same.

The ABAP SQL syntax for this example consists of two SELECT statements. Each SELECT statement has its own FROM clause, FIELDS clause, and WHERE clause, but there is only one INTO clause at the very end. The two SELECT statements are connected by keywords UNION ALL.

Note
With UNION instead of UNION ALL, the database would look for and eliminate duplicates before returning the result. We use UNION ALL to avoid this unnecessary additional load on the database.

Both field lists consist of three elements, the first and second element have identical alias names in both FIELDS clauses. The third field does not need an alias because the field name is the same in both CDS Views.

Note
The first element in FIELDS is a literal text that allows us to distinguish between Airlines and Airports in the combined result.

Try It Out: Internal Tables in ABAP SQL

  1. As in the first exercise of this course, create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
    TYPES: BEGIN OF st_airport,
                 airportid TYPE /dmo/airport_id,
                 name      TYPE /dmo/airport_name,
               END OF st_airport.
    
        TYPES tt_airports TYPE STANDARD TABLE OF st_airport
                              WITH NON-UNIQUE KEY airportid.
    
        DATA airports TYPE tt_airports.
    
    
    * Example 1: Structured Variables in SELECT SINGLE ... INTO ...
    **********************************************************************
    
        DATA airport_full TYPE /DMO/I_Airport.
    
        SELECT SINGLE
          FROM /DMO/I_Airport
        FIELDS AirportID, Name, City, CountryCode
         WHERE City = 'Zurich'
          INTO @airport_full.
    
        out->write(  `-------------------------------------` ).
        out->write(  `Example 1: SELECT SINGLE ... INTO ...` ).
        out->write(  data = airport_full
                     name = `One of the airports in Zurich (Structure):` ).
    
    * Example 2: Internal Tables in SELECT ... INTO TABLE ...
    **********************************************************************
    
        DATA airports_full TYPE STANDARD TABLE OF /DMO/I_Airport
                                WITH NON-UNIQUE KEY AirportID.
    
        SELECT
          FROM /DMO/I_Airport
        FIELDS airportid, Name, City, CountryCode
         WHERE City = 'London'
          INTO TABLE @airports_full.
    
        out->write(  `------------------------------------` ).
        out->write(  `Example 2: SELECT ... INTO TABLE ...` ).
        out->write(  data = airports_full
                     name = `All airports in London (Internal Table):` ).
    
    * Example 3: FIELDS * and INTO CORRESPONDING FIELDS OF TABLE
    **********************************************************************
    
        SELECT
          FROM /DMO/I_Airport
        FIELDS *
         WHERE City = 'London'
          INTO CORRESPONDING FIELDS OF TABLE @airports.
    
        out->write(  `----------------------------------------------------------` ).
        out->write(  `Example 3: FIELDS * and INTO CORRESPONDING FIELDS OF TABLE` ).
        out->write(  data = airports
                     name = `Internal Table AIRPORTS:` ).
    
    * Example 4: Inline Declaration
    **********************************************************************
    
        SELECT
          FROM /DMO/I_airport
        FIELDS AirportID, Name AS AirportName
         WHERE City = 'London'
         INTO TABLE @DATA(airports_inline).
    
        out->write(  `----------------------------------------------------------` ).
        out->write(  `Example 4: Inline Declaration after INTO TABLE` ).
        out->write(  data = airports_inline
                     name = `Internal Table AIRPORTS_INLINE:` ).
    
    ** Example 4: ORDER BY and DISTINCT
    ***********************************************************************
    *
    *    SELECT
    *      FROM /DMO/I_Airport
    *    FIELDS DISTINCT CountryCode
    *     ORDER BY CountryCode
    *     INTO TABLE @DATA(countryCodes).
    *
    *    out->write(  countryCodes ).
    
    * Example 5: UNION (ALL)
    **********************************************************************
    
        SELECT FROM /DMO/I_Carrier
               FIELDS 'Airline' AS type, AirlineID AS Id, Name
               WHERE CurrencyCode = 'GBP'
    
        UNION ALL
    
        SELECT FROM /DMO/I_Airport
               FIELDS 'Airport' AS type, AirportID AS Id,  Name
               WHERE City = 'London'
    *    ORDER BY type, Id
        INTO TABLE @DATA(names).
    
        out->write(  `----------------------------------------------` ).
        out->write(  `Example 5: UNION ALL of Airlines and Airports ` ).
        out->write(  data = names
                     name = `ID and Name of Airlines and Airports:` ).
    
    Copy code
  3. Press CTRL + F3 on your keyboard to activate the class and F9 to execute the console app.
  4. Analyze the console output. Debug the program, play around with the source code to get familiar with the concepts.

Save progress to your learning plan by logging in or creating an account