Consuming CDS Views Using Open SQL in ABAP
Core Data Services (CDS) views are widely used to
retrieve and manipulate data efficiently. While CDS views can push complex
calculations to the database, they can also be consumed in Open SQL queries in
ABAP programs, making it simple for developers to access and display data in
their ABAP applications.
Why
Consume CDS Views in ABAP?
CDS views are powerful because they offer a declarative
method to access data, reduce code complexity, and optimize database
interactions. By consuming them in Open SQL, you bridge the gap between the
data layer and ABAP code, enabling the execution of database-centric operations
within your ABAP logic.
Steps
to Consume CDS View in ABAP Program
When you want to consume a CDS view within an ABAP
program, follow these basic steps:
1. Create the CDS View:
You would have
already created a CDS view, which logically models the data. The view is
activated, and a corresponding SQL view is created in the database.
2. Using Open SQL in ABAP:
In your ABAP
code, you can reference this view just like a normal table using Open SQL. This
allows you to select data from the CDS view and store it in an internal table
for further processing.
Creating CDS view - click here:
Consuming a SQL View
Below is an example of how to consume the SQL view
generated from a CDS view using Open SQL.
abap
Consume generated
SQL view using new Open SQL
SELECT * FROM zcdsview3 INTO TABLE @DATA(itab).
IF sy-subrc = 0.
WRITE: / 'NO. OF COUNTS AVAILABLE', sy-dbcnt.
LOOP AT itab INTO DATA(wa_itab).
WRITE: / wa_itab-customerno,
wa_itab-country,
wa_itab-customername,
wa_itab-city.
ENDLOOP.
ELSE.
WRITE: / 'NO DATA'.
ENDIF.
In the above code, we are selecting all the fields from
the SQL view zcdsview3 into an internal table (itab). We then loop through the
internal table to display the selected records.
Recommended Way: Consuming the CDS View
Though you can consume the SQL view, it is generally
recommended to consume the CDS view directly for better performance and
flexibility.
abap
Consume
recommended CDS view in program
SELECT * FROM
zcds_view3 INTO TABLE @DATA(itab).
IF sy-subrc = 0.
WRITE: / 'NO OF COUNTS AVAILABLE', sy-dbcnt.
LOOP AT itab INTO DATA(wa_itab).
WRITE: / wa_itab-customerno,
wa_itab-country,
wa_itab-customername,
wa_itab-city.
ENDLOOP.
ELSE.
WRITE: / 'NO DATA'.
ENDIF.
Here, you are directly selecting data from the CDS view zcds_view3,
which is a cleaner and more efficient way of consuming the data.
Key
Considerations
- Performance: CDS views can handle data-intensive
operations at the database layer, reducing the load on the application server
and improving performance.
- Flexibility: You can filter and select specific fields
from a CDS view, just like in any SQL query. This allows you to manage the
amount of data processed by your ABAP logic.
- Seamless Integration: CDS views integrate easily with
ABAP programs, ensuring smooth access to the data while taking advantage of the
performance improvements at the database level.
Consuming
Selected Fields from CDS View
If you only want to retrieve a subset of fields from the
CDS view, you can modify your Open SQL statement to select specific columns.
abap
Consuming only
specific fields from the CDS view
SELECT customerno,
country, customername, city
FROM zcds_view3 INTO TABLE @DATA(itab).
IF sy-subrc = 0.
WRITE: / 'NO OF COUNTS AVAILABLE', sy-dbcnt.
LOOP AT itab INTO DATA(wa_itab).
WRITE: / wa_itab-customerno,
wa_itab-country,
wa_itab-customername,
wa_itab-city.
ENDLOOP.
ELSE.
WRITE: / 'NO DATA'.
ENDIF.
This approach is useful when you do not need all fields
in the CDS view and want to minimize the amount of data fetched from the
database.
Consuming CDS views in ABAP using Open SQL provides a
flexible and efficient way to interact with complex data models defined in CDS.
Whether you're accessing a full set of data or just a few fields, you can
leverage the power of CDS in your ABAP programs to enhance performance and
reduce complexity. Always prefer to consume CDS views directly rather than
their generated SQL counterparts for better maintainability and future-proofing
of your ABAP code.