Subject: SAP-HANA-Live | SAP Domain
Core Data Services (CDS) Views in SAP HANA Live provide a powerful and flexible way to model real-time data for analytics and reporting. Sometimes, the data requirements call for more complex queries, such as filtering or aggregating data based on the results of another query. This is where subqueries become essential.
This article explains the concept of subqueries within CDS Views, their syntax, usage patterns, and best practices when working with them in the SAP HANA Live environment.
A subquery is a query nested inside another query. It allows you to use the results of one query to filter, compare, or join data in the outer query. In CDS Views, subqueries enable developers to:
CDS Views support subqueries primarily in the WHERE clause, SELECT list, or within WITH expressions. The common types are:
Fetch an additional aggregated value per record:
define view ZCDS_SalesWithMaxPrice as select from vbak {
key vbeln,
erdat,
netwr,
( select max(netwr) from vbak as max_sales where max_sales.erdat = vbak.erdat ) as MaxNetValuePerDate
}
Here, for each sales order, the maximum net value for the order date is retrieved via a scalar subquery.
Filter data based on the presence of related records:
define view ZCDS_OrdersWithItems as select from vbak where exists (
select from vbap as items where items.vbeln = vbak.vbeln and items.posnr = '000010'
) {
key vbeln,
erdat,
netwr
}
This query returns only sales orders that have an item with position number '000010'.
Filter records based on a list from a subquery:
define view ZCDS_OrdersForVIPCustomers as select from vbak where kunnr in (
select kunnr from kna1 where vip_flag = 'X'
) {
key vbeln,
kunnr,
netwr
}
This CDS View selects sales orders for customers marked as VIP.
WITH ClauseFor complex scenarios, you can define common table expressions (CTEs) with WITH to break down logic:
define view ZCDS_OrdersWithHighValueItems
with HighValueItems as (
select vbeln from vbap where netpr > 1000
)
select from vbak where vbeln in (select vbeln from HighValueItems) {
key vbeln,
erdat,
netwr
}
This approach improves readability and modularizes subquery logic.
WITH clauses to organize complex logic.Subqueries extend the power of CDS Views by allowing nested queries that enable more advanced filtering and data retrieval scenarios. In the context of SAP HANA Live, they empower real-time analytics with complex, business-specific data models. Understanding when and how to use subqueries effectively ensures robust, maintainable, and performant CDS Views.