Skip to Content
Author's profile photo Jerry Wang

My CDS view self study tutorial – Part 6 consume table function in CDS view

Let’s try to resolve one real issue now. What we want to achieve is: in CRM we need a CDS view which returns the service order guid together with its Sold-to Party information, “Title” ( Mr. ) and “Name” ( blGMOUTH ).

/wp-content/uploads/2016/03/clipboard1_915658.png

The title and Name information are stored on table BUT000, while Service order transactional information is maintained in table CRMD_PARTNER, which has a field PARTNER_NO ( CHAR32 ) linking to table BUT000’s PARTNER_GUID ( RAW16 ).

/wp-content/uploads/2016/03/clipboard2_915665.png

/wp-content/uploads/2016/03/clipboard3_915666.png

It is not allowed to do join on these two fields since their data type are not equal. This question is asked via this SCN thread: ABAP CDS View: join tables on columns of different type .

As suggested in the Correction Answer, this issue could be resolved by using CDS Table Function. Here below are the detail steps.

/wp-content/uploads/2016/03/clipboard5_915667.png

1. Create a new table function

/wp-content/uploads/2016/03/clipboard6_915668.png

@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function ztf_BP_DETAIL
  with parameters @Environment.systemField: #CLIENT
                  clnt:abap.clnt
  returns { client:s_mandt;
            partner_guid:BU_PARTNER_GUID;
            partset_guid:CRMT_OBJECT_GUID;
            partner_no: CRMT_PARTNER_NO;
            bp_guid: BU_PARTNER_GUID;
            title:AD_TITLE;
            name: BU_NAME1TX;
          }
  implemented by method
    zcl_amdp_bp_detail=>crmd_partner_but000;

With keyword “with parameters”, the client parameters is defined which works as the importing parameters for the ABAP class method zcl_amdp_bp_detail=>crmd_partner_but000. The keywords “returns” defines available fields which could be consumed by other CDS entities.

For further information about AMDP ( ABAP Managed Database Procedure ), please refer to this document Implement and consume your first ABAP Managed Database Procedure on HANA or this blog An example of AMDP( ABAP Managed Database Procedure ) in 740 .

2. Create a new AMDP implementation

Create a new ABAP class zcl_amdp_bp_detail by copying the following source code:

CLASS zcl_amdp_bp_detail DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .
  PUBLIC SECTION.
  INTERFACES if_amdp_marker_hdb.
  CLASS-METHODS crmd_partner_but000 FOR TABLE FUNCTION ztf_bp_Detail.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.
CLASS zcl_amdp_bp_detail IMPLEMENTATION.
METHOD crmd_partner_but000
        BY DATABASE FUNCTION FOR HDB
        LANGUAGE SQLSCRIPT
        OPTIONS READ-ONLY
        USING crmd_partner but000.
    RETURN SELECT sc.client as client,
                  sc.partner_guid as partner_guid,
                  sc.guid as partset_guid,
                  sc.partner_no as partner_no,
                  sp.partner_guid as bp_guid,
                  sp.title as title,
                  sp.name1_text as name
                  FROM crmd_partner AS sc
                    INNER JOIN but000 AS sp ON sc.client = sp.client AND
                                              sc.partner_no = sp.partner_guid
                    WHERE sc.client = :clnt AND
                          sc.partner_fct = '00000001'
                    ORDER BY sc.client;
  ENDMETHOD.
ENDCLASS.

Here in line 30 the two columns of CRMD_PARTNER and BUT000 are joined. The importing parameter is used in SQLScript source code by adding a “:” before the variable name. The hard code “00000001” means the constant value for partner function “Sold-to Party”.

/wp-content/uploads/2016/03/clipboard8_915669.png

3. Consume the created table function in CDS view

@AbapCatalog.sqlViewName: 'zcpartner'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'partner detail'
define view Z_c_partner as select from crmd_orderadm_h
inner join crmd_link as _link on crmd_orderadm_h.guid = _link.guid_hi and _link.objtype_hi = '05'
  and _link.objtype_set = '07'
inner join ztf_bp_detail( clnt: '001') as _bp on _link.guid_set = _bp.partset_guid
{
  key crmd_orderadm_h.guid,
  --_link.objtype_hi as header_type,
  --_link.objtype_set as item_type,
  _bp.bp_guid,
  _bp.partner_no,
  _bp.name,
  case _bp.title
    when '0001' then 'Ms.'
    when '0002' then 'Mr.'
    when '0003' then 'Company'
    when '0004' then 'Mr and Mrs'
    else 'Unknown'
  end as title
}

