Skip to Content
Author's profile photo Sundaresan Krishnamurthy

Code Push Down for HANA Starts with ABAP Open SQL

What is Code Push Down?

One of the key differences for developing applications in ABAP for HANA is that you can push down data intense computations and calculations to the HANA DB layer instead bringing all the data to the ABAP layer and the processing the data to do computations. This is what is termed as Code-to-Data paradigm in the context of developing ABAP applications optimized for HANA.

Where does Code Push Down Start?

It is a general misconception that if one wants to do code push down in ABAP for HANA you always need to either use HANA native SQL or build complex HANA artefacts in order to achieve this.

But in reality the Code Push Down for HANA  from ABAP can very well start with ABAP Open SQL. Let us see How and Why?

The New Enhanced Open SQL

It has been SAP’s constant endeavour to improve Open SQL with each release of ABAP Application Server in order make it the most efficient channel for accessing data  in a manner that is database agnostic.

Since the release of NW AS ABAP 7.4 SP5 this attempt has manifested in the form several cool and advanced features letting go of many earlier limitations from open SQL

“Change is the Only Constant” – Heraclitus

In order to make use the advanced features of Open SQL one needs to accommodate a minor change in the way we have been writing Open SQL statement for a while now.

SELECT so_id AS sales_order_no,
       gross_amount,   FROM snwd_so
  INTO TABLE @lt_so

As shown in the above code snippet the most significant changes are the following in terms of the syntax

  1. The column list needs to be separated by commas
  2. The  host variables used within the open SQL statement needs to be escaped by an “@” symbol

In order to extend the existing functionalities of open SQL with new features a minor change had to be incorporated with the syntax hence this change.

But for sure we all would agree that positive changes are always welcome 🙂

Old is Gold but New is Platinum 🙂

The existing Open SQL statements with the old syntax will remain intact and syntactically valid. But if you want to make use of some of the advanced features of the new Open SQL you need start using the new syntax. Below is a summary(but not a complete list) of some of the new features supported in Open SQL,

Extended features supported by the new Open SQL

Following are a couple of new features supported by extended Open SQL syntax

  • Support for arithmetic expressions and computed columns in the projection list.

Remember the days when you need to loop through the internal table fetched using the Open SQL statement in order to compute/derive the value for an additional column. You don’t have to do that any more. You can have computed columns part of the projection list.

  • Support for string expressions part of the project list

       You can now have string expression with in your projection , for instance to create new concatenated column using columns from the tables accessed 

This is only the tip of the ice berg for a more comprhensive list of all new features supported by the latest Open SQL syntax refer to the following link

Code Push Down for HANA using Open SQL

As it is already evident with the extended syntax for Open SQL , the new Open SQL still remains preferred approach for Code Push Down to HANA and it very diligently augments and supports the other optimization techniques like AMDP and CDS.

What are the advantages of using Open SQL to do code push down?

  1. Your ABAP code will remain database agnostic and will run on any ABAP server independent what is the underlying database
  2. You implicitly take advantage of all transparent optimizations that have been achieved in the Database Interface level
  3. All default performance optimizations like use of buffer and house keeping activities like client handling etc are automatically taken care of

5 Ways of achieving Code Push Down to HANA from ABAP using Open SQL

  1. Let us first get started 🙂 Start using the new Open SQL syntax. Remember it is easy to convert your existing Open SQL statements to new syntax without any side effects.
  2. Use aggregate functions where relevant instead of doing the aggregations in the ABAP layer
  3. Use arithmetic and string expressions within Open SQL statements instead of looping over the data fetched to do the arithmetic and string operations
  4. Use computed columns in order to push down computations that would otherwise be done in a long loops
  5. Use CASE and/or IF..ELSE expressions within the Open SQL in order embed the logic of conditional expression which in the past would have been done after fetching the results from the database.

If you want to play around with the new Open SQL syntax we have cool new feature in the latest released version of ABAP in Eclipse called “SQL Console”. To know more about this feature refer to my colleague Vijayan Balasubramanian blog on the new SQL Console in AiE here.

Get “Involved” 😎

