What is Database View in SAP ABAP? Types, Uses & Examples

If you're working within the SAP ecosystem it is the data that runs everything. If you're pulling material master records or processing financial documents or managing HR information, the way in which your ABAP applications interact with the database determines how tidy quick, efficient and maintainable your code actually is.

Two concepts that are in the middle of this interplay are databases views and databases tables that are part of SAP's ABAP. Many new developers use these two concepts interchangeably, however they have very distinct purposes. A database table is a physical storage for information. The database view can be described as an online window that allows you to examine data across several tables which are connected, filtered, and displayed -- without duplicated byte.

This guide will cover everything about what is a view of a database is in SAP ABAP is and how to make one, the kinds of tables that are available for database as well as what a logical database is and how you can perform each important DML operation including insert and update, modify and delete, using actual, working ABAP example code.

What is Database View in SAP ABAP?

Database views in SAP ABAP is a virtual table within the ABAP Dictionary (transaction SE11) which is built upon one of the tables in the database that are joined. It does not contain any information of its own. Instead, it retrieves information from base table every when it is called upon and presents a single and read-optimized results set to the program that is calling.

Consider the table view in the same way as a stored SQL join. The join conditions are defined only once in the Dictionary the ABAP program is able to SELECT from the view exactly as it would with a normal table, without the requirement to write the join logic each time.

Key Characteristics of a Database View

• It is described within the ABAP Dictionary, not in the database directly (though SAP does create a similar view in the DB).
• It can be used to join inner joins between various transparent tables.
• It is used primarily to perform read operations. The ability to write through database views are not permitted.
• It helps code reuse — create the join once, then apply it throughout.
• It is possible to expose only certain fields from the base table, acting as an abstraction layer.

What is Database View in SAP ABAP — A Practical Analogy

Imagine that you already have two different tables: MARA (general material data) and MAKT (material descriptions). When you require the material's number and description, you'll have to write a join. A database view, such as MARA_MAKT_VIEW will hold the join for a long time and then you can select in the table just like every other table.

Types of Views in SAP ABAP Dictionary

SAP ABAP supports four types of views. Each one serves a specific function.

1. Database View

The most frequently used view type. It is directly linked onto a view of a database within the base RDBMS (like HANA, Oracle and SQL Server). It only supports internal joins and is employed to read access across a number of tables.

Use example: Joining VBAK (sales order header) with VBAP (sales order item) to read a combined order information.

2. Projection View

Projection views are built upon a table, and can be used to limit access to particular fields. It blocks the sensitive column from applications that do not require the columns.

Application: Exposing just the fields that are not sensitive in the PA0001 HR table to a program for reporting.

3. Help View

This is used specifically as the base for search aids (F4 assist). It allows external joins, which database views do not.

Use instance: Building a customer search assistance that incorporates information taken from KNA1 and KNB1.

4. Maintenance View

It is used to store information across tables in one view, usually via an SM30 transaction. It permits insert, update, deletion, and insert operations.

Application: Customizing and maintaining entries which span two congruous configuration tables.

Database Tables in SAP ABAP

Before diving into the details of views, it's helpful to understand the basis that they rest on — database tables.

Types of Database Tables in SAP ABAP

SAP ABAP recognizes three types of tables in databases:

1. Transparent Tables

The most commonly used type. There is a one-to-one connection to the ABAP Dictionary definition and the physical table that is in the database. Each transparent table has one table within the DB. MARA, VBAK, KNA1 -- they are all transparent tables.

2. Pooled Tables

Small ABAP logical table databases are stored together in one physical database table, referred to as the table pool. It was used in the past on SAP systems to store massive amounts of smaller configuration or control tables. You are not able to directly join pooled tables using transparent tables within a database view.

3. Cluster Tables

Similar to pooled tables, cluster tables contain logical tables within a physical database table known as the table cluster. They typically store variable length data. BSEG (accounting document segment) is among the most popular cluster tables. Like pooled tables they are not part of a view join database.

How to Create Database Table in SAP ABAP

Creating the custom database table can be accomplished through transaction SE11.

Step-by-Step Process

1. Go to SE11 and select "Database table" — enter the name of your table (Z or prefix of Y, e.g., ZEMPLOYEE) then click Create.
2. Enter a short description.
3. The Delivery Class is the one you choose to set (usually A for the application table).
4. Set Data Browser/Table View Maintenance to "Display/Maintenance Allowed."
5. Click on your fields tab, and then create your fields.
6. Affix a domain as well as a Data Element on each field.
7. Define the primary key field (check the Key checkbox).
8. Go to the Technical Settings tab — set Data Class (e.g., APPL0) and Size Category.
9. The table must be activated (Ctrl+F3).

" After creating table ZEMPLOYEE with fields:
" MANDT (Client), EMP_ID (Employee ID - Key), EMP_NAME, DEPT, SALARY

