SAP NetWeaver AS ABAP Release 750, ©Copyright 2016 SAP AG. All rights reserved.

ABAP - Keyword Documentation →  ABAP - Dictionary →  Classic Objects in ABAP Dictionary →  Views →  Classic Views → 

Database Views

A database view is a general view for one or more basis tables. If there are multiple basis tables, they are joined using an inner join. A matching SQL view is created on the database when the view is activated. The structure type defined using the view fields of the database view can be referenced in ABAP programs using TYPE. A database view can be accessed using both Open SQL and Native SQL.

• Basis Tables

• View Fields

• Join Conditions

• Maintenance Status

• SAP Buffering

Notes

• A database view can be activated in ABAP Dictionary without its basis tables being defined on the database. The view is also not created on the database in this case.

• Append views are available for enhancing the database views in ABAP Dictionary delivered by SAP without making any modifications.

• Suitable CDS views can be defined as replacement objects for database views. In this case, reads in Open SQL access the replacement objects rather than the database views.

Basis Tables

The basis tables of database views must be transparent tables, so that the SQL view can access them. The actual join operation is performed on the database.

Note

The basis tables of CDS database views, which are generated for CDS views, can also be other database views.

View Fields

An include mechanism can be used to add individual fields or all fields from the basis tables as view fields of a database view. When individual fields are used, a different name can be defined for a view field than the name in the basis tables. This name can have a maximum of 30 characters, must meet the naming conventions for component names of structures, and cannot be a reserved name. The reserved names that cannot be used are in the database table TRESE.

To use all fields, the * character is used instead of the field name. If the - character is used for individual fields of a basis table included using *, all fields up to the fields specified with - become view fields. If the structure of a database table whose fields were added in this way is modified, the structure of the view is also modified.

Notes

• If a foreign key field is added as a view field, it keeps this attributes and all associated foreign key fields become the foreign key of the view.

• For CDS database views, the View fields displayed here as key fields are in no way related to the key fields that are defined using DEFINE VIEW for the CDS entity.

Join Conditions

The join conditions for the inner join can be formulated using equality relationships between any two table fields in the basis tables. The inner join joins the rows of the basis tables in question as a results set containing all combinations of rows whose columns together meet the join conditions. If there are no rows that meet the join conditions in the basis tables in question, no row is created in the results set. If there is a suitable foreign key dependency (including generic and constant foreign keys) between two basis tables, this dependency can be used as a default for defining join conditions in ABAP Dictionary.

Maintenance Status

The Access setting in the maintenance status of a database view can have the following values:

• Read only

The database view can only be used to read data with Open SQL.

• Read, change, delete, and insert

If the database view contains only a single basis table, data can be changed using Open SQL.

A database view with multiple basis tables can only be read with Open SQL. If a database view contains only a single table, data can be inserted in this table using the view with the Open SQL statements INSERT or MODIFY. The following options are available for the content of the table fields not in the view:

• If the table field is defined with NOT NULL on the database, the field is filled with the corresponding initial value.

• If the table field is defined with NOT NULL on the database, and there is not initial value, nothing can be inserted and a database error occurs with a corresponding exception.

• If the table field is not defined with NOT NULL on the database, the field is filled with the null value.

Notes

• A database view should only be used to insert data in the table if the flag for initial values is set for all table fields that are not in the view.

• It is not usually a problem to modify existing data records using a database view in cases where the database view contains all key fields of the table.

• Like a database table, the maintenance status of a database view has a Display and Maintain setting alongside the Access setting. The setting must match the Access setting. In database views containing multiple basis tables, only the setting Display/maintenance not allowed is possible.

SAP Buffering

Just like in database tables, it is possible to define for a database view whether the data in the SAP buffer is buffered. The same buffering types can be defined and SAP buffering in Open SQL is handled in the same way for views and for database tables with the only difference being that, when data is modified in one of the basis tables, the entire buffer is invalidated when the buffers are synchronized, regardless of the buffering type In client-specific views, however, this only affects the content of the current client.

The following prerequisites must be met before a database view can be buffered:

• A basis table can occur in no more than nine buffered database views.

• If all fields of the database view are key fields, these fields must also be key fields of the basis tables.

Example

The database view DEMO_SCARR_SPFLI contains fields of the tables SCARR and SPFLI. Other names are assigned to these fields in the view.

The associated database object could be as follows:

CREATE VIEW DEMO_SCARR_SPFLI
   (CLIENT,
   ID,
   CARRIER,
   FLIGHT,
   DEPARTURE,
   DESTINATION )
AS SELECT
   T1."MANDT",
   T2."CARRID",
   T1."CARRNAME",
   T2."CONNID",
   T2."CITYFROM",
   T2."CITYTO"
FROM
    "SCARR" T1,
    "SPFLI" T2
WHERE
    T1."MANDT"  = T2."MANDT" AND
    T1."CARRID" = T2."CARRID"

The view could be accessed as follows using Open SQL:

SELECT *
       FROM demo_scarr_spfli
       ORDER BY id, carrier, flight
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).