This is a test message to test the length of the message box.
Login
ABAP CDS Join, Union
Created by Software-Heroes

CDS - Join, Union

2333

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.

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.


Included topics:
CDSCore Data ServiceJoinUnion
Comments (0)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP Tools - Work with Eclipse (CDS Analysis)

Category - ABAP

In the complex world of CDS views, it is important to have the right tool at hand to ensure an analysis of the structures and data sources.

08/25/2023

CDS - View Entity

Category - ABAP

In this article you will learn more about the new Core Data Service entities, what they bring and what makes them different from the old views.

07/29/2022

CDS - Learnings

Category - ABAP

In this article we summarize the learned content and show you the way for what you will need the CDS Views in the future.

06/10/2022

CDS - Virtual fields

Category - ABAP

This article is about virtual fields in Core Data Services and how you can subsequently deliver such complex data.

06/03/2022

CDS - Authority check

Category - ABAP

How and where are the permissions for a Core Data Service delimited? Check out this article for the details.

05/20/2022