ABAP CDS View: Issue faced on join of tables on columns where one column is of RAW data type

[priyanka_sadana2]

(This blog will provide the issues faced and the solution when joining tables in CDS views)

 

ABAP CDS views provide the capability to join tables on linked columns. For example, a table employee_details and company_details can be joined based on company_ID that exists in both the tables. But following issues are faced while performing join:

1) Both columns have different data types:

First solution that will come to your mind is to cast one of these columns as the data type of the other.

Yes, this solution is correct but this will not work in the below mentioned second scenario.

2) Both columns have different data type and one of these is of RAW type

Let’s learn the issue with an example.

We will take two tables, ztemployee and zlogdata.

• Both the tables have a linked column on which a join can be performed.


• Name of that column in ztemployee is DB_KEY and in zlogdata is OBJECTID.


• Data type of DB_KEY is RAW 16 and of OBJECTID is CHAR90.

 



 

Let’s first create a CDS view on table ztemployee:

@AbapCatalog.sqlViewName: 'ZBASEVIEW'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE



define view Z_BASEVIEW

  as select from ztemployee

{

  mandt      as client,

  db_key     as DB_KEY,

  emp_id     as EmployeeId,

  emp_salary as EmployeeSalary

}

 

Now create a CDS view(Z_I_JOINVIEW) to join ZBASEVIEW that contains fields from ztemployee and zlogdata

@AbapCatalog.sqlViewName: 'ZIJOINVIEW'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'join on cndtn that compares string & raw'



define view Z_I_JOINVIEW as select from Z_BASEVIEW association [0..*]  to zlogdata as _data on $projection.DB_KEY = _data.objectid { 

DB_KEY, 

EmployeeId, 

EmployeeSalary, 

/* Associations */ 

_data    

}

Z_I_JOINVIEW does not get activated and throws the error because the data types of both the linked columns are different

Now try to cast column DB_KEY

@AbapCatalog.sqlViewName: 'ZBASEVIEW'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE



define view Z_BASEVIEW

  as select from ztemployee

{

  mandt                               as client,

  cast (db_key as abap.sstring(1033)) as DB_KEY,

  emp_id                              as EmployeeId,

  emp_salary                          as EmployeeSalary

}

 

As we can see in the below image, casting on RAW type is not allowed in CDS view

A table function implemented in AMDP is the solution to this issue.

Step 1: Create a table function that is implemented by an ABAP Managed Database Procedure.

Method implemented in the AMDP allows the join on two linked fields that have different data types.

Table functions follows the same conventions as they are defined for corresponding BASIC, COMPOSITE or CONSUMPTION private views. Hence, it should be annotated with @VDM.private: true

The client parameter defined with the keyword ‘with parameters’ is the importing parameter for the ABAP class method.

Fields defined under the keyword ‘returns’ are consumed by the CDS view on the top of table function

@ClientHandling: {

    type: #CLIENT_DEPENDENT,

    algorithm: #SESSION_VARIABLE

}

@VDM: {

    private: true

}

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Table function to join two tables'

define table function zp_join_table_tf

  with parameters

    @Environment: {

        systemField: #CLIENT

    }

    P_SAPClient :abap.clnt

returns

{

  Client         : abap.clnt;

  DB_KEY         : /bobf/conf_key;

  EmployeeSalary : dec15;

  ObjectClass    : cdobjectcl;

}

implemented by method

  zcl_join_class=>z_join_table;

Step 2: Implement AMDP: create an AMDP and implement the method to join the two tables

Note: It is necessary to add the interface 'if_amdp_marker_hdp' to the declaration of this class to get the capabilities of AMDP. Mention the name of all the tables (required in select statement) after the keyword ‘USING’ and return the selected fields to the table function using the keyword ‘RETURN’.

Join is performed on tables ztemployee and zlogdata on fileds DB_KEY and OBJECTID in the method z_join_table. Here, the join does not throw the error unlike in CDS view where join on fields with different datatypes is not allowed.

CLASS zcl_join_class DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS z_join_table FOR TABLE FUNCTION zp_join_table_tf.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.



CLASS zcl_join_class IMPLEMENTATION.

  METHOD z_join_table

  BY DATABASE FUNCTION FOR HDB

            LANGUAGE SQLSCRIPT

            OPTIONS READ-ONLY

            USING ztemployee zlogdata.

    RETURN

    SELECT

           a.mandt AS client,

           a.db_key,

           a.emp_salary AS employeesalary,

           b.objectclas AS objectclass

           FROM ztemployee AS a

           LEFT OUTER JOIN zlogdata AS b ON a.mandt = b.mandt AND

           a.db_key = b.objectid;

  ENDMETHOD.

ENDCLASS.

 

Step 3: Consumption of Table function in CDS view

Here $session.client is passed to the parameter P_SAPClient of the table function zp_join_table_tf and required fields are fetched.

@AbapCatalog.sqlViewName: 'ZIJOINTABLE'

@AbapCatalog.compiler.compareFilter: true

@ClientHandling.algorithm: #SESSION_VARIABLE

@AccessControl.authorizationCheck: #NOT_REQUIRED

@VDM.viewType: #BASIC

@EndUserText.label: 'View top of tablefunction to join tables'



define view zi_join_table

  as select from zp_join_table_tf(P_SAPClient :$session.client)

{

  client,

  DB_KEY,

  EmployeeSalary,

  ObjectClass

}

Execute the CDS view zi_join_table (Note: it is the result of a left outer join; it can be changed depending upon the requirement)

Hence, whenever there is a requirement to join two tables on linked columns via CDS views and the columns have different data types then cast one of these columns. If one of these columns have RAW data type then join these in a method implemented in AMDP implementation and return the required fields to a table function. Further consume that private table function in a CDS view.