Skip to Content
Technical Articles
Author's profile photo Michael Keller

calculation example (ABAP, Open SQL, CDS)

Dear community, Jelena Perfiljeva recently reminded me by one of her comments how useful examples are. At least I feel that I can learn well from examples, too. In the beginning, very simple and basic examples are ideal to understand the possibilities or the underlying idea.

Since I am currently exploring ABAP Core Data Services (ABAP CDS) and the possibilities of Open SQL, I built a small example.

It’s about the addition of value pairs that come from a database table. The example is intended to show the different ways in which this addition can be done. I didn’t worry about the performance and I don’t think one of the solutions is better or worse. For me, that would depend on the overall context. However, due to the simplicity of the example, this context doesn’t exist here.

What do you think? Please comment.

This is my database table ZCALC in transaction SE12.

If you look the same database table via ABAP Development Tools for Eclipse (ADT), you will get this view. Please note that I created the database table via ADT.

@EndUserText.label : 'calculation example'
@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table zcalc {
  key client  : abap.clnt not null;
  key example : numc2 not null;
  summand_1   : int4;
  summand_2   : int4;
}

I’ve made some example entries.

Here’s my ABAP CDS design per Data Definition Language (DDL).

@AbapCatalog.sqlViewName: 'ZCDSCALCEXAMPLE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'calculation example'
define view ZCDSCALC
  as select from zcalc
{
  key example,
      summand_1,
      summand_2,
      summand_1 + summand_2 as addition_result
}

Finally, the source code. I show the same idea in three ways: via ABAP, Open SQL and CDS.

REPORT zcalculation_example.

START-OF-SELECTION.
  " ABAP based
  TYPES: BEGIN OF calculation,
           example         TYPE numc2,
           summand_1       TYPE int4,
           summand_2       TYPE int4,
           addition_result TYPE int4,
         END OF calculation.

  TYPES calculations TYPE TABLE OF calculation WITH KEY example.

  DATA abap_based_result TYPE calculations.

  SELECT * FROM zcalc
           INTO CORRESPONDING FIELDS OF TABLE abap_based_result.

  LOOP AT abap_based_result ASSIGNING FIELD-SYMBOL(<row>).
    <row>-addition_result = <row>-summand_1 + <row>-summand_2.
  ENDLOOP.

  " Open SQL based
  SELECT example,
         summand_1,
         summand_2,
         summand_1 + summand_2 AS addition_result
         FROM zcalc
         INTO TABLE @DATA(open_sql_based_result).

  " CDS based
  SELECT * FROM zcdscalc
           INTO TABLE @DATA(cds_based_result).

  IF abap_based_result = open_sql_based_result AND 
     open_sql_based_result = cds_based_result.
    cl_demo_output=>display( abap_based_result ).
  ENDIF.

All three ways (ABAP, Open SQL and CDS) deliver the same result.

I would like to have Calculon check the result. Unfortunately, he is no longer available ๐Ÿ˜‰ However, the calculations seem to be correct.

Here are some ressources I used:

Special thought provoking note for Ged Hurst: “What if you were developing with ABAP for the first time and someone told you that there are three different solutions for the same requirement in three different technologies?” ๐Ÿ™‚

 

Best regards, thanks for reading and have a great time

Michael

 

P.S.: Please support the virtual wishing well.

