This article is about providing some samples for CAP Queries, and the context to execute them. Let me mention a small comparison to querying in ABAP at the beginning.

Working with Open SQL and with ABAP SQL for decades was very convenient in ABAP. I cannot tell the same for CAP now. While in ABAP there was one database schema You never explicitly have to mention it anywhere. The database interface covers everything in SAP Netweaver, and You have a single/central global type definition pool called DDIC (Data Dictionary) available in ABAP programs, all as part of a robust safe system, but it is not designed for dynamic scalability.

In contrast in the cloud everything is a resource/service including the database, usually a HANA DB Container holding the tables required for the application. In case You need another data defined by another CAP application from another DB container, You make it accessible using synonyms, You cannot simply select from it.

A CAP application has a db folder including a schema.cds file, and based on that the DB tables are generated automatically, and deployed to a HDI container (HANA DB Container). Another pretty stuff, that You can also define and implement native HANA artifacts like Calculation Views in BAS to use capabilities of SAP HANA and deploy it directly to the HDI Container w/o defining it in the schema.cds.
Of course You might want to use them at later point of time to fetch data from those calculation views, therefore You can refer to it in the schema.cds with some special annotations. The aim of this article is to present the syntax to access such entities in CAP service implementations, and to demonstrate some useful queries and notations. Note the dynamic DB access in template literals, creepy right ? This is also not like in ABAP where You have the where-used-list.

This is a growing list, more will come.

Counting

Count All Records (*)

const { POListCached } = cds.entities("CatalogService");

let errorCount = await SELECT.one.from(POListCached).columns(["COUNT(*) as count"]).where(`CC_STATUS <> 'OK'`);

console.log( errorCount.count.toString() );

Count by columns

let duplicatesQuery = cds.parse.cql(`SELECT COUNT(DTR_EBELN_EKKO) AS COUNT, DTR_EBELN_EKKO, DTR_EBELP_EKPO FROM CONTRACT_ALL GROUP BY DTR_EBELN_EKKO, DTR_EBELP_EKPO ORDER BY COUNT DESC LIMIT 10`);

let top10PossibleDuplicateRecords = await cds.run(duplicatesQuery);

Built-in functions with parameter list in template strings

await cds.run(`UPDATE DATA_CONTRACTDECISION SET MODIFIEDBY = 'JOB' , MODIFIEDAT = CURRENT_UTCTIMESTAMP , MESSAGE = '' , STATUS = ? , CONTRACTREQUEST = ? WHERE CONTRACTNUMBER = ?  AND CONTRACTITEMNUMBER IN (` + groupedContractItemNumbersToStrings + `)`, [CONTRACT_SAP_POSTING_STATUS.SUCCESS, res.Reqnr, conNumber]);

Share this content: