Core Data Services (CDS) views are central to modeling and consuming data efficiently within the SAP HANA ecosystem, especially in SAP-HANA-Live, which relies heavily on CDS to expose real-time operational data. While basic CDS views are powerful, advanced SQL features such as UNIONs and CASE statements empower developers to build sophisticated, flexible, and business-ready data models. This article explores how to leverage these techniques to enhance CDS views for complex reporting and analytical scenarios.
The UNION operation combines the result sets of two or more queries into a single result set, eliminating duplicate rows by default. It is useful when data is spread across multiple tables or views that need to be presented in a unified format.
In CDS, unions can be defined using the union construct within view definitions. This allows combining multiple select statements into one CDS view.
define view ZSalesAndReturns
as union
select from ZSales {
sales_order_id,
sales_date,
amount,
'Sale' as transaction_type
}
union
select from ZReturns {
return_order_id as sales_order_id,
return_date as sales_date,
return_amount as amount,
'Return' as transaction_type
}
Key Points:
transaction_type helps distinguish row origins.The CASE statement is a conditional expression that allows you to create new calculated fields based on conditions — similar to IF-THEN-ELSE logic.
CASE statements enable:
case
when condition1 then result1
when condition2 then result2
else default_result
end
define view ZSalesPerformance
as select from ZSales {
sales_order_id,
amount,
case
when amount > 10000 then 'High'
when amount between 5000 and 10000 then 'Medium'
else 'Low'
end as sales_category
}
These techniques can be combined for sophisticated models, such as unifying datasets from multiple sources with conditional classification:
define view ZCombinedTransactions
as union
select from ZSales {
sales_order_id,
sales_date,
amount,
'Sale' as transaction_type,
case
when amount > 10000 then 'High Value'
else 'Standard'
end as value_category
}
union
select from ZReturns {
return_order_id as sales_order_id,
return_date as sales_date,
return_amount as amount,
'Return' as transaction_type,
'Return' as value_category
}
Mastering advanced CDS view techniques like UNIONs and CASE statements greatly enhances the flexibility and power of SAP-HANA-Live data models. These capabilities allow SAP professionals to deliver unified, categorized, and business-ready datasets that drive better analytics and reporting with minimal overhead. Embracing such advanced modeling unlocks the true potential of SAP HANA’s in-memory processing and real-time data access.