skip to Main Content

Introducing Core Data Services (CDS) in SAP Cloud Application Programming Model (CAPM) – Part 2 Decoding CDS Query Language (CQL)

In my previous blog we talked about the different languages and notations in the Core Data Services (CDS). In this part today, we will look at what CDS Query Language(CQL) is;  how many types of CQL there are, and the basic syntaxes of CQL. 

What is SAP CAPM CQL?

CDS Query language(CQL) within the  SAP Cloud Application Programming Model (CAPM) is a Standard Query Language (SQL) for connecting to databases. Using SQL you can interact with the database like CRUD (Create, Read, Update, Delete) operations, for example. It allows you to get the data from database tables using GET call; you can update the existing data in database table records using PATCH call, or insert new records into the database table using POST call. You can also delete records from a table using DELETE call, and perform a variety of other SQL operations.

Let us begin by exploring what Core Query Notations (CQN) are.

CQN specifications are used to capture queries as plain JavaScript objects. By using the query builder APIs you can use respective objects directly in your code.

As you can see in the example below, I added the user understandable query (select, insert, delete, update) in our code. Once the query is compiled, it is automatically converted to a capm internal query structure (plain javascript object). 

What is a Core Expression Notation (CXN)?

CXN is used to capture expressions as plain JavaScript objects. It is a part of the query that will also convert user expression to expression notation. The queries can be any one of the below.

expr = // one of…

  val       |   // [literal values]: #literal-values

  ref       |   // references or functions

  xpr        |   // operator expressions

  func      |   // function calls

  param  |   // binding parameters

  sym    |   // enum symbol

  SELECT     // subqueries

let exp = cds.parse.expr

CDS QL is based on standard SQL, which it enhances in different ways : 

Using CDS QL you can execute different types of queries. Go about selecting a few columns in the table , joins (LEFT OUTER JOIN), ‘where’ clauses etc.

Postfix Projections : CQL supports the put projections like :

      Normal SQLSELECT name, address.street from Authors;

                   CQl projection SQL : SELECT from Authors { name, address.street };

      It also support “Smart * Selector” like SELECT from Books { *, author.name as author };

Path Expressions  :  Use path expressions to navigate along with associations 

and/or construct elements in any of the SQL clauses. With path expressions in ‘from’ clauses, you can fetch entries from a target entity that are associated to a parent entity. 

Path expressions also support all other clauses like ‘where’ clauses, left outer joins, etc.

Casts in CDL : There are two different constructs commonly called casts.

      Normal SQL :  SELECT cast (foo+1 as Decimal) as bar from Foo; 

                   CDL projection SQL : SELECT from Foo { foo+1 as bar : Decimal };

Excluding Clause: Use the excluding clause in combination with SELECT * to 

select all elements except for the ones listed in the exclude list.

Ex: SELECT from Books { * } excluding { author };

 Output : In the books table results exclude the author column.

Query-Local Mixins: It is used to mixin localization Associations using mixin 

and into a clause.

Ex: SELECT from Books mixin {

  localized : Association to LocalizedBooks on localized.ID = ID;

} into {

  ID, localized.title

};

Note: This is only supported in postfix notation.

 

Ravi Krishna Thota is a Developer based out of Mindset’s Hyderabad office. He's been a front-end developer in the SAP space for over 3 years now and has spent the last 1 year as a NodeJS back-end developer with a strong focus on SAP Fiori. Ravi is extremely passionate about learning all things new in the SAP spectrum. When he is not working, you will usually find him hanging out with his friends or lounging in front of the TV.

Back To Top