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 –
- Create a table function and expose the required fields which can be used to expose in further views.
- Create the implementing class for table function.
- Implement the class method as an AMDP function and return the required tabular data from tables with joins using SQLSCRIPT.
- 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
Created Table function ZSYTEST_TF will look like below :
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 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
- ABAP on SAP HANA. Part I. First Program in ABAP HANA
- ABAP on SAP HANA. Part II. ADT Eclipse and HANA Studio
- ABAP on SAP HANA. Part III. Debugging in ADT
- CDS Part 1. Core Data Services – Introduction
- CDS Part 2. Core Data Services – Deep Dive
- ABAP on SAP HANA. Part VI. New Age Open SQL ABAP 740
- ABAP on SAP HANA. Part VII. SQL Script and SAP HANA Stored Procedure
- ABAP on SAP HANA. Part VIII. ADBC – ABAP DataBase Connectivity
- ABAP on SAP HANA. Part IX. AMDP – ABAP Managed Database Procedure
- ABAP on SAP HANA. Part X. AMDP with SELECT OPTIONS
- ABAP on SAP HANA. Part XI. Are Native SQL and Open SQL Competitors?
- ABAP on SAP HANA. Part XII. Open SQL, CDS or AMDP, which Code to Data Technique to use?
- ABAP on SAP HANA. Part XIII. Sample Functional Specification of HANA Project
- ABAP on SAP HANA: Part XIV. HANA Ready, HANA-tization & HANA Plus
- CDS Part 3. Expose CDS Views as OData Service through Annotation
- ABAP on SAP HANA: Part XVI. HANAtization
- ABAP on SAP HANA: Part XVII. ATC – ABAP Test Cockpit Setup & Exemption Process
- SAP ABAP on HANA: Part XVIII. SALV IDA (Integrated Data Access)
- ABAP for SAP HANA. Part XIX. Sample Technical Specification of HANA Project
- ABAP for SAP HANA. Part XX. ALV Report On SAP HANA – Opportunities And Challenges
- 4 Useful Tips on ABAP and ABAP on HANA
- Associations in HANA β A Conceptual Approach
- CDS Part 5. ABAP CDS Views With Authorization
- ABAP for SAP HANA. Part XXII. How to Consume Native HANA Views Using ADBC?
- CDS Part 6. Basic Expressions & Operations Available for CDS View – I
- 115 ABAP for SAP HANA Interview Questions & Answers
- CDS Part 7. Basic Expressions & Operations Available for CDS View β II
- CDS Part 8. Usage of Built-In Functions in CDS – I
- CDS Part 9. Usage of Built-In Functions in CDS β II
- CDS Part 10. Usage of Built-In Functions in CDS β III
- CDS Part 11. How to Consume CDS View in Smart Business Service KPI Fiori Apps?
- CDS Part 12. Useful 6 CDS Related Tools in ADT
- CDS Part 13. Key Definition in CDS Views
- CDS Part 14. ABAP Annotations for Translatable Texts in CDS Views
- CDS Part 15. Associations in CDS Views – I
- ABAP for SAP HANA – Part 23. How to Access Database Schema Dynamically Using AMDP?
- Video Course – New Features and Syntaxes in SAP ABAP 7.40+ with Exercises
- CDS Part 16. Usage of Built-In Functions in CDS β IV
- βABAP for SAP HANAβ Points to Remember
- Limitations of Open SQL Compared to SQL Script in HANA
- CDS Part 17. How to Overcome GUID Mismatch Linking Problem in ABAP CDS?
- Curious Case of ATC – Priority 2 – Low Performance Operations on Internal Tables
- ABAP for SAP HANA β Part 24. Custom Report Using AMDP
- ABAP on HANA – A Mini Guide to your Certification Path
- How to Append Standard Tables having Replacement Objects in S/4HANA
- CDS Part 19 β Finding Week of the Year in CDS Views
- CDS Part 20 β Virtual Elements (ABAP Functions) in CDS
- CDS Part 21 β CDS View Finder Tool
- ABAP for SAP HANA β 25 – GUID Mismatch: Table Function & AMDP as the Savior
- ABAP for SAP HANA β 26 β New way of ABAP Programming in HANA
- CDS – 26: Analyzing CDS with Analytical Annotation and Manipulating its Filter Options
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.
Well explained. Great content. Thank u Akash for this wonderful article
Nicely written article , thank you for sharing :-).
Thank you Madhu