New Features in ABAP 7.4 – Database Access

Anthony Cecchini is the President of Information Technology Partners (ITP), an SAP consulting company headquartered in Pennsylvania. ITP offers comprehensive planning, resource allocation, implementation, upgrade, and training assistance to companies. Anthony has over 20 years of experience in SAP R/3 business process analysis and SAP systems integration. His areas of expertise include SAP NetWeaver integration; ALE development; RFC, BAPI, IDoc, Dialog, and Web Dynpro development; and customized Workflow development. You can reach him at [email protected].

Introduction to ABAP 7.4

The technical innovations in SAP are coming in rapid succession. It should therefore not come as a surprise that even the ABAP language is undergoing transformations. A host of features have been introduced in the ABAP 7.40 release, and with the upcoming 7.50 release even more new constructs can be added to your ABAP tool-kit. While nothing has been taken away, this is to ensure backward compatibility, the rate of change continues to accelerate. To get a clear technical understanding of ABAP 7.4, I recommend you read the following links…

One tool I suggest you do add to your ABAP arsenal, and what this blog series is based on, is the book ABAP® to the Future by Paul hardy. I believe Paul has currently released a 2nd edition as well. You can find the book here.

This blog series, like Paul’s book, will focus on the changes that came with version 7.4 by breaking the blog up into sections a developer would normally be interested in, like string processing, or conditional logic…etc. Since you and I are developers, we tend to spend a good bit of time accessing the database when developing ABAP programs, so let’s begin this series here….

New Commands in OpenSQL for ABAP 7.4

CASE Statements In OPEN SQL Queries in ABAP 7.4

One of the new features of ABAP 7.4 is the ability to use CASE statements in Open SQL queries. The code below shows an example of this. In this example there is a field in a local structure named ERNAM, and it should be filled with the literals “NAME1”, “NAME2”, or “NAME3” respectively, depending on the contents of the database field AUART (DocType).

DATA: ls_vbak TYPE vbak,
      ld_vbeln LIKE vbak-vbeln.

PARAMETERS: p_vbeln like vbak-vbeln.

CONSTANTS: lc_name1(5) TYPE c VALUE 'name1',
           lc_name2(5) TYPE c VALUE 'name2',
           lc_name3(5) TYPE c VALUE 'name3'.

ld_vbeln = p_vbeln.

SELECT vbeln, vbtyp,
 CASE
   WHEN auart = 'ZAMA' THEN @lc_name1
   WHEN auart = 'ZACR' THEN @lc_name2
  ELSE @lc_name3
END AS ernam
FROM vbak
WHERE vbeln = @ld_vbeln
 INTO CORRESPONDING FIELDS of @ls_vbak.
ENDSELECT.

SELECT vbeln, vbtyp,
 CASE
   WHEN auart = 'ZAMA' THEN @lc_name1
   WHEN auart = 'ZACR' THEN @lc_name2
  ELSE @lc_name3
END AS ernam
FROM vbak
WHERE vbeln = @ld_vbeln
 INTO CORRESPONDING FIELDS of @ls_vbak.
ENDSELECT.

Please make note that you have to put an @ symbol in front of your ABAP variables (or constants) when using new features, such as CASE, in order to let the compiler know that you are not talking about a field in the database. (This is called “Escaping” the Host Variable). You also have to put commas between the fields you are bringing back from the database and put the INTO statement at the end. This is a result of a new “strict” syntax check that comes into force when the compiler notices you are using one of the new features. In this way, SAP can still being backward compatible.

Why did I use this as my first example? Surely you have used the CASE statement on data AFTER you have retrieved it. So what have we gained or even done by placing the CASE inside the SELECT? Well, what the CASE statement has allowed you to do is outsource the conditional logic to the database, as opposed to performing the CASE on the application server.

If you are unfamiliar with coding ABAP on HANA, the paradigm shift of pushing logic down into the SAP HANA database to be processed is new, but can result in huge performance improvements. While this example is not HANA specific, it does introduce you to the “concept of pushing code down” to the database layer. Something in the past we have been told to avoid. The new paradigm in ABAP is “Code-to-Data”. We will learn to create VALUE by optimizing the backend DBMS (HANA).

Performing Calculations within SQL Statements in ABAP 7.4

Another feature that is new to release 7.4 is the ability to perform arithmetical operations inside of SQL statements. Before 7.4 you had to select the data first, then you could perform calculations on it. This is best explained with an example. Let’s say we are selecting against table SFLIGHT. We want all rows for United Airlines connection id 941. For each row, we will add together the total occupied seats in Business Class and First Class, then we will multiply that by price and store the result in field paymentsum of our internal table.

DATA: lt_sflight TYPE TABLE OF sflight.

 CONSTANTS: lc_carrid TYPE s_carr_id VALUE 'UA',
            lc_connid TYPE s_conn_id VALUE '941'.

 SELECT carrid, connid, price, seatsocc_b, seatsocc_f,
    ( ( seatsocc_b + seatsocc_f ) ) * price AS paymentsum
    FROM sflight
     WHERE carrid = @lc_carrid
       AND connid = @lc_connid
      INTO CORRESPONDING FIELDS of TABLE @lt_sflight.

In-Line Declarations within SQL Statements in ABAP 7.4

ABAP 7.4 has removed the need to create the data declaration for an internal table or structure. In prior versions of ABAP, if you declared a TYPE and then suddenly wanted to retrieve an extra field in your SELECT, then you would need to make the change in two places: in the TYPE definition and in the SELECT statement. In ABAP 7.4, however, you can not only skip the TYPE definition but the internal table declaration as well. An example of this is shown below:

SELECT carrname AS name, carrid AS id
       FROM   scarr
       INTO TABLE @DATA(result).

Look at the debugger screen shot below:

As you can see, the table is created at the instant the database is accessed, and the format or ABAP TYPE of the table is taken from the types of the data fields you are retrieving.

This also works for structures if you are doing a SELECT on multiple database fields. The column name can also be influenced in the target internal table using the AS <variable> construct. So in the example below, in the internal table result, CARRNAME will be called NAME and CARRID will be called ID.

SELECT SINGLE carrname AS name, carrid AS id
       FROM   scarr
       WHERE  carrid = @id
       INTO @DATA(result).

SELECT scarr~carrname, spfli~*, scarr~url
        FROM scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid
        INTO TABLE @DATA(result).

Take a look at the Debugger screen shot below:

You can see that SPFLI has been added to the table RESULT. Please remember to address the data for SPFLI you would need to code as follows…

RESULT[n]-SPFLI-data_element

Also, please, please, please… be mindful when using the asterisk. It acts just like the wild card in SELECT * and can impact performance if you really didn’t want all of the columns.

Once again, I’d want to mention Paul Hardy’s excellent book ABAP® to the Future. Click the image below to pick up a copy, it will definitively become your go-to reference as the new features of ABAP are rolled out where you work!