CDS Views: retrieve a field from table joins

[tafkap95]

Hi,

I am creating a CDS view in which I would like to retrieve a field that comes from the join of several tables and I am faced with the limit of CDS views or I do not know how to use them well.

Here is an extract from my CDS (very simplified):

AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Demo'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{ serviceQuality: #X,
 sizeCategory: #S,
 dataClass: #MIXED }

define view entity Z_cds_entity_demo
as select from ZI_MaintenanceNotification_Ext as _MaintenanceNotification
association [0..1] to I_Material as _Material on $projection.Material = _Material.Material
{
 key _MaintenanceNotification.MaintenanceNotification,

 _MaintenanceNotification.Material,
 _MaintenanceNotification.MaintenanceOrder
}

From the MaintenanceOrder field I would like to be able to read the tables EBKN -> EKKN -> EKKO in order to retrieve in my CDS fields of EKKO, for example EKKO.

In classic ABAP it is simple, but with the mechanism of CDS View I do not know how to do it and especially if it is possible. I looked into the TABLE FUNCTION but apparently it is not possible to use it in an association or to send a parameter (AUFNR in my case).

Thank you in advance for your help.

[junwu]

don't know what you are talking

[tafkap95]

Oh dear, either I'm expressing myself badly or I missed something, at what point did you hang up?

[NTeunckens]

I believe you should check which CDS-views / Services are at your disposal in your SAP-System (S/4?) ...
We can't interprete what the structure of CDS-view "ZI_MAINTENANCENOTIFICATION_EXT" is.

From my point of view, I would suggest looking into the following Stdrd. CDS-views (on recent S/4HANA environments), in order of relevance :

• I_PurOrdAccountAssignmentAPI01
• I_PurOrdAccountAssignment
• I_PurchaseOrderAPI01
• I_PurchaseOrder
• I_MaintenanceOrder

If you would have the availability of "I_PurOrdAccountAssignmentAPI01" CDS, this would probably be ideal ... This CDS holds associations "_PurchaseOrder" and "_PurchaseOrderItem" and there is a Parameter "OrderID" coming from the association "I_PurOrdAccountAssignment"-CDS ...

So with some luck you would have a direct link in a SAP-stdrd CDS for your MaintenanceOrder as input ("OrderID" - "AUFNR") and the PurchaseOrder data ...

Please check the SAP-Help on CDS views related to Purchase Orders indicating your correct ERP-version ...

Hope this helps

Nic T.

[tafkap95]

Hi Nic, 

Thank you very much for your answer. Actually I understood that I needed to split the most complex parts into functional/technical units in order to call them in my current CDS under development, and it works very well, but it is tedious to do 😉

[Raifloresca]

Great explanation, @NTeunckens

[MehmetOztop]

Hi,

If you've sorted out the functional direction to join the tables, you can join them in your CDS view in a similar fashion to an SQL query in classic ABAP: ABAP CDS - SELECT, JOIN