Varad
I attempted to demonstrate Open SQL’s limitations in relation to SQL Script in my most recent piece. Today, I want to draw attention to a CDS constraint and provide a quick workaround. One issue with ABAP CDS Views is that you can’t join tables together if the GUIDs aren’t the same, that is, if one GUID is declared as CHAR and the other as RAW.
CRMD_PARTNER has PARTNER_NO defined as CHAR(32), while BUT000 has PARTNER_GUID defined as RAW(16). The mismatched join fields will cause an error and prevent the view from activating when you attempt to combine these two tables together in an ABAP CDS view. This limitation stems from the HANA SQL query language.
GUID is what we’ll be using for the demonstration. Any field with a mismatched data type could be the problem. And everyone would benefit from our modification.
Making another TYPE of view to handle the join is an easy way to get around this problem. What I’m talking about is an ABAP Dictionary View.Yes, the CDS View can be saved via the standard ABAP Dictionary View.
Dictionary views, as opposed to ABAP CDS views, use Native SQL, which enables the joining of data types that aren’t compatible for GUIDs.
Dictionary views don’t need source code entries, hence there isn’t a DDL source file connected with them because they are built within the SAP GUI editor (ABAP Perspective of HANA Studio or in regular GUI).
Several restrictions on dictionary views
Joins can only be made between tables.
Only INNER JOIN can be used to join them.
There is only one use per table in the view because you cannot alias table names.
No grouping or computation is feasible.
The aforementioned restrictions make it clear that these views must be kept basic, which is appropriate given our goal, which is to merely establish a connecting view between two tables so that it can be used with other, more intricate ABAP CDS views.
Dictionary views can be made using transaction code SE11 directly in the SAP GUI or via the ABAP perspective in SAP HANA Development Studio.
To create in SAP HANA Development Studio, take the following actions:
- Visit the ABAP viewpoint.
- Launch the package that needs the view added to it.
- Expand the Folder Dictionary >> Views.
- Right-click the name of the “Views” folder.
- Choose “New Dictionary View.”
- Alternatively
- Visit the ABAP viewpoint.
- To add the view, right-click on the package and select
- Choose “Other ABAP repository object” by clicking New.
- Slide open the Dictionary folder.
- Choose Dictionary View, then Click Next.
- Next
6. You’ll see the screen that follows.
You can construct two different kinds of views: External View and Dictionary View.In this instance, dictionary views are the only thing that matter.
7. Enter the view name desired and select Dictionary View
- The transaction code SE11 maintenance screen appears. From there, you may choose the relevant tabs to add tables, join criteria, fields to output, and selection criteria to apply.
Alternatively, the following procedures can be used to construct the view directly within the SAP GUI.
- Put in the transaction code SE11.
- After entering the view name, select Create.
After entering a description, specify the tables and necessary join requirements.
4. Fill out the required basic fields. For a link table, these should only include the CLIENT and the two GUIDs that are used in the join.
You can add more fields if you’d like, but remember that the main purpose of this view is to serve as a connecting table, so simplicity is preferable.
The type mismatch between the GUIDs will be displayed in the activation log, along with the following cautions.
After making all the necessary entries, save, activate, and designate to a transport.
We can disregard the alert.The database view is now available.
See Also: Complete SAP ABAP on HANA Tutorials.
As of right now, the following view can be used to join CRMD_PARTNER with BUT000 inside of an ABAP CDS view:
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>
}
Take a good look at the database view in the sample above. By serving as the intermediary between the two CDS Views, the database view has successfully solved the issue of joining CRMD_PARTNER direct with BUT000 with GUID mismatch (data element mismatch). Additionally, as it is a CDS View, it allows for the possible use of LEFT OUTER JOIN between the tables.