" You can now use it in ABAP just like any other table:
SELECT * FROM zemployee INTO TABLE @DATA(lt_employees)
  WHERE dept = 'HR'.

Create Database View in SAP ABAP

A database view can also be created and utilizes the transaction SE11.

Step-by-Step: How to Create Database View in SAP ABAP

1. Go to SE11 and select "View" — enter view name (e.g., ZMAT_DESC_VIEW) and click Create.
2. Select "Database view" as the view type.
3. Enter a short description.
4. Click on the Tables/Join Conditions tab — add the tables that are base (e.g., MARA and MAKT).
5. Determine your joining conditions (e.g., MARA-MATNR = MAKT-MATNR).
6. Click on the View Fields tab — choose the fields you would like to display within each table.
7. Include selection conditions if necessary (e.g., MAKT-SPRAS = 'EN' to limit to English description).
8. Activate the view.

" Selecting from the created database view
SELECT matnr maktx meins mtart
  FROM zmat_desc_view
  INTO TABLE @DATA(lt_materials)
  WHERE mtart = 'FERT'.

LOOP AT lt_materials INTO DATA(ls_mat).
  WRITE: / ls_mat-matnr, ls_mat-maktx.
ENDLOOP.

Once it is activated the view will behave as the normal table when viewed from an ABAP perspective.

How to Update Database Table in SAP ABAP

SAP ABAP provides several Open SQL statements to manipulate data: INSERT, UPDATE, MODIFY as well as DELETE.

INSERT — Adding New Records

DATA: ls_employee TYPE zemployee.

ls_employee-mandt    = sy-mandt.
ls_employee-emp_id   = 'E001'.
ls_employee-emp_name = 'Rahul Sharma'.
ls_employee-dept     = 'Finance'.
ls_employee-salary   = 75000.

INSERT INTO zemployee VALUES ls_employee.

IF sy-subrc = 0.
  WRITE: / 'Record was inserted with success.'.
ELSE.
  WRITE: / 'Insert failed — record may already exist.'.
ENDIF.

UPDATE — Modifying Existing Records

The UPDATE statement alters specific fields in existing rows. If there is no row that matches there is no change and sy-subrc gives 4.

" Update the salary of a particular employee
UPDATE zemployee
  SET salary = 85000
  WHERE emp_id = 'E001'.

IF sy-subrc = 0.
  WRITE: / 'Record is up-to-date.'.
ENDIF.

You can also update from an area of work:

DATA: ls_employee TYPE zemployee.

SELECT SINGLE * FROM zemployee
  INTO @ls_employee
  WHERE emp_id = 'E001'.

IF sy-subrc = 0.
  ls_employee-salary = 90000.
  ls_employee-dept   = 'IT'.
  UPDATE zemployee FROM @ls_employee.
ENDIF.

Modify Database Table in SAP ABAP

The MODIFY statement is an amalgamation of INSERT and UPDATE. If a record that has identical primary keys is present it will update it. If there is no match, it creates a new record. This is extremely useful in cases where you don't know if records exist prior to time.

DATA: ls_employee TYPE zemployee.

ls_employee-mandt    = sy-mandt.
ls_employee-emp_id   = 'E002'.
ls_employee-emp_name = 'Priya Mehta'.
ls_employee-dept     = 'HR'.
ls_employee-salary   = 68000.

MODIFY zemployee FROM @ls_employee.

IF sy-subrc = 0.
  WRITE: / 'Record added or modified.'.
ENDIF.

Modify Database Table from Work Area Transporting in SAP ABAP

The TRANSPORTING option lets you edit only certain fields within the work area, while leaving the rest of the fields unaffected.

DATA: ls_employee TYPE zemployee.

ls_employee-emp_id = 'E001'.
ls_employee-dept   = 'Management'.

" Only the DEPT field will be changed; other fields will remain unchanged
MODIFY zemployee FROM @ls_employee
  TRANSPORTING dept.

IF sy-subrc = 0.
  WRITE: / 'Department updated in a selective manner.'.
ENDIF.

This is a performance-conscious approach -- you avoid reading the full record first just to update one field.

Update Database Table from Internal Table in SAP ABAP

If you are required to update several records simultaneously, using an internal table and bulk operations is more effective than looping through each record.

DATA: lt_employees TYPE TABLE OF zemployee,
      ls_employee  TYPE zemployee.

" Build internal table with updated records
ls_employee-mandt    = sy-mandt.
ls_employee-emp_id   = 'E001'.
ls_employee-emp_name = 'Rahul Sharma'.
ls_employee-dept     = 'IT'.
ls_employee-salary   = 95000.
APPEND ls_employee TO lt_employees.

ls_employee-mandt    = sy-mandt.
ls_employee-emp_id   = 'E002'.
ls_employee-emp_name = 'Priya Mehta'.
ls_employee-dept     = 'HR'.
ls_employee-salary   = 72000.
APPEND ls_employee TO lt_employees.

