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

[Juwin]

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...


• https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/a924ee1e98ab435a874efa32e6...

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/04e64a8ffffe49b8a8a5469796...