Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
priyanka_sadana2
Explorer
(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.
8 Comments