Technical Articles
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.
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.
I'm already looking forward to your answer ๐
Here's a little ping to Enno Wulff so he will not miss your kind words!
Thanks for sharing, Michael!
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.
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.
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..:
ย
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.
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.
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".
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 ๐
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.
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.
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.ย ๐
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 ๐
I would love that!
Thank you
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!