Data Retrieval Overview

You can use Open SQL or Native SQL statements in your program to enable database access.

 

Open SQL is preferred over Native SQL, because Open SQL is not database-specific and ensures that your program is independent of the database engine. Open SQL allows portability between different RDBMS systems and allows your company to change to a different database system, without the need to change the code. ABAP Open SQL is completely integrated into the ABAP Workbench, which has integrated syntax checks for Open SQL syntax.

 

ABAP Open SQL also makes it possible to use the SAP table buffers, where buffering has been activated for a particular table.

 

 

Database Views

You can create database views in the ABAP Dictionary. Database views are typically application-specific and allow you to work with multiple database tables. Database views are implemented on the database as an INNER JOIN.

 

You can also buffer database views. This means that data can be read from the buffer on the application server when you select data from the view. The same rules apply when buffering views as when buffering tables (for example, it should be done when access is predominately read access).

 

 

Creating a Database View

1. Go to the ABAP Dictionary and create a view of type database view
2. On the table/join condition tab, enter the appropriate tables.
3. The system will propose the join conditions, which you should check
4. On the view field tab, enter the fields to be included in the view. Using table fields button can make the work easier.
5. Save and activate the database view.

Inner Joins

In a single result set, an inner join links data from two or more transparent database tables. This result set contains all combinations of rows whose columns meet the join condition. If there are no records in the result set that match the join condition, then an entry will not be created in the result set.

 

Join conditions are not limited to key fields. They can be declared using any fields for which a foreign key relationship is defined, or in certain cases, between fields that have the same domain.

 

If columns from the tables involved in the join have the same name, ensure that you prefix the field with the table name or an alias name when you refer to it in the Select statement.

 

A table join is an efficient way of reading from the database. The database decides which table is read first and which index is selected.

 

 

Outer Join

With a LEFT OUTER JOIN, the result set can also contain entries from the left-hand table without the presence of corresponding data records (according to the join conditions) from the table on the right. These table fields are filled with null values by the database. However, the null values will be translated to an appropriate ABAP type before they are passed to your program.

 

Use a LEFT OUTER JOIN statement when data from the table on the left is needed even if there are no corresponding entries in the table on the right. For example, not all airlines (SCARR table) have flights schedules (SPFLI table), but all airline names must be displayed in the list.

 

 

FOR ALL ENTRIES

The FOR ALL ENTRIES statement works with the database in a set-oriented way. Initially, data is fetched from the first database table and collected in an internal table. You must then evaluate the value of SY-SUBRC, to check whether the initial SELECT returned any data. If data was retrieved, the SELECT...FOR ALL ENTRIES can then be used to retrieve data from a second database table, based on the entries in the first internal table.

 

Caution: If the first SELECT returns no data, the SELECT... FOR ALL ENTRIES will retrieve ALL entries from the database.

 

To prevent multiple reads of the same data, you can use DELETE ADJACENT DUPLICATES to remove duplicate entries from the internal table, which is filled with data from the first database table.