Skip to Content
Author's profile photo Horst Keller

ABAP News for Release 7.50 – INSERT FROM Subquery and GTTs

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.

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Peter Inotai
      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog 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

      Author's profile photo Peter Inotai
      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

      Author's profile photo Michal Lipnicki
      Michal Lipnicki

      One step closer to SELECT (...) FROM ( subquery ).

      🙂

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      Or even something better: "Common Table Expressions (CTE)", under development, more versatile.

      Author's profile photo Michal Lipnicki
      Michal Lipnicki

      Now You're just teasing ... next You'll be telling us that analytic functions with partitioning are coming.

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      In fact, yes, they're coming.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog 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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Horst Keller
      Horst Keller
      Blog 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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      I already wondered ...

      I guess you know more about databases than I do 😉

      Author's profile photo Abdul Hakim
      Abdul Hakim

      Excellent.

      Author's profile photo Rajagopal AshokKumar
      Rajagopal AshokKumar

      Hi Horst,

       

      If I had to insert millions of records, is it advisable to go with this insert statement instead of inserting through parallel processing in small packages like 10000 records?

       

      Regards,

      Ashok