CDS - Join, Union
In today's article, we'll take a look at linking data and views and how you can generate new amounts of data from them.
Table of contents
Article update: Since release 7.57 (S/4 HANA 2022), DEFINE VIEW is marked as obsolete, you should use DEFINE VIEW ENTITY instead. These may differ from the examples in some places. You can find more information about the new views in this article.
In previous examples, we have always limited ourselves to one entity and its data, which we have already added to new fields and content. However, this is only one aspect of CDS views, another is the linking of data sets to form larger views.
Join
If you have in the past converted different data sources into an output table, then there were different ways to do this. It was possible to import the tables individually and then convert them into a common structure using a loop. Or you decided on a join right away, which then reached a certain size in the program. But what did you do if you needed the same access again at a different location? You probably copied the join into the next program, or moved it to a reusable class.
Today we can map such reusable accesses as core data services and map individual views in different scenarios through the different levels. With the joins used, you can access all joins (inner, left, right, outer), which was not possible with the classic SAP views. Here's an example:
@AbapCatalog.sqlViewName: 'ZBSIDMOCOMPDOC'
@EndUserText.label: 'Complete Invoice Document'
define view ZBS_I_DmoCompleteDocument
as select from ZBS_I_DmoPosition as Position
inner join ZBS_I_DmoInvoice as Head on Head.DocumentNumber = Position.DocumentNumber
inner join ZBS_I_DmoPartner as Partner on Partner.PartnerNumber = Head.PartnerNumber
{
key Position.DocumentNumber,
key Position.PositionNumber,
Head.PartnerNumber,
Partner.PartnerName,
Partner.City,
Partner.Country,
Position.MaterialNumber,
Position.PositionQuantity,
Position.PositionPrice,
Position.PositionCurrency,
Head.DocumentDate
}
As a basis, we take the position table and look up information about the head and partner. We then define a key that represents the result set. In Eclipse's data preview, we can preview the data:
We can now use the Core Data Service in a SELECT in a program without building a complex join.
" Join CDS
SELECT FROM zbs_i_dmocompletedocument
FIELDS documentnumber, partnername, country, positionprice, positioncurrency
WHERE positionnumber = 1
ORDER BY documentnumber
INTO TABLE @DATA(lt_full)
UP TO 20 ROWS.
Analysis
The "Dependency Analyzer" helps to analyze relationships and links. You can find this as a separate button in the "CDS Navigator" view. This allows you to analyze the currently selected Core Data Service when you select the function.
SQL Dependency Tree
In this view you can see the relationships between the views and tables, as well as the data being merged. You can also get additional information from view names and entity names here.
SQL Dependency Graph
This view breaks down the source of our core data service down to the database. You can right-click the hierarchy to open the corresponding objects in the editor.
Complexity Metrics
At this point you get information about the complexity of your views and the relationships that lie underneath. The information can give a better indication of the performance when loading the data.
Union
A special form of the join is the union, with which two sets of data can be combined to form a new set. The data is transferred into a uniform structure and used as a common source. In our example, we take the view from the last article as the basis for the union:
@AbapCatalog.sqlViewName: 'ZBSIDMOUNION'
@EndUserText.label: 'Union example'
define view ZBS_I_DmoUnion
as select from ZBS_C_DmoPositionError
{
key DocumentNumber,
key PositionNumber,
'Normal' as PositionType,
PositionPrice,
PositionCurrency
}
where
ErrorInConversion = ' '
union select from ZBS_C_DmoPositionError
{
key DocumentNumber,
key PositionNumber,
'Error' as PositionType,
0.0 as PositionPrice,
PositionCurrency
}
where
ErrorInConversion = 'X'
In the first set of data we create all the calculations that are in order and set the PositionType to "Normal". Using the where condition, we only determine data records that have no errors in the conversion. The second set of data contains the erroneous items whose price is set to zero. A small select to import a few documents:
" Union Select
SELECT FROM zbs_i_dmounion
FIELDS *
WHERE documentnumber BETWEEN '30000010' AND '30000020'
ORDER BY documentnumber, positionnumber
INTO TABLE @DATA(lt_union).
As an example, we output the determined data to the Eclipse console, so the result could look like this:
Hint: This is an example with the same table, this could also be solved with a CDS view without a union.
Conclusion
The new virtual data model offers a variety of functions to bring the data together view by view and yet provide a reusable basis. The interface views serve as the basis and the normalized fields as the basis of the entire model.
