Database Access

SAP Application Development

(S/4 HANA)
Database Access

Motivation Prerequisites
This chapter focuses on the database Before starting the exercises you
accesses. You will learn how to use should be familiar with SQL and the
the data dictionary to gain information basic concepts of ABAP programs.
about data elements, domains and so
on. Moreover, you will develop your
first ABAP program, which uses
database operations.

Content Lecture notes

This chapter explains the basic access All students should be familiar with
to the database by using OpenSQL. SQL and the basic concepts of a
You will have a look into the hierarchy database as the chapter builds upon
of data elements in the SAP system. this knowledge. Students can go on
Hence this chapter forms the with their account from previous
fundament for developing more chapters.
complex SAP applications later on.

 Product: All

 Level: Beginner

 Focus: Programming

 Version: 2.0

 Author: UCC Technical University of Munich

© 2018 SAP UCC TUM Page 1

 Database Access

Task 1: Start Eclipse and open your project

Short description: Start Eclipse and open an existing project or create a new one

Start Eclipse and open the ABAP perspective. If an ABAP project exists please Login
open this project. Else create a new ABAP project according to the Introduction to
SAP Programming chapter. Please refer to your instructor for your username and
your password. Ensure having your own package added to the favorites in Eclipse.
If you don’t have an own package, create a new one according to the mentioned
chapter.

Task 2: First steps in the data dictionary

Short description: Use the data dictionary to explore the structure of table
‘SCARR’

Start the internal SAP GUI by clicking the button or press Ctrl + 6 on your
keyboard. Please start the Data Dictionary now by entering the transaction code
SE11 in the according field.

Choose the option ‘Database table’ and type in the table name ‘SCARR’.

Then use the ‘Display’ button to have a look at the table definition.

© 2018 SAP UCC TUM Page 2

 Database Access

Hint: Hint
If the IDE opens the source code editor instead of the integrated SAP GUI

Right-click into the source code to open the context menu and select Open With •
SAP GUI or use the installed version of the SAP GUI

The SAP system brings you directly to the field definition of the table. In this tab
you see all the data elements which form the table. Moreover, you see which data
element is the key element. Beside the fields tab, there are also several other tabs
available:
 Attributes: The attributes show you to which package the table is
assigned and who did the last changes on which date.
 Delivery and maintenance: This is a very important tab as it shows you
to which delivery class the table is assigned and if changes to the table
are allowed or not.
 Entry help/check: This tab shows you if any ‘check tables’ are used
and if there is a ‘search help’ available.
 Currency/quantity fields: The last tab is important when the table
contains currency data as it is necessary to choose a reference
currency from a customizing table then.

Please switch back to the ‘Fields’ tab so you can have a look at the table fields.

© 2018 SAP UCC TUM Page 3

 Database Access

You see the fields being defined by table ‘SCARR’. This table is part of the flight
data example which SAP includes in every SAP ERP system and contains the IDs
and names of the carriers being part of this demo dataset. The table uses field
‘MANDT’ and ‘CARRID’ as key fields. This is indicated by the Key checkbox.
Moreover, you see all the different data elements. For example, the field ‘MANDT’
uses the data element ‘S_MANDT’ which is linked to the data type ‘CLNT’ and has
a length of 3.

If you want to explore the data element ‘S_MANDT’ now, use the forward Forward
navigation of the SAP system and double click on the data element. The data
dictionary now jumps directly to the definition of the data element and comes up navigation
with the details of the definition.

Hint: Hint
Please note that the data element and the table are both in the active state. This is
indicated by the ‘Active’ text next to the table name and the data elements name.
When changing a table or data element in the dictionary the state is changed to
‘Inactive’. If an active version of the table or data element is still in the dictionary,
all programs use the active version until the inactive version is activated
successfully.

Please return to the table definition using the ‘Back’ button and switch to the tab Entry help
‘Entry help/check’. This tab gives you information about implemented entry help
and check tables. When working with the table later on, the user may choose an
appropriate value to be inserted into the table. To ensure the table integrity the
entry help only shows appropriate values. The entry help is built upon the check
table. This table only contains appropriate values.

© 2018 SAP UCC TUM Page 4

 Database Access

In the next step you want to check the foreign key relations. To do so you will
consult the graphical representation of the table. The graphical representation can Display
graphic
be called by pressing the ‘Graphic’ button . This button can be found in the
toolbar. By pressing the button an additional SAPGui program is started, which is
called ‘SAP Graphical Editor’. If you do not see a graphic you have to install the
‘SAP Graphical Editor’ first. To return to SAPGui please use the ‘Back’ button (F3).

Checking the table content is very important from time to time. You can do this very
easily by choosing the following menu path:
Menu path
(Menu •) Utilities • Table Contents • Display

