CDS – creating a table and a view – Part 1

Hello, In this blog post, I will showcase how to create a table and a view via the Core Data Services (CDS).

In my example I will be using the HCP trial landscape and the Web Development Workbench editor.

1) Create a CDS file ( .hdbdd extension)

2) Add some (correct) content and activate the file

3) Go to your schema and open the tables folder to see the newly generated table.

4) Perform a select statement – as expected, initially your table will be empty

5) Right click on the table, select “Generate Insert”   or type it (if you want to make spelling mistakes) 🙂

6) Your generated insert statement is opened on a new tab. then, you can save it as a .sql file (selct a location on your package)

7) I opened the sql file, copied and pasted into the SQL console so i could run it. Notice console output successful insert message, “Rows affected 1” after I ran my insert statement.

8) after I was able to insert my statement, then I went back to my select statement and I ran it again to verify my record was in fact inserted. As a result, you can see my first record in the table

9) After a few inserted records, we can see the table again with more rows populated.

10) Since I have a table, then I want to create a simple view. I went back to my CDS file and I declared a view. Then I re-activated my file.

11) Once the hdbdd file was activated, then I went back to my Catalog and opened the Views folder where my newly created view was saved.

12) Once again, I did a right click on the view and generated a select statement.( I avoid typing my SQL as much as possible so that I do not end up having to type it over and over before I get the correct spelling 🙂 but up to you to try to get the name right the first time.

13) My select statement was generated

14) After the statement was generated I ran it (I changed it a little but the one above should have returned the same number of records since I only have 6 on my example. See output below

* Be careful if you are doing this on a table with a lot of records as you may select *

Thins to remember about CDS:

1) Stored procedures and sequences cannot be created in CDS (as of SP10)

2) CDS files may contain one or more entities or views and when it is activated, your entities, views, etc are directly created as design time objects on your schema and are ready for use.

3) HCP trial ( in your Web Development Workbench) is very helpful for learning how to use CDS ( as you may also use HANA Studio)

Things to forget:

1) Forget to re-run SQL statements when promoting your code from one environment (such as dev) into another environment (such as QA or Prod). CDS plays very well with HALM during migrations.

2) Forget to think you are alone on this journey, you have an entire SCN group behind you

3) (I forgot 3 – i am working on the next exercise on my Workbench, aren’t you?)

Again, this is a very simple document explaining how to create a table and a view via CDS. Please share your experience or struggles with CDS. Happy programming. For a more advanced scenario, please read part 2 of this blog where I showcase a view with a 1 to 1 association on a table

Here are some helpful links I used while writing this blog:

create a CDS document

create a CDS view