Loop through multiple rows in ABAP CDS by AMDP

If you have worked with ABAP CDS long enough, there is no function to really loop through multiple records and use them for a calculation of a row or certain field. While ABAP CDS syntax still does not offer such functions till today, there are few functions available in ABAP Managed Database Procedures(AMDP) table functions from which ABAP CDS can consume the calculated.

Exercise

We will create a CDS view that returns all the business partners and concatenate their business partner roles(customer, vendor, etc) into one field.

Create table function

Table function must define the fields structure required. The actual data fetching will not be done in the table function object but in the ABAP class that is triggered. So the ABAP class and the method must be defined in table function as well.

@EndUserText.label: 'table function'
define table function ZTBL_FUNCTION
  with parameters
    @Environment.systemField: #CLIENT
    p_clnt :abap.clnt,
    @Environment.systemField: #SYSTEM_LANGUAGE
    p_lang :abap.lang
returns
{
  client            : abap.clnt;
  salesdoc          : vbeln_va;
  mergefunctiontext : abap.char( 1000 );
}
implemented by method
  ZCL_TableFunction=>Get_Data;

Note that if you are using system fields in the SQL to fetch the data, I suggest to define them as parameters. This is because in the subsequent ABAP class where the SQL Script is performed, certain system fields are not available. If they are defined as parameters in table functions, they can be used in the SQL script. We will demonstrate that with client and system language fields.

Create ABAP class to fetch table function data

Class name and method must match the ones defined in table function. Few key points to mentions:

  • INTERFACES if_amdp_marker_hdb is added to the public section
  • Get_data method must define what database is used, script language, and the tables used in the script
  • Use RETURN statement along with SQL to return the data to table function ztbl_function.
  • Most importatntly, the SAP script is client dependent, so it must specify the client in SQL otherwise it will fetch the data from all clients in instance.

Few things to mention on the logic:

  • bpfunctionconcat will return the “BP function code ( description ) ” for easier readability
  • vbpa.posnr = ‘000000’ excludes all sales document that has BP specifically set for sales item
  • As function text is language dependent, tpart.spras must be specified with system language therefore using p_langu defined in the table function.
CLASS zcl_tablefunction DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS:
      get_data  FOR TABLE FUNCTION ztbl_function.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS zcl_tablefunction IMPLEMENTATION.
  METHOD get_data
   BY DATABASE FUNCTION
    FOR HDB
    LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
    USING vbak vbpa tpart.


*    Get the sales header BP functions
    it_data = select DISTINCT
                     vbak.mandt    as client,
                     vbak.vbeln    as salesdoc,
                     vbpa.parvw    as bpfunction,
                     tpart.vtext   as bpfunctiontext,
                     concat(concat(concat(cast(vbpa.parvw as "$ABAP.type( parvw )" ), '('), tpart.vtext), ')') as bpfunctionconcat
                from vbak
               inner join vbpa
                  on vbak.mandt = vbpa.mandt
                 and vbpa.vbeln = vbak.vbeln
                 and vbpa.posnr = '000000'
               inner join tpart
                  on tpart.mandt = vbpa.mandt
                 and tpart.parvw = vbpa.parvw
                 and tpart.spras = :p_lang
               where vbak.mandt = :p_clnt;

        RETURN select client,
                      salesdoc,
                      string_agg(bpfunctionconcat, ', ' ORDER BY bpfunctionconcat) as mergefunctiontext
                 FROM :it_data
                GROUP BY client,
                      salesdoc;

  ENDMETHOD.
ENDCLASS.

Consume table function

Create a CDS view to consume the table function. Pass the system fields client and language. 

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Consume table function'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity ZTBL_FUNCTION_CONSUME 
  as select from ZTBL_FUNCTION( p_clnt : $session.client, p_lang : $session.system_language )
{
  salesdoc,
  mergefunctiontext      
}

Preview this CDS in SE16n and you can see that mergefunctiontext returns all the business partner and it’s text in one single line.

By ABAP