Please note that the created table function in step1 could be directly consumed just as a normal CDS view, see example in line 8.

Since the table function declares client as parameter, so when consumed, I put the current client id 001 into it. The fields defined as the returning parameters in table function could be used in consuming view.

/wp-content/uploads/2016/03/clipboard9_915670.png

4. Test the whole solution

Click F8 on the view z_c_partner, check whether data previews as expected.

/wp-content/uploads/2016/03/clipboard10_915674.png

or you can also check against single data record, by clicking “SQL Console”, maintain the SQL and click Run:

/wp-content/uploads/2016/03/clipboard11_915675.png

The same test could also easily be done on ABAP side:

/wp-content/uploads/2016/03/clipboard12_915676.png

/wp-content/uploads/2016/03/clipboard13_915677.png

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi ,

      i have a requirement to join two table (tabel1 key is char type and table2 type is RAW type). So i tried to implement the Table defination as shown by your blog.

      but i am not able to create Table difination (1st step) since getting syntax error like "Unexpected word table ; the keyword View is expected ". could you please help me in this ?

      Author's profile photo Arshad Ansary
      Arshad Ansary

      Hi Vinuta,

      This CDS Table Function  are only available as of ABAP 7.5 . Please check your abap server version

      BR

      Arshad

      Author's profile photo Dmitrii Sharshatkin
      Dmitrii Sharshatkin

      Hi Jerry,

      In your procedure you write: sc.partner_no = sp.partner_guid.

      Do i get it right, that you let your database to do all needful calculations and conversions?

      If this is the case what is the difference to if we create a normal DDIC view in SE12?

      I have done a simple ABAP DDIC view joining GUID16 and CHAR70 and it seems to work fine on SAP HANA. But view activation gave some warning and i'm wondering, what would be the right approach?

      Thanks, Dima

      Author's profile photo Former Member
      Former Member

      This is a very nice solution to my question, many thanks!

      Author's profile photo Former Member
      Former Member

      Hi. This is very informative. I need a small help with this example. Here only the client information is passed as a parameter. My question is , if i have a CDS view calling a table function, then how to pass the parameter from CDS function into the CDS tale function with out hard coding.

      Here from CDS view , table function is invoked as Ztf_bp_detail(clnt: '001'), If want want to pass 2 parameters from CDS view into the table function ( with out hard coding) and pass those parameters to the class for fetching the result. How this can be done. Need your help on this.

      Thanks for your help.

      Regards

      Ibrahim

       

      Author's profile photo Vijay Simha Surabhi
      Vijay Simha Surabhi

      Hello Ibrahim,

      Hope you might got the clarification.

      I recently visited this blog in self-learning process, this is the reason could be a late response to your question. I tried the same scenario, CDS view with paramters without hardcoding. and below is my solution.

      Author's profile photo Santhosh Kadiyala
      Santhosh Kadiyala

      Hi Jerry Wang,

      I know that with CDS view using parameters option, we can extract the records from the HANA passing the required parameter as an input. however, if my requirement is to extract the records based on the list of values - how do i achieve this using CDS using Table Functions ?

       

      i have been looking for blogs on this but not getting it. Can you please do the needful.

       

      thanks,

      Santhosh

      Author's profile photo Philipe de Oliveira
      Philipe de Oliveira

      Hi, Santhosh.

      I have the same requirement. Did you get any solution?

      Author's profile photo Ravivarman Palanisamy
      Ravivarman Palanisamy

      Hi Jerry Wang

      I have consumed a CDS view with Table Function in another CDS view for my Fiori Element report, but I am getting an error while transporting the changes to quality system.

      DDL Source <Consumer View> could not be activated
      (E- The data source "ZCDS_COBRB_PTR_TF<CDS with Table Function>" does not exist or is not active )

      i know we dont have a SQL view created for this table function, can you please let me know how you will transport the view consuming the CDS with Table function.

      P.S. It is kind of urgent to me, so please help as soon as possible.

      Author's profile photo Udita Saklani
      Udita Saklani

      HiJerry Wang ,

       

      we have created a DDIC table in pur custom namespace that begins with a '/' . Is it allowed to use this table in the method of the Class being implemented for the table function CDS?

       

      Thanks,

      Udita Saklani