CDS creating a table and a view – part 2
First of all, thank you for reading part 2 of my blog on CDS. For a basic example, please read part 1 of this blog.
On part 2, I wanted to expand into creating another table with an association. On this post, I will explain how to create a simple 1 to 1 Association, however, CDS can further support associations such as 1.. many, etc.
1) On my new table HCCAsugEvent below, I created a column EventPresenter with an association back to HCCAsugPresentations (my first entity). Notice that the key on my HCCAsugPresentations will become my key for the associations. I did not have to explicitly state it but it was is understood on my next step.
2) I went back to the workbench Catalog and opened the SQL Console. I ran a Select query to verify the table was indeed created.
3) After the table was created, I inserted a few records (no picture of the insert, but it was easily done by right clicking on the table and then selecting “Generate insert”. One nice thing about the workbench I discovered during the exercise is that I can double click on the table and its definition will be shown. I knew about this feature from HANA Studio but it was also nice to see it on the workbench (Thanks SAP teams!)
My next step was to create a view for the 2 tables I created. Initially, I created a select query with an inner join. I knew this is a way to do it in SQLScript, but I wanted to also achieve this from my CDS view involving at least 2 tables.
Next is my approach to create a similar view via CDS. The great thing about creating an association via CDS and trying to do a JOIN on my view is that my entity knows about the association so that I do not need to invoke my associated table. The column that has the relationship knew about the association and it lets me callt he columns from the other table via its COLUMN_ON_TBL.COLUMN_FROM_ASSOCIATED_TBL property as shown below. I added at least one more column from my joined table just to showcase the easiness of creating views via CDS. I am becoming a fan!
Here is the output
Thank you for your time reading this blog and feel free to post your questions or share your thoughts about your CDS experiences.
Here are some helpful links I used while writing this blog:
as per request… someone asked me how to expose this view or data model via xsodata , here is how on my next blog post. Good luck on your scenario!