Access HANA DB functions from ABAP CDS Views (based services)

Disclaimer:

Needless to say, this may not address all the issues faced by developers and there may be other ways also to address this requirement. So, as always, explore and learn….

Inspired by: 

• https://answers.sap.com/questions/12395917/abap-cds-equivalent-for-stringagg-function-from-ha.html
• https://blogs.sap.com/2017/12/12/concatenate-multiple-records-in-a-single-field-using-abap-cds-table-function/
• https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/a924ee1e98ab435a874efa32e6f0ae14.html

Challenge:

Some of the functions that may be needed at ABAP CDS level, are only available as HANA DB functions (most common I have seen is STRING_AGG) and the way described earlier, to access such functions was to declare an internal TABLE_FUNCTION as described in the blog above.

Table functions may not be helpful all the time, especially if the parameters can’t be passed in to the function.

Solution:

This solution only works for CDS based services. The solution implements virtual elements in a CDS View by using @ObjectModel.virtualElement annotation.

@AbapCatalog.sqlViewName: 'zcdjptca'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'cds annot test'
@OData.publish: true
define view zjptca
  as select from aufk
{
  key mandt,
  key aufnr,
      autyp,
      auart,
      werks,
      objnr,
      @ObjectModel.readOnly: true
      @ObjectModel.virtualElement
      @ObjectModel.virtualElementCalculatedBy: 'ABAP:ZCLJPTTF'
      cast('' as co_sttxt)                                                 as istatus,
      @ObjectModel.readOnly: true
      @ObjectModel.virtualElement
      @ObjectModel.virtualElementCalculatedBy: 'ABAP:ZCLJPTTF'
      cast('' as co_asttx)                                                 as estatus
};

Here, the 2 virtual fields that are added to the view calculates Internal & External statuses of the Order object.

Class definition:

class zcljpttf definition
  public
  final
  create public .

  public section.
    interfaces if_amdp_marker_hdb.
    interfaces if_sadl_exit .
    interfaces if_sadl_exit_calc_element_read .
  protected section.
  private section.
    types: begin of ty_objnr,
             objnr type j_objnr,
           end of ty_objnr,
           tyt_objnr type standard table of ty_objnr,

           begin of ty_status,
             objnr   type j_objnr,
             istatus type char255,
             estatus type char255,
           end of ty_status,
           tyt_status type standard table of ty_status.
    class-methods get_statuses
      importing value(it_objnr)  type tyt_objnr
                value(iv_mandt)  type sy-mandt
                value(iv_langu)  type sy-langu
      exporting value(et_status) type tyt_status.
endclass.

class zcljpttf implementation.
  method get_statuses by database procedure for hdb language sqlscript options read-only using jest jsto tj02t tj02 tj02t tj30 tj30t.
    lt_ijest = select jest.objnr, replace(cast(string_agg(txt04, ', ' order by jest.stat desc) as nvarchar(255)),',','') as istatus
                      from jest
                      join tj02
                        on jest.stat = tj02.istat
                       and substr(jest.stat,1,1) = 'I'
                      join tj02t
                        on tj02.istat = tj02t.istat
                       and tj02t.spras = :iv_langu
                     where jest.objnr in ( select objnr from :it_objnr )
                       and inact = ''
                       and tj02.nodis = ''
                     group by jest.objnr;
    lt_ejest = select jest.objnr, replace(cast(string_agg(txt04, ', ' order by tj30.stonr desc) as nvarchar(255)),',','') as estatus
                      from jest
                      join jsto
                        on jest.mandt = jsto.mandt
                       and jest.objnr = jsto.objnr
                      join tj30
                        on jest.mandt = tj30.mandt
                       and tj30.stsma = jsto.stsma
                       and tj30.estat = jest.stat
                      join tj30t
                        on jest.mandt = tj30t.mandt
                       and tj30t.stsma = jsto.stsma
                       and tj30t.estat = jest.stat
                       and tj30t.spras = :iv_langu
                      where jest.objnr in ( select objnr from :it_objnr )
                        and inact = ''
                      group by jest.objnr;
    et_status = select distinct inp.objnr, istatus, estatus
                      from :it_objnr inp
                      left outer join :lt_ijest int
                        on inp.objnr = int.objnr
                      left outer join :lt_ejest ext
                        on inp.objnr = ext.objnr;
  endmethod.

  method if_sadl_exit_calc_element_read~calculate.
*Called after selection from CDS view is completed and is only called if fields are requested by the frontend

*Call calculation method, this can be a HANA procedure too  
    me->get_statuses( exporting iv_mandt = sy-mandt iv_langu = sy-langu it_objnr = corresponding #( it_original_data )
                      importing et_status = data(lt_status) ).
                      
*Pass data back                      
    loop at it_original_data assigning field-symbol(<lfs_row>).
      assign ct_calculated_data[ sy-tabix ] to field-symbol(<lfs_nrow>).
      assign component 'OBJNR' of structure <lfs_row> to field-symbol(<lfs_objnr>).
      if sy-subrc eq 0.
        read table lt_status into data(lwa_status) with key objnr = <lfs_objnr>.
        if sy-subrc eq 0.
          <lfs_nrow> = corresponding #( lwa_status ).
        endif.
      endif.
    endloop.
  endmethod.

  method if_sadl_exit_calc_element_read~get_calculation_info.
*Which fields are required for the calculation.  
    append 'OBJNR' to et_requested_orig_elements.
  endmethod.
endclass.

Execution:

When the CDS based service is called, SAP selects data from the CDS view first and then calls the exit classes to calculate additional fields. In the exit classes you may call additional methods and procedures to calculate the field and pass values back.

This allows the selection to be done based on any field on the view (except the virtual fields) and not just based on the parameters exposed like in case of a table function.

As mentioned above, This solution only works for CDS based services and not for ABAP selection (like general SELECT statements e.g., SE16N queries)

More information:

https://help.sap.com/viewer/cc0c305d2fab47bd808adcad3ca7ee9d/7.51.6/en-US/04e64a8ffffe49b8a8a5469796f397c0.html