Do you want to have the first hand experience of the latest Open SQL features in addition to other exciting features for ABAP Development for SAP HANA? If you say yes, why do you wait?

Get enrolled for the Open SAP course on ABAP Development for SAP HANA and get “involved”  and be part of the exciting journey.

Wishing you loads of fun!!! 🙂

Tell me, and I will forget. Show me and I may remember. Involve me, and I will understand.Confucius

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hello Sundaresan,

      Thank you for the detailed article. Do you have an end-to-end example of how can we convert a existing custom report into HANA using the code push down methodology.

      We currently are running ECC on HANA and I am trying to convert an existing Z report into HANA.



      Author's profile photo Ajith Cheruvally
      Ajith Cheruvally

      Thanks Sundaresan,

      This is really informative.


      Author's profile photo Joachim Rees
      Joachim Rees

      Hey Sundaresan, very nice, thanks for opening my eyes in that respect!

      So the lesson learned here is: if you formulate your Open-SQL SELECT so it returns the value / format you want, you don’t have to do any post-processing in ABAP.

      Here’s a little, very simple use-case I have imagined:

      I want list of sales-orders and who created them:

      from vbak
      where .....



      12321   JOHNSON

      54654   SAP-Mueller

      46546   Exim

      21132   SAP-Plattner

      …apparently, some usernames where created with a company prefix, others not.

      If there is such a prefix, I want to drop it, leaving over only the name.


      This could be how:

      replace( ernam, 'SAP-', ' ' ) as Person_Name
      from vbak
      where .....



      12321   JOHNSON

      54654   Mueller

      46546   Exim

      21132   Plattner


      (OPEN SQL-Console in AdT is great for trying this out!)



      Author's profile photo Sundaresan Krishnamurthy
      Sundaresan Krishnamurthy
      Blog Post Author

      Dear Joachim,

      I am happy that  you liked the post and were able to relate it to a sample use case that you have demonstrated.

      Best regards


      Author's profile photo Michelle Crapo
      Michelle Crapo

      Excellent blog.  I wish I would have found it quicker.   I've also had fun learning that select * isn't a bad thing anymore when using CDS.   That was a big surprise for me.  When I get a chance I'm going to click through the links.

      This blog is even helpful around 2 years later!  We are catching up.

      Author's profile photo Joachim Rees
      Joachim Rees

      This blog is even helpful around 2 years later!

      Yes, and I think that is true form some other blogs as well. I don't have an example handy, but I like the "Related Blog Posts"-section on the right -> that's how I re-discovered this today! 😉


      Author's profile photo Adem Güler
      Adem Güler

      Excellent blog. Thank you for sharing.

      Author's profile photo Joachim Rees
      Joachim Rees

      Again, coming back to this blog, as the topic is still relevant and comes up from time to time.

      [ Interesting: Searching for "abap code pushdown begins with open sql" in SAP Community search did NOT help me find this;
      in Google, it was the 1st hit! ]

      Those are more examples of simple use cases, surly not new but helpful as a reference:

      *1. existence check
      SELECT SINGLE @abap_true
      FROM mara
      WHERE matnr = 'TESTMAT123'
      INTO @DATA(lv_matnr_exists) .
      *2. build range table with select
      DATA: lr_test_material TYPE RANGE OF mara-matnr.
      *2.1 with DB literals
      SELECT FROM mara
      FIELDS 'I',
      WHERE matnr LIKE 'TEST%'
      INTO TABLE @lr_test_material.
      *2.2 same, with ABAP-Constants
      SELECT FROM mara
      FIELDS @wmegc_sign_inclusive,
      WHERE matnr LIKE 'TEST%'
      INTO TABLE @lr_test_material.
      Author's profile photo Sundaresan Krishnamurthy
      Sundaresan Krishnamurthy
      Blog Post Author

      Thanks Joachim for the feedback. I am glad this blog is still useful. I will check and add your inputs part of the main blog. Is that ok with you?

      Author's profile photo Joachim Rees
      Joachim Rees

      Sure, if you see benefit in including parts if my comment in the main blog, that's very ok with me!