Alternatively, you can press the Contents button . This menu path does not
bring you directly to the table content but jumps into the data browser transaction
(transaction code SE16). All you have to do now is pressing the ‘Execute’ button
(F8). This triggers the SAP system to read all the data from table ‘SCARR’ and
display it. The result should look similar to this:

© 2018 SAP UCC TUM Page 5

 Database Access

Task 3: Read and display data from table

Short description: Use SQL statements to read data from a table and display the
data using an ABAP program

Before starting programming, have a look at the table ‘SPFLI’. The table contains
data about flights, which will be displayed in your program later. Please ensure you
are familiar with the table definition, especially the primary and foreign keys.

Now close the internal SAP GUI and create a new program called
‘Z_###_FLIGHTS’ in your existing package Z_###. Please use your number
instead of ###. Use your existing transport request or create a new one with an
unambiguous description like “Creation of program Z_###_FLIGHTS’.

In the first section of your new program you want to define the variables which are
needed later on. You need two variables: it_flights as an internal table and
wa_flight as a work area. The internal table it_flights will contain the entire content
of the database table ‘SPFLI’ whereas the work area wa_flight only contains one
single data set from the internal table.

Define
variables

As you can see you define it_flights as a type table of ‘SPFLI’. This means the SAP
system consults the data dictionary for the structure of the database table SPFLI
and creates an internal table with the same structure like SPFLI. The work area
wa_flight has the same structure like SPFLI but is not a table, only a single record.

As you want to display the content of the table in your program you have to read all
the content from the table. This is done by implementing a simple SQL statement
which reads the content from SPFLI into your internal table it_flights
SQL
statement
In the last step we want to display the content from it_flights and therefore we loop
through the internal table, read the current data record and write it into our work
area. The content from the work area will be written to the console.

Generate
output

Save,
Save and activate your program now. The output should look similar to this: check,
activate

© 2018 SAP UCC TUM Page 6

 Database Access

The program seems to work fine. But how can you know if the SQL statement was
executed successfully or not? Well, therefore SAP provides you with the system
variable ‘sy-subrc’. You may use the variable to determine if the last action/step in
your program was successful or not. You will do this now in your program using an
if-else-branch. Please use the if-branch to determine if the SQL statement was
executed successfully. In this case the program writes all the data to the output
whereas when the SQL statement was not executed successfully, the program
should return an error message.

It is necessary to place the if-branch directly after the SQL statement as the
variable ‘sy-subrc’ only contains the return code of the last instruction. Of course,
this is just a very simple error handling but it shows you how to use the system
variable to react on failures during runtime. Another one is using exceptions which
will be discussed in a later chapter.

© 2018 SAP UCC TUM Page 7

 Database Access

Task 4: Use domains, data elements and entry helps

Short description: Use the data dictionary to create an entry help for a new table

Please start the ABAP dictionary by opening the internal SAP GUI and using
transaction code SE11.

In the first step you will create a new domain as this is the highest hierarchy level in
the ABAP dictionary. Your new domain is named ‘Z_###_CHAR30’. Select the
‘Domain’ radio button and type in the name into the input field. Then click on the
‘Create’ button. The SAP system will bring you directly to the domain details where
you have to define a short text and a data type. You can choose a short text on
your own. We want to use the data type ‘CHAR’ in our domain and the number of
characters is limited to ‘30’. If the Output attribute of the Output Characteristics is
not available to choose, you may ignore it.

Now save and activate your new domain using your package and your previously Save and
used transport request. Return to the ABAP dictionary by pressing the ‘Back’ activate
button (F3).

In the next step you want to create a new data type named ‘Z_###_EN_CHAR30’.
Select the radio button ‘Data type’ and type in the name of your new data type.

The system asks you now if you want to create a Data element or a structure or a
table type. Please select the first option ‘Data element’. Maintain the short text and
the domain of your new data element. Choose the domain you created in the first
step of this task.

© 2018 SAP UCC TUM Page 8

 Database Access

Maintain
Switch to the ‘Field Label’ tab and maintain the field labels, too. You can define the
maximum length of each field label. The maximum length actually used is ‘Field
dependent on the window size. Label’

Save and activate your new data element using your transport request and Save and
package.
activate
The next step is the creation of your database table. The table will contain some
exemplary titles of persons and will therefore function as a check table. The name
of the new table is ‘Z###_TITLE’. Please note that because of naming conventions Create
there is no underscore after Z. Create it analogue to the creation of the Domain table
and Data type. Maintain the short text and then choose ‘Application table (master
and transaction data)’ as the ‘Delivery Class’ and ‘Display/Maintenance
Allowed’ as the value for ‘Data Browser/Table View Maint.’.

© 2018 SAP UCC TUM Page 9

 Database Access

