ABAP on SAP HANA. Part VI. New Age Open SQL ABAP 740

Open SQL in Modern ABAP

New Age Open SQL ABAP 740

In this article, we would take a break from HANA. We would pause and check what is available in Open SQL. Why is it called Open? You guessed it correct!! Open means “Open to any Database“, i.e. database independent. You do not need to have HANA database to take advantage of the Open SQL statements which can improve the way we develop our applications.

If you have been following the previous posts on SAP ABAP on HANA, you would know that CDS View is another technique to achieve Code to Data paradigm. If the same functionality can be achieved by both CDS Technique and Open SQL, which one should we adopt?

Answer: SAP wants us to stay Open. Open SQL is the first choice. Then comes CDS View and then the stored procedures (ADBC, ADMP which we will cover in our subsequent articles).

The whole idea of the modern ABAP/SQL/HANA is to push down logic to the database. We outsource these powerful innovations to put and execute the logic in the database. But do remember, SAP also wants to be as Open as possible. So given a choice between database specific and database independent solution, always chose the latter (database independent).

Enough of preaching, let us come to the topic of the day. New Age SQL for ABAP.

Prior to release 740, if we had the requirement to add an additional column in the output which did not exist in SAP table with some custom logic, then we usually wrote something like below.

We defined the TYPES. We looped through the table and added the custom logic (High Purchase or Low Purchase) as shown below.

Open SQL in ABAP 740

Let us see how we can achieve the same thing in a new way. With ABAP 740 and above, we get rid of TYPES, Data Declaration and Loop. Isn’t it cool?

Sample 1 ( Using comma separated fields with inline data declaration and usage of CASE for reference fields)

HANA SQL ABAP

Outputs from both the above techniques are same. But the path does matters. Isn’t it? 🙂

If you have some confusion regarding HANA, check this popular post: SAP HANA from Space Level.

Next, let us check the powerful inbuilt functions in SELECT.

Sample 2 ( Using JOIN and COUNT / DISTINCT functions in SELECT )

DISTINCT Material is 1 and DISTINCT Plant is 2. SUM for the Unrestricted stock is 2, AVG is 2/2 = 1 and SUM of Blocked stock is 2. This is just a sample to showcase how versatile and powerful the SELECT statement has become.

SELECT in ABAP 740

Next, in our menu, today is the Mathematical Operators in SELECT. Check the below snippet where we can directly assign ’10’ (as rebate percent) which would be in the internal table. CEIL function, multiplication, subtraction etc can be handled during the SELECT statement. If we were not in 740, we would have needed a separate loop and bunch of code to achieve this function. Isn’t ABAP real modern now? 🙂

Sample 3 ( Using vivid mathematical operators in SELECT )

Modern ABAP in HANA

Not only Mathematics is fun with ABAP 740, but also logical programming. Continue below to taste the new flavour.

Sample 4 ( Using Complex Case statement on non-referenced fields i.e. multiple in one Select )

ABAP 7.4 SELECT

COALESCE’s literal meaning from the dictionary is ‘come together and form one mass or whole‘ or ‘combine (elements) in a mass or whole‘.

According to SAP documentation, the COALESCE function in Open SQL returns the value of the argument arg1 (if this is not the null value); otherwise, it returns the value of the argument arg2. A blank must be placed after the opening parenthesis and before the closing parenthesis. A comma must be placed between the arguments

Check the usage below. If data for ekko~lifnr is present (means PO is created for the lessor) then the LIFNR (Vendor Number) from EKKO is printed else, ‘No PO’ literal is updated. This function is quite handy in many real practical scenarios.

