Today, I would like to point out the limitation in CDS and a simple tweak to overcome it. ABAP CDS Views have a problem in that you cannot join tables together with mismatched GUIDs, that is to say where one GUID is defined as RAW and the other is defined as CHAR.
BUT000 has PARTNER_GUID defined as RAW(16) and CRMD_PARTNER has PARTNER_NO defined as CHAR(32). When you try to join these two tables together in an ABAP CDS View, it will give an error and the view will not activate due to the join fields being mismatched. This is a restriction imposed by HANA SQL query language used for ABAP CDS Views.
We have taken GUID for our demonstration. The issue can be with any field with data type mismatch. And our tweak would be applicable for all.
The simple solution to bypass this issue is to create another TYPE of View to handle the join. The type of view I am referring to is an ABAP Dictionary View. Yes, normal ABAP Dictionary View can come to the rescue of the CDS View.
Unlike ABAP CDS views, Dictionary Views use Native SQL, which allows joining of mismatched data types for GUIDs.
Dictionary views are created within the SAP GUI editor (ABAP Perspective of HANA Studio or in normal GUI), require no source code entries, so no DDL source file is associated with them.
Some limitations of dictionary views:
- Only tables can be joined.
- They can only be joined using INNER JOIN.
- You cannot alias table names, so each table can only be used once in the view.
- No calculations or grouping is possible.
As you can tell from the above limitations, these views need to be kept basic, and this is fine for the purpose we have, which is simply to create a linking view between two tables which can then be combined with other more complex ABAP CDS views.
Dictionary views are created in SAP HANA Development Studio under the ABAP perspective or directly in the SAP GUI using transaction code SE11.
If creating in SAP HANA Development Studio, follow the steps below:
1. Go to ABAP perspective
2. Open the package to which the view is to be added
3. Open the Dictionary >> Views folder.
4. Right click on the “Views” folder name
5. Select New Dictionary View
Or alternatively
1. Go to ABAP perspective
2. Right click on the package to add the view to
3. Click New and select “Other ABAP repository object”
4. Open the Dictionary folder
5. Select Dictionary view and Next
Next
6. The following screen will be displayed.
There are two types of view you can create; Dictionary View and External View. In our case we are only interested in Dictionary Views.
7. Enter the view name desired and select Dictionary View
8. The maintenance screen for transaction code SE11 is displayed, under which you can then select appropriate tabs to add tables, join criteria, the fields to output and the selection criteria to use.
Alternatively, the view can be created directly within the SAP GUI using the following steps.
- Enter transaction code SE11
- Enter the view name and click create
3. Enter a description, then define the tables and join conditions required.
4. Enter the basic fields required, for a link table these should simply be the CLIENT and the two GUIDs used in the join.
You can add further fields if desired but bear in mind, this view is primarily being created as a linking table, so keeping it simple is best.
5. Once all entries are made, save, activate and assign to a transport.
6. The activation log will give the following warnings, showing the type mismatch between the GUIDs.
We can ignore the warning. Our database view is ready.
7. Now we have a view that can be used for joining CRMD_PARTNER with BUT000 within and ABAP CDS view as follows:
<code>SELECT FROM CRMD_PARTNER AS PART
INNER JOIN ZVW_PARTNER_LINK AS LINK
ON LINK.CLIENT = PART.CLIENT
AND LINK.PARTNER_NO = PART.PARTNER_NO
INNER JOIN BUT000 AS B000
ON B000.CLIENT = LINK.CLIENT
AND B000.PARTNER_GUID = LINK.PARTNER_GUID
{
<desired fields>
}</code>Look at the database view closely in the above snippet. The database view has become the connecting link between the two CDS Views and has effectively overcome the problem of joining CRMD_PARTNER direct with BUT000 with GUID mismatch (data element mismatch). And because it is used in a CDS View, this enables use of LEFT OUTER JOIN between the tables if needed.
