In this article, we would dive a little deeper on CDS View from an ABAPer’s perspective.
By now we know, there are two components of CDS Views in HANA.
DDL SQL View : It is read-only classical database view which is visible in ABAP Dictionary (SE11). It cannot be edited in SE11.
CDS View Entity: It is the DDL Source File and the actual CDS View. It is a Database Object which is visible in Eclipse/HANA Studio/ADT and we cannot view CDS View Entity in SE11. It covers the CDS Database view and makes other attributes possible, such as authorization checks defined in CDS view.
Before I show, how CDS View is created in HANA ADT, let me start with, how CDS View can be deleted.
Question: Do we need to delete both the Dictionary DDL SQL and CDS View individually?
Answer: No.
Question: Can we delete DDL SQL to delete the CDS View?
Answer: No.
Check the below image, I am trying to delete the DDL SQL which is created when CDS View is created.
HANA does not allow me to delete this independently. ‘Generated DDL SQL views cannot be deleted’.
So we are left with CDS View entity. And you guessed it right. Check the below images, we can delete CDS View entity.
Question: What happens to DDL SQL View when CDS View (DDL Source) is deleted?
Answer: They are twins. They cannot be separated even by death.
DDL SQL is automatically deleted when the CDS View is deleted. Check the image below, both are deleted in one go.
Now, let us see how we can create a CDS View. There are ample tutorials available on this topic. We would be short and would show what is new, in the below image.
In all other tutorials, you would see that DDL Source is below Dictionary. In our image above, check it is below Core Data Services folder. HANA and SAP are evolving at great pace. We need to keep up with their pace.
The above images are self-explanatory. Let us pause at the final step of the wizard. As of now, SAP is kind enough to provide six templates for creating the CDS View as per our need. ABAPers feared they might not be able to learn SQL and remember the syntaxes. Look, SAP already thought for us. ABAPers are not going anywhere.
In most of the tutorials on CDS View in other blogs, you might have seen only first 5 templates. You would find the sixth template “Define Table Function with Parameters” now. SAP and HANA innovation team are really fast (like their in-memory system) . When you actually get a chance to make your hand dirty in HANA ADT, do not be surprised if you find more that 6 templates.
Let us select the first template Define View and hit Finish button.
Here system expects us to christen our DDL SQL View Name. We also need to provide the data_source_name (i.e the table or view from where data would be selected). As pointed out in the previous article, it is a good idea to separate SQL View Name and actual CDS View Name. For consistency, we name SQL View Name with DDLS and CDS View with CDSV. You might have a different naming convention in your project.
For our example the SQL View Name is YDDLS_WO_STAT and CDS View is YCDSV_WO_STATUS.
Food for thought for all ABAPers
What is the maximum length of the name which we can give to the SQL View Name?
Look at the first four auto-generated lines. They precede with “@”. They are called “Annotation”.
Additional information and properties can be specified to the CDS Views using Annotations. For example @ClientDependent annotation lets us set whether the CDS View is Client Dependent or not. In the above example, it is client dependent (by default). Annotations also can be used for specifying the Buffer Status (switched on/off) and Buffer Type (single/generic/fully) of the CDS View.
Annotations enrich the data models with additional (domain specific) metadata.
In layman’s words, Annotations are extensions of CDS to SQL.
Annotation “AbapCatalog.sqlViewName” is mandatory for the definition of a Core Data Services (CDS) view.
Also, check the Outline window section in the left side corner. It shows the CDS views breakups. source data table / view, CDS View key and field list.
Now, let us try to open the CDS View entity in SE11.
Oops, it is not meant for Data Dictionary.
Open the DDL SQL CDS View in SE11.
No problem to view it. We can even display the data pulled by the view.
Transports for CDS View
Ok, while creating the CDS View, it asked for the transport where we wanted to save our generated objects. What do you think, did both DDL SQL View and CDS View entity get saved in that transport? Or do you think otherwise?
Let us check it for ourselves.
You can see, only the CDS View entity is saved in the transport.
All change objects and transports are managed in the ABAP layer end to end. We do not need to go to the lower underlying database (HDB) level to manage the transport of CDS entities/artifacts.
Join in CDS View
While creating the new CDS View, let us select the Define View with Join template. As discussed, we need to type our ABAP Dictionary (DDL) SQL View name. In addition, we need to replace the auto-generated data_source_name and joined_data_source_name along with its element names.
For our example, we have joined the Status table and Status text. Join is the same as we have been doing in ABAP.
Check the output of the Joined CDS View.
Parameters in CDS View
ABAPers are familiar with the term Parameter. Just like we can have PARAMETERs in the selection screen of a report, similarly we can have Parameters on CDS Views. Do not be too optimistic, we do not have SELECT OPTION in CDS View till now.
We know, Parameter helps to filter the data and is useful in WHERE Clause.
CDS View with Parameters is normally created to filter the data during selection process at database level itself (i.e additional filtration is done using CDS View with Parameters). So, there is no need to put additional filtering (where condition) at ABAP Layer. Code to Data shift (one of the motivations of Core Data Services).
Let us see how we can define a CDS View with Parameter.
Choose the template Define View with Parameters and provide the DDL SQL View name (data dictionary) and data source name as done in above examples. In addition to that, provide the parameter name and parameter type. We can have multiple parameters in a CDS View, separated by a comma.
Check the usage of parameters in the above image. If we define CDS View with multiple parameters separated by a comma, we can have multiple parameters in the WHERE Clause separated by AND/OR. Also, note that “$” sign needs to be provided with parameters while using it in WHERE Clause.
with parameters p_stat: j_status,
p_lang: spras
WHERE jcds.stat = $parameters.p_stat and tj02t.spras = $parameters.p_lang;Les us see the DDL SQL View (data dictionary) for this CDS View with Parameter and try to display the content output for this CDS View.
Opps. Data display for views with parameters is not yet supported. Hopefully, SAP would come up with this option too in near future.
There are other templates like View with Association, Extend View and Table Function with Parameters. We can cover them some other day.
You might also like to check Debugging in SAP HANA Studio/ADT/Eclipse.
Usage of CDS View in ABAP Programs
The last thing we want to cover today is how to consume a CDS View in ABAP Program.
We can use the CDS View like any other table or data dictionary view in ABAP. I found the usage of CDS View with Parameters little tricky. Please check the below code snippet for usage of CDS View with Parameters. Let me accept up front that the below program does not show the real power of CDS View. It is only for demonstration.
SELECT * FROM ycds_wo_stat_txt_para( p_stat = @p_status ) INTO TABLE @i_wo_status.You would notice below that “@” symbol is used for escaping of host variables. They help to identify ABAP work areas/variables/constants in Open SQL statement. Literals need not be escaped using “@”. If we decide to escape one host variable, all host variables should be escaped.
Also, we can select from both DDL SQL View and CDS View. So, we need to declare the internal tables/work areas according to the View you intend to use. Although DDL SQL View and CDS View are mirror images still you cannot use the TYPE statement interchangeably in the program.
Question: In our previous article, we suggested that SE11 Data Dictionary DDL SQL View should not be normally used. Why?
Answer: If we consume DDL SQL View in ABAP SELECT statement, then, it will act as any other normal view/table which is created in data dictionary using SE11. We would not be taking real advantage of HANA. We would not see the performance improvement. Theoretically, when the DDL SQL View is used, a database connection from ABAP Layer to Database Layer is established and this process would consume some resources for database connection (even though your database in HANA).
Question: Why is it good practice to use CDS View Entity (DDL Source) while using ABAP SELECT statement?
Answer: By now we have a fair idea that CDS View Entity (DDL Source) is a database object which is known to ABAP Layer and does not exist in data dictionary (SE11). This database object contains SQL power and resides at the database layer. Consumption of CDS View by DDL Source name invokes Database Object which is residing at Database Layer i.e., SQL inside the DDL Source Name at Database layer (DDL Source). This way, we can execute an SQL without creating a database connection between ABAP Layer and Database. Only results will be transferred back to ABAP layer. This will save resources for creating a database connection from ABAP Layer to Database Layer.
I would like to request HANA Experts to provide some more insight and justification of using CDS View Entity (DDL Source) in SELECTs.
Finally, the program to show usage of CDS View with Parameter.
*&---------------------------------------------------------------------*
*& Report YCDS_WO_STATUS_REPORT
*&---------------------------------------------------------------------*
*& Description: Demo to fetch data from CDS View with Parameter
*& Note: This program is just to show how CDS View can be used.
*& This does not depict the true strength (Core to Data Paradigm) of CDS
*&---------------------------------------------------------------------*
REPORT YCDS_WO_STATUS_REPORT.
*--------------------------DATA DECLARATION----------------------------*
DATA: i_wo_status TYPE STANDARD TABLE OF ycds_wo_stat_txt_para. " CDS Entity View
* DATA: I_WO_STATUS TYPE STANDARD TABLE OF YDDLS_WO_STAT_PA. " DDL SQL View (DDIC)
*&---------------------------------------------------------------------*
*&SELECTION SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK a01 WITH FRAME TITLE text-001.
PARAMETERS : p_status TYPE j_status. " Status
SELECTION-SCREEN END OF BLOCK a01.
*&---------------------------------------------------------------------*
*& START OF SELECTION.
*&---------------------------------------------------------------------*
START-OF-SELECTION.
* Select data using CDS View with Parameter
PERFORM sub_get_data_from_cds.
*&---------------------------------------------------------------------*
*& END OF SELECTION.
*&---------------------------------------------------------------------*
END-OF-SELECTION.
* Display data
PERFORM sub_display_data.
*&---------------------------------------------------------------------*
*& SUB ROUTINES
*&---------------------------------------------------------------------*
FORM sub_get_data_from_cds.
* Fetch from CDS Entity View
SELECT * FROM ycds_wo_stat_txt_para( p_stat = @p_status ) INTO TABLE @i_wo_status.
* Fetch from DDL SQL data dictionary View
* SELECT * FROM YDDLS_WO_STAT_PA( P_STAT = @P_STATUS ) INTO TABLE @I_WO_STATUS.
ENDFORM.
FORM sub_display_data.
DATA:
lv_status_rel TYPE j_status VALUE 'I0002', " Release Status
lr_functions TYPE REF TO cl_salv_functions, " ALV Functions
lr_alv TYPE REF TO cl_salv_table, " ALV Functions
lr_display TYPE REF TO cl_salv_display_settings, " ALV Functions
lv_salv_msg TYPE REF TO cx_salv_msg. "ALV Functions.
* Display the final internal table in ALV
IF i_wo_status IS NOT INITIAL.
TRY.
* Factory Method
cl_salv_table=>factory( IMPORTING r_salv_table = lr_alv
CHANGING t_table = i_wo_status ).
CATCH cx_salv_msg INTO lv_salv_msg.
MESSAGE lv_salv_msg TYPE 'E'.
ENDTRY.
* Self explanatory
lr_functions = lr_alv->get_functions( ).
lr_functions->set_all( abap_true ).
lr_display = lr_alv->get_display_settings( ).
lr_display->set_striped_pattern( cl_salv_display_settings=>true ).
lr_display->set_list_header( text-001 ).
* Actual Diplay
lr_alv->display( ).
ELSE.
MESSAGE 'No data found' TYPE 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