Sample 5 ( Using COALESCE and Logical operators like GE / GT/ LE / LT etc in JOIN which was originally not available

COALESCE in HANA

Also Read: Are you a Lazy ABAPer?

How many times and in how many projects did you have the requirement to print Plant and Plant description together like 0101 (Houston Site) or in forms you had the requirement to write Payee (Payee Name)? We achieved it by looping and concatenating. We did not have better option earlier, but now we can do it while selecting the data. Thanks to the SAP Development Team.

Sample 6 (Concatenation while selecting data )

CONCATENATE in SELECT statement

Every report/conversion/interface asks us to validate the input data and we do it by checking its existence in the check table. That has become easier and better now like shown below.

Sample 7 ( Check existence of a record )

ABAP was always a fifth generation programming language and it has become more so. It has become more readable and real life syntactically too. 🙂 . HAVING function is another feather to the crown.

Sample 8 ( Use of HAVING functions in SELECT )

ABAP on HANA

You might also enjoy GPS like Tool in SAP.

Remember, sometimes we need to select all fields of more than one table and provide custom names in the output. Wasn’t it tiresome to create TYPEs and achieve our requirement?

Sample 9 ( Use of selection of all columns with renaming of fields. This is handy in case you have to do all field select )

I thought with ABAP 740, I could do the below.

The above code is syntactically correct. Wow!! I was so excited to test it as it would show all columns from both the tables.

SAP ABAP

OOPs!! We get the above message. Too early to be so happy. 🙂

Let us modify the same code a little bit. We need to define the TYPEs and declare the internal table (Inline did not work above).

Check _CHANGE is added to the field name. _TEXT is also added in the column name from second table (not captured in the screen print below)

SAP ABAP for beginners

These were just the tip of the icebergs. We would stumble upon more features and surprises as we work on projects in real system. Just to let you know, all the above code snippets are from a traditional database (not HANA) which has EhP 7.4. So do  not confuse that we need HANA database to take advantage of modern SQL techniques. We just need near and above EhP 7.4.

Next Post: SAP HANA ABAP Tutorial. Part VII. SQL Script and SAP HANA Stored Procedure

Update 08/25/2016:

We asked If CDS Views and SQL can achieve the same functionality. Which one should we choose?

Expert Simon Bain (CEO SearchYourCloud Inc.) said:
I suppose the answer would be another question or set of questions. In your application do you currently use CDS? Are your developers knowledgeable on CDS? If yes to both then probably CDS Views.
If there is a learning curve then go for the more widely known SQL and train the development team for the next update, rather than putting in code that they are either unhappy with or have little knowledge on.

At the end of the day, I would say use whichever one works best for your project, team and application. The user should not see any difference in usability. It is all about maintenance and knowledge at the end of the day.

If you want to get such useful articles directly to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons and like us on facebook.

Thank you very much for your time!!

Useful Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. GOS Tutorial
4. OOPs ABAP Tutorial
5. HANA Tutorial
6. SAP Netweaver and OData Tutorial
7. SAP Adobe Form Tutorial
8. SAP Fiori Tutorial
9. SAPUI5 Tutorial

Call for Guest Authors and Contributors to write SAP Articles on our page and get noticed and also receive cool Gifts.

Do you have any tips or tricks to share? Do you want to write some articles at SAPYard? Please REGISTER and start posting and sharing your knowledge to the SAP world and get connected to your readers. Please check our simple guidelines for contributing your articles and receiving the gifts.

Share on Facebook42Share on LinkedIn206Tweet about this on TwitterShare on Google+7Email this to someonePin on Pinterest2Share on Reddit0
Isn't it Fair to Share??

9 Comments on "ABAP on SAP HANA. Part VI. New Age Open SQL ABAP 740"

  1. Very interesting and useful. Many many Thanks.

  2. thanks for the blog. I am new to ABAP. We have a requirement of adding 2 columns, when sending data from ECC to HANA. For this we have written a ABAP include to add these columns (which is configured on SAP SLT server), which is not working. Any suggestions, if we can use openSQL to configure the same on ECC/SLT Server? thanks for your guidance.

    Regards
    Giri

    • vinay singh | August 30, 2016 at 4:48 pm |

      hello Giri,
      this should be work . Please drop the table and reload it HANA studio, if it still does not work please share the error log with us.(I hope you the restriction on lines of code you could have in it).
      You can also use Smart Data Integrator for it.

      please be reminded Open SQL is preferred approach for Code Push Down to HANA.

      Please revert if still have the issue .

      regards,

      vinay singh

  3. Very helpful blog. Thanks for sharing knowledge. Appreciate for your work. Keep doing and helping.

    • Dear Saif – Thank you so much for your encouraging words. Please keep visiting and sharing your thoughts.

      Regards,
      Team SAPYard.

  4. Very useful information for Abapers willing to explore SAP HANA and beyond. Hoping one day I could taste it’s power.

    • Dear Feleciano – Thank you for sparing some time going through our posts and leaving your feedback. You can try Hana Cloud Platform for free. You can try some basic stuffs there for free. We need to be prepared for any opportunity which might knock our door in future. 🙂

      Regards,
      Team SAPYard.

  5. Your blog is very interesting and really helpful to every abaper for quick reference.

    I would like to add one more point, which i faced many times during my work. some of the above explained statements won’t work with forall entries. So, use new open sql accordingly.

    keep sharing 🙂

    Thanks
    Naveen

    • Dear Naveen,

      Thank you very much for your message and thank you for pointing out the exceptions.

      Would it be possible for you to provide some documents about these exception in For All Entries? We would append them along with this article.

      Regards,
      Team SAPYard.

Comments are closed.