Switch to tab ‘Fields’ to maintain the fields of your table and define the first field
‘Mandt’ with data element MANDT and select the 'Key' and 'Initial value' Tab Fields
checkbox for it. This is necessary for client dependent tables. Afterwards define the
second field ‘Title’. The title field is also used as the primary key. So select the
checkbox ‘Key’ and 'Initial Value'. Furthermore you have to define to define the
corresponding data element. Here you choose the created data element
‘Z_###_EN_CHAR30’. Please use the F4 help to avoid any typing failures and
save all your changes.

Now switch to the technical settings of your table by using the button: Technical
settings

The technical settings describe how the table will be stored later on and if data
records of the table are buffered or not. The maintenance of the data class is
mandatory: please choose ‘APPL0’ and the size category ‘0’.

Leave the rest of the settings as they are and save the technical settings. After
saving the settings, go back (F3) to the table maintenance. Now define an
enhancement category by using following menu path:

(Menu •) Extras • Enhancement Category

Confirm the message and select the first option.

© 2018 SAP UCC TUM Page 10

 Database Access

Activate your table now and ignore all issued warnings.

In the next step you will enter some data records into the table. This can be done
easily using the ‘Data browser’. The Data browser can be accessed via the menu
path:

Menu • Utilities • Table Contents • Create Entries Menu path

In case the menu option is displayed in gray (can not be selected), make sure you
did not forget to change the 'Data Browser/Table View Editing' in the Delivery
and Maintenance tab to 'X Display/Maintenance' Allowed as described above.

The SAP system jumps directly to a new program where you can add new data
records into the table. To add a new entry into the table, type in the title into the
input field and then press the ‘Save’ button.

Insert data
record

Add the following titles: PhD., Dr., Prof. Dr., Prof. Dr. h.c. mult. After you entered all
titles please go back to the data dictionary.

In the next step we want to create another table named ‘Z###_PERSON’. The
table will contain data about persons. You will integrate the previous generated
table ‘Z###_TITLE’ into the new table by using a foreign key. Create the table
using the following fields: PERSONID, NAME, FAMILY_NAME and TITLE. Use
also Delivery Class A and Allow Display/Maintenance for this table. Refer to the
following figure:

© 2018 SAP UCC TUM Page 11

 Database Access

Hint:
When defining table fields you may either use predefined data types or data Hint
elements. The best way to define your table fields is to define all fields using
predefined data types at first and then define table fields using data elements. Use
the button / to switch the modus.

Now you want to define the foreign key connection between both tables whereas
the ‘TITLE’ field should be linked to the table ‘Z###_TITLE’. Therefore select the

field ‘TITLE’ and press the ‘Foreign Key‘ button in the toolbar. The SAP
system comes up with a pop-up and asks you for the check table. Please select
your title table ‘Z###_TITLE’ and press ‘Enter’. The system will automatically read
the repository information and will propose a foreign key definition using the field
names from both tables.

Now finally define an enhancement category as deep (see page 10 and 11) and
specify the Data Class APPL0 and Size Category 0 in the technical settings.
Please save and activate your new table after you maintained the technical
settings. Prompted warnings can be ignored. To prove if the check table was
defined successfully you will now create a program. Leave the data dictionary.

Close the integrated SAP GUI and create a new program called ‘Z_###_PERSON’.
Use the following code fragment to define parameters in your program:

© 2018 SAP UCC TUM Page 12

 Database Access

In case the IDE marks z###_person as an unknown data type you probably forgot Save, check,
to save and activate the database table. activate, test
Save, check and activate your new program. Now when testing your new program
the SAP system comes up with an entry help on the ’PA_TITLE‘ input field. This is
because of the foreign key definition. All the values come from the title table.

Challenge: Table joins and data aggregation

Task: Your task is to join the tables SFLIGHT and SBOOK via an inner join in
order to sum up the information of the field luggweight (SBOOK-luggweight) per
plane type (SFLIGHT-planetype).

1. Therefore firstly create a new program called Z_###_LUGGWEIGHT

2. Declare an own local datatype structure, called 'FLIGHTWEIGHT' consisting of

a field to store SUM information called weight_sum of type "Packed Number" and
a field to store the plane type called planetype (use the TYPES statement to
declare own datatypes)

3. Declare an internal table called it_flight and a workarea called wa_flight that
are based on the newly created datatype.

4. Write an SQL command to read the SUM of FLIGHTWEIGHT grouped by

planetypes into your it_flight table. Use the internet to come up with a correct
SQL syntax that combine the inner table joins with the SUM aggregate. You may
find the following websites helpful:

[Link]
abapselect_join.htm
(Use the field CONNID for the table join)

[Link]
abapselect_aggregate.htm

5. Loop through your internal table and print the output to the screen

Your solution screens should look like this:

© 2018 SAP UCC TUM Page 13