P.S.S.: Not tired of reading blogs? Check this blog by Enno Wulff. I really recommend it.

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo GED HURST
      GED HURST

      Well Michael, I was happily working through this CDS learning, and quite enjoying it, when I came across my name check near the end... LOL. I'll really have to think about this and reply later ๐Ÿ˜‰

      Many thanks for this simple example! I really enjoy Enno's blogs too. You are both doing a great service to the ABAP community.

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

      I'm already looking forward to your answer ๐Ÿ™‚

      Here's a little ping to Enno Wulff so he will not miss your kind words!

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Thanks for sharing, Michael!

      I didnโ€™t worry about the performance and I donโ€™t think one of the solutions is better or worse

      This reminds me of a Seinfeld episode: "he yada-yadaed over the best part!" ๐Ÿ™‚ I agree with you that it's about the context. But I think at least some general observations, like pros and cons would be helpful.

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

      I have to remember that. From now on I only work with three PowerPoint slides: motivation ... yada yada ... solution ๐Ÿ™‚

      I can't think of any general observations straight away. The comment of Patrick Van Nierop is really good. I may fill my database table with a few thousand test data and measure the runtime of the individual instructions. You can probably guess the result.

      Author's profile photo Patrick Van Nierop
      Patrick Van Nierop

      Thanks for this post. Interesting to see the different options back to back.

      IMHO and sticking to the spirit of code-push-down, I would forego option one (and personally, I try to never ever write SELECT * any more).

      So that leaves either the OPEN SQL solution or the CDS. Then, if..:

      • ..it is: a one-off -> OPEN SQL
      • ..there is a chance/need for reuse, or the data is part of a larger data model, or OPEN SQL cannot do the trick -> CDS

      ย 

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

      Thanks for your comment. Iโ€™m currently working with the SQL functions. Since Open SQL and CDS differ here, there are requirements that only CDS can fullfill.

      Author's profile photo Lars Breddemann
      Lars Breddemann

      That's a really nice back-to-basics thought game!

      When I'm looking at code, there is commonly one big question I try to answer first:

      "What is the purpose of this code?"

      Not "What steps are performed?" or "What's the performance of this?".

      So, given there is nothing else given except this abstract requirement to add the numbers in your table, I prefer code that tells me that as straight as possible.

      The whole ABAP-sing-and-dance of loading the data into an internal table and looping over it is not that straight forward. It's convoluted and comically verbose for something simple as "A + B all records".

      The CDS view approach, on the other hand, actively hides this simple task in the view definition. Fortunately, you named the resulting column "ADDITION_RESULT", so that it's obvious, what happened here (could've named it SUM_AB to make it even clearer, but hey :)).
      Unfortunately, you decided to HIDE this good piece of information by using the "DEVIL's SELECT *"...

      That leaves the OpenSQL option as a favorite for me in this example.
      Barely any boilerplate code and obvious functionality, while putting the calculation to the data (err... push-down).

      While I am painfully aware that this approach of evaluating code is not that wide-spread, in my experience so far, all the other qualities of programs ("correctness", "performance", "maintainability" -> air-quotes to avoid fundamentalist argumentations here) that are so often sought after, follow directly from being able to understand what the code should achieve.

      I really like to "refactor towards understanding" before trying to change the other aspects of programs.

       

      Thanks for this post, Michael! Even for a not-so-ABAP-person like myself, this was enjoyable.

       

       

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

      I often ask myself the same question while working on code. However, the answer is sometimes very, very well hidden ๐Ÿ™‚ And a valid answer among others is "there is no purpose".

      Author's profile photo GED HURST
      GED HURST
      Now for the challenge!
      First, randomly, my little data example gave me a number series of 1,3,7,11,15 which looked curiously like a Fibonacci series, which led me to the golden section and The On-Line Encyclopedia of Integer Sequences. The number series in my little table is actually a toothpick sequence . But I digress.
      By huge coincidence, I took part today in Thomas Jung and Rich Heilman's SAP Community Coding Challenge which gave me lots of opportunities to look at different approaches to a fiendish little challenge.
      I always look for the sweetspot between the simple and the elegant. So: I would always pick the most simple and straightforward of the 3 solutions even if the code was longer.
      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Nice answer. The choice requires knowing the options. I often feel that I don't know all the technological possibilities. Therefore, I can only find a solution based on my knowledge. It doesn't always have to be good ๐Ÿ˜‰

      Author's profile photo Florian Henninger
      Florian Henninger

      Hi Michael,

      nice one. I like the approach to make it as simple as possible. I had today a very complex task to manage with cds and also done the game to make it first as simple as possible and that give me a solution to stay with my ida-alv ๐Ÿ™‚

      So thank you for sharing it. I will for sure refer someone here:-)

      BTW: I never thought that there is something next to SE11, when using the Gui.. SE12... you always learn something new.

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

      Hi Florian, I'm also a fan of ALV IDA ๐Ÿ™‚ At the moment I'm really fascinated by the possibilities of ABAP CDS and the new extensions of Open SQL. I like the challenge of which requirements can be solved with that technology.

      Author's profile photo Michelle Crapo
      Michelle Crapo

      I love these "simple" examples.ย  ย It helps.

      Sometime (A LOT) I wonder just which one I should be using.ย  Of course, I'm choosing CDS right now.ย  I've really got to learn to use it better. ย That's my reason for using it.ย  As things progress from we "sort of" know CDS to we "do" know CDS. I'm sure we will began to put together some really good suggestions of what should be used.ย  I smile as I think of the one I just read never use "binary search".ย  I bet we'll come up with things like that.

      And then they'll be something else to learn.ย  Hence the reason I love to develop using SAP.ย  ๐Ÿ™‚

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

      Hi Michelle, I feel the same here. You have to try out a technology yourself and gain experience with it. I am currently working on a follow-up blog to this blog here. I will ping you as soon as it is released. I guess you will like it ๐Ÿ™‚

      Author's profile photo Michelle Crapo
      Michelle Crapo

      I would love that!

      Thank you

      Author's profile photo Joachim Rees
      Joachim Rees

      I agree, examples are very helpful!
      Especially if the are publicly shared and can be found again easily.
      (I still refer back to my blog https://blogs.sap.com/2017/04/11/exploring-sql-features-using-a-demo-report/ or the comments in https://blogs.sap.com/2014/09/26/code-push-down-for-hana-from-abap-starts-with-open-sql/ from time to time ).

      So: thanks for sharing!