" Bulk MODIFY from internal table
MODIFY zemployee FROM TABLE @lt_employees.

IF sy-subrc = 0.
  WRITE: / 'All records were updated correctly.'.
ENDIF.

The use of the FROM TABLE method allows the push of a single bulk SQL statement into the database, not individual row-level updates — this can be significantly quicker for huge data sets.

Delete Database Table in SAP ABAP

The DELETE statement deletes data from a table in a database. It may perform the operation on one row using a work area or multiple rows using the WHERE condition.

Delete a Single Record

DATA: ls_employee TYPE zemployee.

ls_employee-mandt  = sy-mandt.
ls_employee-emp_id = 'E001'.

DELETE zemployee FROM @ls_employee.

IF sy-subrc = 0.
  WRITE: / 'Record deleted.'.
ELSE.
  WRITE: / 'Record cannot be discovered.'.
ENDIF.

Delete Multiple Records Using WHERE

" Eliminate all employees of the Finance department
DELETE FROM zemployee WHERE dept = 'Finance'.

WRITE: / sy-dbcnt, 'record(s) deleted.'.

sy-dbcnt contains the amount of rows that were affected by the previous DML statement. It is useful for recording and validating.

Delete Using an Internal Table

DATA: lt_to_delete TYPE TABLE OF zemployee.

" Fill the table with the key values of records to delete
APPEND VALUE #( mandt = sy-mandt emp_id = 'E003' ) TO lt_to_delete.
APPEND VALUE #( mandt = sy-mandt emp_id = 'E004' ) TO lt_to_delete.

DELETE zemployee FROM TABLE @lt_to_delete.

What is Logical Database in SAP ABAP?

A logical database within SAP ABAP can be described as a distinct ABAP program that offers a structured, hierarchical method to read data from various related databases. It functions in the role of an abstraction layer — managing all the authorization checks, SELECT statements as well as table join logic — to ensure that the user of the report program does not need to.

Logical databases were widely utilized in traditional ABAP reports prior to when they were introduced in the Open SQL and OOP era. SAP provides a number of basic logical databases, including F1S for documents for FI, PNP for HR personnel information and KDF for CO data as well as other.

Logical Database in SAP ABAP — How It Works

A logical database is comprised of three elements:

• Structure: defines the table's hierarchy (parent-child relationship).
• Selections: screen selection fields which the logical database places on the report's screen for selection.
• Program: the actual ABAP program that reads data, and sends information to the reports through GET events.

" Classic ABAP report using a logical database (e.g., PNP for HR)
REPORT zhr_report.

" The LDB PNP provides the GET PERNR event automatically
GET pernr.
  WRITE: / pernr-pernr. " Personnel number provided by LDB

Logical Database in SAP ABAP — Current Relevance

With the advent in the use of ABAP OOP, HR Renewal and SAP S/4HANA these databases are generally thought of as a concept from the past. The HR module continues to use PNP and PNPCE extensively, however for all new developments it is recommended to utilize specific Open SQL SELECTs, CDS Views (in SAP S/4HANA) as well as dedicated service layers. Understanding the logical database is crucial to maintain and enhance classic reports that are found in old SAP landscapes.

Database View vs. Database Table — Key Differences

Best Practices for Working with Database Tables and Views

• Always make use of @DATA(...) declarations inline within modern ABAP to keep the code short and safe for typing.
• Choose MODIFY ... from a table over looping MODIFY calls when working with multiple records — results in one database round trip.
• Always verify sy-subrc after each DML operation to deal with errors easily.
• Utilize TRANSPORTING with MODIFY for when you only need to update certain fields — this will prevent unnecessary overwrites.
• Wrap the related DML operations within a COMMIT WORK or ROLLBACK WORK block to assure consistency of data.
• Utilize database views instead of repeating the same join logic in multiple programs — this keeps your codebase DRY (Don't Repeat Yourself).
• Do not write to views in databases. Use the tables that are transparent directly to make changes to data.
• In S/4HANA-based environments, consider CDS (Core Data Services) views as the latest alternative for ABAP Dictionary database views for more complex reporting scenarios.

Conclusion

Understanding the definition of what a view for databases is in SAP ABAP -- and the difference between it and the database table provides you with a more precise tool for each data-access scenario. Database views allow you to encapsulate complicated JOIN functionality at the Dictionary level, which makes your SELECT queries more streamlined and simpler to manage. Tables in databases are where the data actually is and ABAP's Open SQL commands give you exact, efficient control over every INSERT, UPDATE, MODIFY as well as DELETE operation.

Logical databases, though largely outdated, support HR reports that are traditional and are an important concept to understand for anyone who is working in the older SAP environments.

When you're creating the first Z-table in your organization or creating a join-view to be used in a report program or writing bulk update logic with internal tables, the basics taught in this article will help you in each SAP ABAP project.

Frequently Asked Questions (FAQs)