ABAP News for Release 7.50 - INSERT FROM Subquery and GTTs

[horst_keller]

This is about two (no, even three) new things in Open SQL in ABAP 7.50.

INSERT FROM Subquery

Before ABAP 7.50, you can use subqueries in the WHERE condition of SELECT, UPDATE, or DELETE of Open SQL.

With ABAP 7.50, there is a new position for a subquery in Open SQL: Behind FROM of statement INSERT. This can help you to avoid unnecessary round trips of aggregated data between database and application server. I simply show you the example from the documentation:

Before 7.50: INSERT FROM TABLE

SELECT

  FROM scarr AS s

       INNER JOIN spfli AS p ON s~carrid = p~carrid

  FIELDS s~mandt,

         s~carrname,

         p~distid,

         SUM( p~distance ) AS sum_distance

  GROUP BY s~mandt, s~carrname, p~distid

  INTO TABLE @DATA(temp).

INSERT demo_sumdist_agg FROM TABLE @temp.

The task is to aggregate some data from two tables and insert them into another table. Before ABAP 7.40 you aggregated into an internal table temp and inserted that table into the target table. For that the aggregated data were transported from the database to the application server and back again.

With 750: INSERT FROM SELECT

INSERT demo_sumdist_agg FROM

  ( SELECT

      FROM scarr AS s

        INNER JOIN spfli AS p ON s~carrid = p~carrid

      FIELDS s~carrname,

             p~distid,

             SUM( p~distance ) AS sum_distance

      GROUP BY s~mandt, s~carrname, p~distid ).

Isn't that simple? Same SELECT statement as above but placed directly as a subquery in parentheses behind addition FROM of INSERT. Only one SQL statement. No transport of the aggregated data from the database to the application server and back again. Don't have to say more here, or? For more information see INSERT dbtab - subquery.

Intermission: SELECT FROM FIELDS

Did you notice the new FIELDS addition in SELECT? The FIELDS addition allows you to place the FROM clause directly behind SELECT and in front of all other clauses. To do so, the SELECT list has to be placed behind FIELDS. Why that new sequence? A FROM clause in front of all other clauses supports tools like code completion and syntax coloring in all clauses. Compare to SELECT in ABAP CDS. Same there, but with curly brackets instead of FIELDS.

GTTs

Now imagine that you need the aggregated data of table demo_sumdist_agg only within one DB transaction (DB LUW). A normal database table is too heavy weighted for such temporary data because an database's administration of a normal table supports persistent data. Therefore databases support the concept of Global Temporary Tables (GTTs). A GTT is database table with a light weight administration that is bound to one DB transaction: A GTT is always empty at the start of a DB transaction and must be empty at the end of each DB transaction.

Database systems support GTTs natively.

With ABAP 7.50, you can also define GTTs in the ABAP Dictionary by setting the Table Category to Global Temporary Table.

Then, the underlying database behaves accordingly. When accessing a GTTdefined in the ABAP Dictionary with Open SQL, the following additional rules apply:

• If you modify the contents of  a GTT with Open SQL statement, you must delete it explicitly before the end of the current database LUW either by the Open SQL statement DELETE FROM dbtab without a WHERE condition or with an explicit database commit or database rollback (then the database interface deletes the contents).
  
• If the contents of a GTT filled with Open SQL is not deleted explicitly before an implicit database commit, the runtime error COMMIT_GTT_ERROR occurs independently of the actual content (even it is empty).

Why that special behavior?

• Comprehensibility: A database system deletes the contents of a GTT at the end of the database LUW. A developer might be surprised to find a table empty after an implicit database commit (a simple WAIT statement suffices). Therefore, Open SQL forces explicit deletion.
  
  
• Platform dependency: It cannot be guaranteed that every database platform deletes the data in a GTT before an implicit database commit.

For our above example, this means: If demo_sumdist_agg is a GTT (it isn't in 7.50 SP01, but I changed it into a GTT for SP02 during writing that blog) you must include

DELETE FROM demo_sumdist_agg.

before any implicit database commit as e.g. before calling a dynpro screen. Otherwise the above runtime error occurs (and I had to adjust my examples that access the table).

For more information and examples see Global Temporary Tables.