ABAP for SAP HANA – 25 – GUID Mismatch: Table Function & AMDP as the Savior

3
23697
CDS Table Function

SAPYARD has already provided a solution in CDS part 17 for joining two tables where one key is a GUID of RAW(16) type and another one is of char(32) type. Previously we have resolved this problem with the help of an old approach i.e using a DB view( when no one does the job, backend is always there for you πŸ™‚ ).

Meanwhile, SAP always tries to enhance its design day by day and came up with SAP HANA then we got AMDP and now we have a way to use table function created natively in database layer in our beloved CDS view( which now seems to be a solution for everything ) .

First let’s get to know our new friend for today –

table fUNCTION

Table function is not new to the database layer but it is certainly new to the application layer design cycle. Table functions are built using SQLScript and offer a lot of flexibility to have simple and complex logic. We can relate table function to a data source that can return tabular values. One thing we should note here that one table function can only return a single tabular data set .

It was introduced with ABAP 7.50, previously AMDP used to support only procedure without any return value but now with AMDP we can return tabular data set using which we are going to implement CDS table functions.

So without further ado lets resolve GUID mismatch issue using table function :

Lets start with the steps that we are going to follow here –

  1. Create a table function and expose the required fields which can be used to expose in further views.
  2. Create the implementing class for table function.
  3. Implement the class method as an AMDP function and return the required tabular data from tables with joins using SQLSCRIPT.
  4. Our CDS table function function is ready to consume . Create another view to consume the created table function.

Before proceeding further, I would highly recommend to go through the CDS and AMDP tutorial available in SAPYARD.

Now lets start ABAPer’s favorite part – the actual development

Repeating the problem statement : BUT000 has PARTNER_GUID defined as RAW(16) and CRMD_PARTNER has PARTNER_NO defined as CHAR(32).Β These two tables needs to be joined but can not be joined simply in CDS view because of type mismatch and we can’t use CAST as it is not supported for RAW data type.

Step 1. Creation a table function.

Open ADT -> Select system -> Package -> New ->Core data service -> Data definition -> Provide name -> Chose “Define table function with parameters” template

Table function template available in ADT

Created Table function ZSYTEST_TF will look like below :

Annotation @Environment.systemField: #CLIENT to specify the parameter as client

Code:

@EndUserText.label: 'Table function demo'
define table function ZSYTEST_TF
with parameters
@Environment.systemField: #CLIENT
client : mandt
returns {
Client : mandt;
// Fields from CRMD_PARTNER
PARTNER_NO : crmt_partner_no;
PARTNER_FCT : crmt_partner_fct;
// ------extra fields-------- //

// Fields from BUT000
PARTNER_GUID : BU_PARTNER_GUID;
TYPE :bu_type ;
// ------extra fields-------- //
}
implemented by method zcl_sytest_tf=>join_but000_crmd_partner;

Note : We are directly dealing with DB layer now so client has to be specified explicitly as parameter.

Specify the fields to be exposed and their respective data types.

Specify the class name and the class method to return the values.

Step 2nd and 3rd : Creation of class and implementing the method .

Package -> New -> Source code library -> ABAP Class

Created class ZCL_SYTEST_TF will look like below :

Class spinet
CLASS zcl_sytest_tf DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .

PUBLIC SECTION.
INTERFACES : if_amdp_marker_hdb.
CLASS-METHODS join_but000_crmd_partner FOR TABLE FUNCTION zsytest_tf.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_sytest_tf IMPLEMENTATION.
METHOD join_but000_crmd_partner BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING but000 crmd_partner.
RETURN
SELECT a.client as client,
b.partner_no as partner_no,
b.partner_fct as partner_fct ,
a.partner_guid as partner_guid,
a.TYPE as TYPE
FROM but000 AS a
INNER JOIN crmd_partner AS b ON a.client = b.client and
a.partner_guid = b.partner_no
where a.client = :client ; 

ENDMETHOD.
ENDCLASS.

Kindly note the “BY DATABASE FUNCTION” and ” READ-ONLY ” both are must as table function can be used only to read data . The client parameter is same as we passed while creating table function . Add if_amdp_marker_hdb and class method defined in table function as a public parameter to class.

We can now directly execute the table function to see the data and validate that the join is working but our solution is not completed. Till this step we had our starters, now its time to have the main course and use the newly create table function further in higher layers.

You can use this in composite or consumption view same as you use a normal basic view. Here I have created a simple basic view fetching the data from our table function .

Step 4 : Using the created table function in CDS views .

New-> Core data service -> Data definition -> Provide name -> Select view template -> Finish

Created view will look like below :

Code :

@AbapCatalog.sqlViewName: 'ZSYTESTIF'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #BASIC
@EndUserText.label: 'cds view to use table function'
define view zsytest_use_tf
as select from ZSYTEST_TF(client : $session.client)
{
// Fields from CRMD_PARTNER
PARTNER_NO,
PARTNER_FCT,
// ------extra fields-------- //
// Fields from BUT000
PARTNER_GUID ,
TYPE
// ------extra fields-------- //
}

Notice the client parameter is passed to the table function.

This way we can resolve many such issue and also table function can be used directly as reusable forms and even can call other function too.

Hope this tutorial gave you an insight to our new concept “CDS Table function” and it will help you someday somewhere in your project. πŸ™‚

Please follow our LinkedIn Page, LinkedIn Group, Facebook Page, Twitter and Instagram.

Save our number +1-646-727-9273 and send us a Whatsapp message ‘LEARN’ to be part of our Learning Community.

Also, Check ABAP Programming for SAP HANA Tutorials

3 COMMENTS

  1. Can you please also let us know the way by which we can return the table from AMDP implementation of table function? I am sorry, I am new to the CDS View.

LEAVE A REPLY

Please enter your comment!
Please enter your name here