Skip to Content

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.

/wp-content/uploads/2015/11/gtt_832875.gif

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.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Peter Inotai

    Hi Horst,

    I guess in case of “INSERT FROM SELECT” it’s not possible to debug SELECT and INSERT separately, right? Or is there any trick or way to see what’s going wrong when it doesn’t work as expected? Or just return to “old” syntax, make it work, then try to combine them with the “new” way?

    Thanks,

    Peter

    (0) 
    1. Horst Keller Post author

      Hi Peter,

      You can ask the same question for UNIONs and all other complex SQl statements. It would mean to debug a single SQL statement on database level (and that must be possible on all platforms then). I don’t know if database systems support step-by-step debugging of SQL statements at all, do they? If yes an infrastructure has to be built that connects the ABAP debugger to the SQL debuggers. Maybe not unthinkable but dreams of the future. As far as I know, first steps are taken with debugging AMDP procedures. But those are HANA only and procedures consist of some statements not single statements.

      Horst

      (0) 
      1. Peter Inotai

        Hi Horst,

        Thanks for the info.

        That’s true, that the same applies for all complex SQL statements, which are pushed down to DB level. Performance vs maintenance/troubleshooting possibilities will be an interesting topic in this area 🙂

        Peter

        (0) 
  2. Suhas Saha

    Hello Horst,

    as per the SAP documentation –

    GTTs are used to split complicated database processes by saving temporary intermediate results in multiple steps.

    I was thinking if GTTs can replace data clusters (INDX-type) for storing temporary data. But then GTTs are restricted to be used in a single DB-LUW. For all other cases i can use an internal table to store the transient data, why/where do i need a GTT?

    BR,

    Suhas

    (0) 
    1. Horst Keller Post author

      Hi Suhas,

      But with an internal table you have to transport the data to and from the application server. With GTTs you stay on DB level. In fact, from the usage (not technical) point of view, GTTs are a kind of internal tables on DB level: They are only available within a “session”. B.t.w. the above sentence should rather be:

      GTTs can be used to store temporary results in order to split complicated database processes into multiple steps.

      Best

      Horst

      (0) 
      1. Shai Sinai

        As far as I understood (Correct me if I’m wrong), the main purpose of GTT is to enable you executing complex selections/updates of internal tables with standard DB tables/ABAP CDS in a simple manner (e.g. Join of internal table A with DB table B). Isn’t it?

        Edit: Weird. My original comment should have been in reply to Suhas Saha question. I’ve just read Horst answer and it seems that he already said exactly the same thing.

        (0) 
        1. Horst Keller Post author

          Hi Shai,

          What do you mean with “internal table”? An ABAP internal table? Then the answer is no.

          GTT’s are special database tables that are defined globally, but whose contents are restricted to temporary data of one DB transaction (DB LUW).

          Please compare the following two examples:

          They do the same. But in the second example, the SELECT statement of the first example is split in two statements –  say for reasons of maintainability – and the intermediate result is stored in a GTT.  If another Open SQL statement would need to work with the intermediate result, it could do so too, as long it is done before the end of the current DB LUW.

          Or do you think think about scenarios, where you fill GTTs from internal ABAP tables and join them on the databse to DB tables or use them for subqueries? That would work too (selfmade FOR ALL ENTRIES).

          Horst

          (0) 
          1. Shai Sinai

            Yes.

            Actually, we are saying exactly the same thing:

            GTT is (more or less) an internal table in DB level.

            I’ve already edited my previous message after seeing you also replied to Suhas’ question.

            Somehow, I missed it.

            (0) 

Leave a Reply