📚 Understanding background for CDS SQL Basics
- 🔤SQL
- 🗃️Structured Query Language
- 📊There are three sublanguages of SQL:
-
🏗️Data Definition Language (DDL)⚙️CREATE TABLE, CREATE VIEW, ALTER TABLE, DROP TABLE
-
🔄Data Manipulation Language (DML)⚙️SELECT, INSERT, UPDATE, and DELETE
-
🔒Data Control Language (DCL)⚙️GRANT and REVOKE
-
⚠️ The Limitations of Classical Open SQL
⏳Open SQL ( < ABAP 7.40 SPO5 )
- ❌No fixed-values and computed columns
- ❌No CASE expression
- ❌No right outer joins
- ❌No UNIONs
- ❌No sub-queries in SELECT or FROM clauses
- ❌No access to SAP HANA built-in functions, for example. DAYS_BETWEEN ( )
- ❌No access to SAP HANA views and procedures
✨ New Open SQL ( > ABAP 7.40 SP05 )
- ➕Support arithmetic expressions with operators + - / *
- 🧮Arithmetic functions FLOOR, CEIL, MOD, ABS, DIV
- 🔄Support CASE expression
- ↔️Support right outer join
- ⛓️Support UNION and UNION ALL
- 📈The maximum number of tables supported in Open SQL JOIN clauses has been increased to 50
- 🔢The maximum number of sub-queries supported in an Open SQL statement has been increased from 9 to 50
- ⚡Access to SAP HANA built-in functions, for example, DAYS_BETWEEN ( )
- 👁️Access to SAP HANA views and procedures
⚠️ The Limitations of ABAP Dictionary Views
- 🔗Support only Inner Join
- 🧮Calculation expression, aggregation, grouping not supported
- 🔄Nested Views not supported
⚠️ The Limitations of ABAP Dictionary Views
- 🔗Support only Inner Join
- 🧮Calculation expression, aggregation, grouping not supported
- 🔄Nested Views not supported
🚀 Core Data Services (CDS)
📚Core Data Services are a collection of domain specific languages and services for defining and consuming semantically rich data model.
🔍 Semantics:
As attributes that help describe an underlying entity
🗂️ Data models:
Provide a standardized method for defining and formatting database contents consistently across systems, enabling different applications to share the same data
📝 Core Data Services are made of up 3 sub languages
1️⃣ Data Definition Language (DDL)
- 🔝Model and retrieve data on a semantic level higher than SQL
- 📈Extends native SQL means for increased productivity
2️⃣ Query Language (QL)
- 🔄Consume CDS entities via Open SQL in ABAP
- 🔍Fully transparent SQL extensions
3️⃣ Data Control Language (DCL)
- 🔒Define authorizations for CDS views
- 🏗️Modeled and declarative Approach
- 🤝Integrates with classical authorization concepts
🗂️CDS Related Repository Objects
✨With ABAP CDS, the following new repository object types were introduced:
Data Definition
- 📌 Also referred to as: DDL Source (for Data Definition Language, named after the DDL part of SQL)
- 🔍 Contains: The definition of either a CDS View or a CDS Table Function
- 👀 Display: Only in ABAP Workbench
- ⚙️ Editing: Requires the use of ABAP Development Tools (ADT in Eclipse)
Access Control
- 📌 Also referred to as: DCL Source (for Data Control Language, named after the DCL part of SQL)
- 🛡️ Contains: Definition of authorization rules that are automatically checked when a program accesses a CDS View or CDS Table Function
- 👀 Display: Only in ABAP Workbench
- ⚙️ Editing: Requires the use of ABAP Development Tools (ADT in Eclipse)
⚙️ Basic Features of ABAP CDS View
Database Independent
Use ABAP CDS View with any database supported by SAP
Advanced View Building
ABAP CDS views provide much more SQL features than classical dictionary views
Annotations
Add semantic information including:
- End user texts and currency keys
- Buffer settings
- Semantic information for consumers (BI analytics, OData, SAP UI5)
Implicit Authorization Checks
Define authorization rules for CDS objects
Associations Instead of Joins
Define relations between ABAP CDS View objects that will be translated into joins
CDS Table Functions
Views based on scripted coding
📜 Syntax Rules Inside DDL Sources
🔑 Keywords
Keywords must be all uppercase, all lowercase, or in lowercase with an uppercase initial letter. Mixed uppercase and lowercase is not allowed.
Examples:
Allowed: SELECT, Select, select
Not allowed: SeLect
💬 Comments
Two forward slashes (//) introduce a comment, which continues until the end of the line.
Comments within lines or that span multiple lines are enclosed by the characters /* and */
🏷️ Names
- Names are not case-sensitive
- A name can have a maximum of 30 characters
🔢 Literals
Number literals must always be specified in full and a decimal point (.) used as a decimal separator if necessary. Character literals are enclosed in single quotation marks (')
Examples:
Allowed: 1, 2.0, 0.5
Not allowed: .5
📛 Naming Rules
DDL Source name:
- Max 30 characters
- Always Upper-Case
CDS View name:
- Max 30 characters
- Not Case-sensitive
- Can be different from DDL Source name (not recommended)
🔤 SQL View name
- Max 16 characters
- Not Case-sensitive
- Different from CDS View name
📊 CDS View Example
Object:
Create a CDS View with KUNNR, NAME1, ORT01 and LAND1
@AbapCatalog.sqlViewName: 'ZBB_TEC_OSQL1'
@EndUserText.label: 'Simple cds'
Define view ZBB_TEC_OCDS1 as select from kna1
{
kunnr,
name1,
ort01,
land1
}
ABAP program to consume the data from CDS View:
REPORT zbb_tec_occds1.
SELECT * FROM ZBB_TEC_OCDS1 INTO TABLE @DATA(IT_KNA1).
CL_SALV_TABLE=>factory(
IMPORTING
r_salv_table = DATA(OB_SALV)
CHANGING
t_table = IT_KNA1
).
OB_SALV->DISPLAY( ).
🏷️ Annotations in CDS Views
Annotations are code snippets used to enable certain functions or to add component-specific metadata to a CDS view.
5 groups of annotations:
1. View Annotations
@AbapCatalog.sqlViewName: 'Z_CDS_SQL_EX2' @EndUserText.label: 'use of annotation in cds views'
2. Element Annotations
@Semantics.amount.currencyCode: 'local_currency_key' @Semantics.currencyCode: true
3. Parameter Annotations
@Environment.systemField: #SYSTEM_DATE
4. Extension Annotations
@AbapCatalog.sqlViewAppendName: 'Z_CDS_SQL_EX9'
5. Function Annotations
@ObjectModel.readOnly: true
Example on Annotations in CDS Views
@AbapCatalog.sqlViewName: 'ZBB_TEC_OSQL2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AbapCatalog.buffering.status: #ACTIVE
@AbapCatalog.buffering.type: #SINGLE
@EndUserText.label: 'Working with annotations'
define view ZSP_8PM_OCDS2 as select from bsik
{
bukrs,
belnr,
gjahr,
bldat,
budat,
dats_days_between(bldat, budat) as DUE_DAYS,
@Semantics.amount.currencyCode: 'WAERS'
dmbtr,
@Semantics.currencyCode: true
waers
}
💻 Program to consume the data from ABAP CDS
REPORT zbb_tec_occds2.
TABLES BSIK.
SELECT-OPTIONS S_BUKRS FOR BSIK-BUKRS.
SELECT * FROM zbb_tec_occds2 INTO TABLE @DATA(IT_BSIK)
WHERE BUKRS IN @S_BUKRS.
CL_SALV_TABLE=>factory(
IMPORTING
r_salv_table = DATA(OB_SALV)
CHANGING
t_table = IT_BSIK
).
OB_SALV->DISPLAY( ).
Note: This program demonstrates how to:
- Use selection screen parameters with CDS views
- Fetch data from CDS view zbb_tec_occds2
- Display results using SALV table
🔀 Syntax of CASE in CDS
Simple CASE Syntax:
CASE <Operand/element> When '<Value1>' Then '<Result1>' When '<Value2>' Then '<Result2>' ... Else '<Result N>' END AS <Alias name>
Complex CASE Syntax:
CASE WHEN <Operend1/Element1> = '<Value1>' and <Operend2/Element2> = '<Value2>' Then '<Result1>' WHEN <Operend1/Element1> = '<Value3>' and <Operend2/Element2> = '<Value4>' Then '<Result2>' ... ELSE '<Result N>' END as <Alias name>
Usage Notes:
- CASE expressions evaluate conditions and return a result
- Simple CASE compares one operand to multiple values
- Complex CASE can evaluate multiple conditions
- END AS clause assigns a name to the result column
- ELSE clause is optional but recommended
📦 CDS View with CASE Expressions
Object:
To display material numbers, material types, material groups and material type description
(Ex: MTART = 'ROH' → 'Raw material', MTART = 'HALB' → 'Semi finished product', MTART = 'FERT' → 'Finished product'...)
@AbapCatalog.sqlViewName: 'ZBB_TEC_OSQL9'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Working with CDS with CASE Expressions'
define view ZBB_TEC_OSQL9 as select from mara
{
key matnr,
mtart,
matkl,
meins,
case mtart
when 'ROH' then 'Raw material'
when 'HALB' then 'Semi finished product'
when 'FERT' then 'Finished product'
else 'Not classified'
end as MTYP_DES
}
Key Features:
- Uses CASE expression to translate MTART values to descriptions
- Includes standard material master fields (MATNR, MTART, MATKL, MEINS)
- Proper CDS view annotations for technical settings
- ELSE clause handles any unclassified material types
- Result column aliased as MTYP_DES for clear identification
💻 ABAP Program to consume the data from ABAP CDS
REPORT zbb_tec_occds9. SELECT * FROM zbb_tec_occds9 INTO TABLE @DATA(IT_MARA). CL_DEMO_OUTPUT=>DISPLAY( IT_MARA ).
Note: This program demonstrates:
- Simple data retrieval from CDS view zbb_tec_occds9
- Using CL_DEMO_OUTPUT for quick display of results
- Modern inline data declaration with @DATA
🔢 Built-in Functions for Arithmetic Expressions
| Function | Description | Example | Result |
|---|---|---|---|
abs(a1) |
Returns the absolute value | abs(-2.5) |
2.5 |
floor(a1) |
Rounds to next lower integer | floor(2.5)floor(-2.5) |
2 -3 |
ceil(a1) |
Rounds to next higher integer | ceil(2.5)ceil(-2.5) |
3 -2 |
round(a1, pos) |
Rounds to pos decimal places | round(4.9514, 2) |
4.95 |
CAST(operand AS target_type) |
Converts value to target type | CAST('123' AS abap.int4)CAST(price AS abap.dec(8,2)) |
123 (as integer) Formatted decimal |
Usage Notes:
- Target types can be predefined dictionary types (abap.int4, abap.char, abap.dec)
- Can also use any dictionary data element (e.g., LAND1_GP)
- Arithmetic functions work on numeric fields in CDS views
- CAST is particularly useful for type conversions in calculations
🔤 Built-in Functions for String Processing
| Function | Description | ABAP Equivalent |
|---|---|---|
concat(a1, a2) |
Returns concatenated string of type CHAR or STRING | CONCATENATE without SEPARATED BY |
replace(a1, a2, a3) |
Replaces all occurrences of a2 in a1 with a3 | REPLACE ALL OCCURRENCES OF |
substring(a1, pos, len) |
Extracts substring from position with length | substring() or direct substring access |
length(a1) |
Returns length as INT4 | num of char() |
💰 Built-in Functions for Currency and Unit Processing
| Function | Description | Return Type |
|---|---|---|
unit_conversion( |
Converts between units of measure | abap.quan |
currency_conversion( |
Converts between currencies | abap.curr |
📊 Example on Arithmetic and String Expressions in CDS Views
@AbapCatalog.sqlViewName: 'ZBBS_TEC_OSQL3'
@EndUserText.label: 'Working with built-in functions'
define view ZBBS_TEC_OSQL3 as select from bsik
{
bukrs as COMP_CODE,
belnr as ACC_DOC,
gjahr as FYEAR,
bldat as DOC_DATE,
budat as POSTING_DATE,
ABS(dmbtr) as ABS_VALUE,
FLOOR(dmbtr) as LOWER_VALUE,
CEIL(dmbtr) as HIGHER_VALUE,
ROUND(dmbtr, 2) as ROUND_VALUE,
dmbtr as AMOUNT_LC,
waers as CURRENCY,
currency_conversion(
amount => dmbtr,
source_currency => waers,
target_currency => cast('INR' as abap.cuky(5)),
exchange_rate_date => cast('20221220' as abap.dats)
) as TARGET_AMOUNT,
'INR' as TARGET_CURRENCY,
CONCAT(bukrs, belnr) as UNIQUE_ID,
LENGTH(CONCAT(bukrs, belnr)) as LENGTH,
SUBSTRING(bldat, 7, 2) as DOCUMENT_DAY
}
Key Features:
- Demonstrates arithmetic functions (ABS, FLOOR, CEIL, ROUND)
- Shows currency conversion with CAST for type safety
- Includes string operations (CONCAT, LENGTH, SUBSTRING)
- Uses meaningful aliases for all calculated fields
- Proper formatting for complex function calls
💻 ABAP Program to Consume Data from ABAP CDS
REPORT zbbs_tec_occds. SELECT * FROM zbbs_tec_occds INTO TABLE @DATA(IT_BSIK). CL_DEMO_OUTPUT=>DISPLAY( IT_BSIK ).
🧮 Aggregations in CDS Views
| Function | Description |
|---|---|
MIN(operand) |
Returns smallest value in operand |
MAX(operand) |
Returns greatest value in operand |
SUM(operand) |
Calculates the sum of values of operand |
AVG(operand) |
Calculates the average value of the values of operand |
COUNT(*) |
Returns the number of entries in the result set |
COUNT(DISTINCT operand) |
Returns number of distinct values of operand |
GROUP BY
All elements not defined using aggregate expressions must be specified after GROUP BY
HAVING
The HAVING clause is used to restrict the results returned by the GROUP BY clause
📊 Example on Aggregation Functions in CDS Views
@AbapCatalog.sqlViewName: 'ZSP_8PM_OSQL16'
@EndUserText.label: 'Working with aggregate functions'
define view ZBBS_TEC_OCDS16 as select from ekpo
{
matnr,
min(netpr) as MIN_VALUE,
max(netpr) as MAX_VALUE,
avg(netpr) as AVG_VALUE,
sum(netpr) as TOTAL_VALUE
}
group by matnr
Key Features:
- Demonstrates multiple aggregation functions (MIN, MAX, AVG, SUM)
- Groups results by material number (matnr)
- Uses meaningful aliases for aggregated values
- Fixed typo in the label ('Working' instead of 'Workign')
- Properly formatted GROUP BY clause
💻 ABAP Program to Consume Aggregated CDS View Data
REPORT zbb_tec_occds16. SELECT * FROM ZBB_TEC_OCDS16 INTO TABLE @DATA(IT_EKPO). CL_DEMO_OUTPUT=>DISPLAY( IT_EKPO ).
Program Details:
- Fetches aggregated data from CDS view ZBB_TEC_OCDS16
- Uses modern inline data declaration with @DATA
- Displays results using CL_DEMO_OUTPUT for quick viewing
- Works with the grouped material data (matnr) and aggregated values
🔄 SQL Join Types in CDS Views
⬅️ Left Outer Join
Picks all data from the left-hand side table, even when no match exists in the right-hand side table. Non-matching right side records will have NULL values.
➡️ Right Outer Join
Picks all data from the right-hand side table, even when no match exists in the left-hand side table. Non-matching left side records will have NULL values.
🔀 UNION and UNION ALL Operations
∪ UNION
The combined result contains no duplicate rows (distinct values only).
∪+ UNION ALL
The results are combined with all rows preserved, including duplicates.
Prerequisites:
- Same number of elements in both SELECT statements
- Elements in the same position must have compatible data types
- Elements in the same position must have identical names/aliases
📊 Example on UNION and UNION ALL in CDS Views
@AbapCatalog.sqlViewName: 'ZBB_TEC_OCDS6'
@EndUserText.label: 'Working with UNION and UNION ALL'
define view ZBB_TEC_OCDS6 as select from bsik
{
bukrs,
belnr,
gjahr,
bldat,
budat,
dmbtr
}
union // union all
select from bsak
{
bukrs,
belnr,
gjahr,
bldat,
budat,
dmbtr
}
Key Features:
- Combines data from BSIK (open items) and BSAK (cleared items) tables
- Shows both UNION (distinct) and UNION ALL (with duplicates) options
- Maintains identical field structure in both SELECT statements
- Properly formatted with clear field alignment
- Includes standard CDS view annotations
💻 ABAP Program to Consume CDS View
REPORT zbb_teh_occds6. SELECT * FROM zbb_teh_occds6 INTO TABLE @DATA(IT_RES). CL_DEMO_OUTPUT=>DISPLAY( IT_RES ).
📌 CDS View with Input Parameters
A CDS (Core Data Services) view with input parameters is a type of view in SAP HANA that allows users to define a reusable query that can accept input parameters at runtime. Input parameters can be used to filter data or restrict the output of a query to a specific subset of data that meets certain conditions.
Syntax:
Define View <CDS View name>
With parameters <parameter1> : <Data type>,
<parameter2> : <Data type>
Note: The data type must be either a data element or dictionary predefined type (abap.char, abap.cuky, etc.)
Note: In WHERE conditions, reference parameters with : or $parameters. prefix (e.g., :parameter1 or $parameters.parameter1)
Object:
Develop an ABAP CDS to display customer numbers, customer names, cities and countries based on given country
@AbapCatalog.sqlViewName: 'ZTEC_BBS_OSQL7'
@EndUserText.label: 'Working with CDS View with parameters'
define view ZBB_TEC_OCDS7
with parameters I_LAND1 : land1_gp
as select from kna1
{
kunnr,
name1,
ort01,
land1
}
where land1 = :I_LAND1
ABAP Program to Consume:
REPORT zBBS_TEC_occds7. PARAMETERS P_LAND1 TYPE KNA1-LAND1. SELECT * FROM ZBB_TEC_OCDS7( i_land1 = @P_LAND1 ) INTO TABLE @DATA(IT_KNA1). CL_DEMO_OUTPUT=>DISPLAY( IT_KNA1 ).
✨ Advanced CDS View with Multiple Parameters
@AbapCatalog.sqlViewName: 'ZBB_TEC_OSQL8'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Working with CDS View with parameters'
define view ZBBS_TEC_OCDS8
with parameters
I_TCUKY : abap.cuky(5),
@Environment.systemField: #SYSTEM_DATE
I_ERDATE : abap.dats
as select from bsik
{
bukrs,
belnr,
gjahr,
bldat,
budat,
dmbtr,
waers,
:I_TCUKY as TARGET_CURRENCY,
currency_conversion(
amount => dmbtr,
source_currency => waers,
target_currency => :I_TCUKY,
exchange_rate_date => $parameters.I_ERDATE
) as TARGET_AMOUNT
}
ABAP Program to Consume:
REPORT zBBS_8pm_occds8.
PARAMETERS: P_TCUKY TYPE BSIK-WAERS,
P_ERDATE TYPE SYDATUM.
SELECT * FROM ZBBS_8pm_ocds8(
i_tcuky = @P_TCUKY,
i_erdate = @P_ERDATE
) INTO TABLE @DATA(IT_BSIK).
CL_DEMO_OUTPUT=>DISPLAY( IT_BSIK ).
Key Features:
- Demonstrates multiple input parameters (currency key and date)
- Shows both parameter reference methods (
:and$parameters.) - Includes currency conversion using input parameters
- Uses system field annotation for default date value
- Corrected "TARGET_CURRECY" to "TARGET_CURRENCY"
- Proper parameter passing in ABAP program
🔗 Working with Associations in CDS Views
There are 3 types of associations:
- Ad-hoc associations
- Exposed associations
- Filtered associations
1️⃣ Ad-hoc Associations
The data definition that defines the association uses an individual field of the associated table or CDS view, are immediately translated into JOIN clause in the SQL create statement that is sent to the database.
Example:
@AbapCatalog.sqlViewName: 'ZSP_8PM_OSQL10'
@EndUserText.label: 'Working with Ad-hoc associations'
define view ZBBS_TEC_OCDS10 as select
from kna1 as a association to vbak as _b
on a.kunnr = _b.kunnr
{
a.kunnr,
a.name1,
a.ort01,
_b.vbeln, //ad-hoc association
_b.audat //ad-hoc association
}
ABAP Program to Consume:
REPORT zbbs_8pm_occds01. SELECT * FROM ZBBS_TEC_OCDS10 INTO TABLE @DATA(IT_RES). CL_DEMO_OUTPUT=>DISPLAY( IT_RES ).
2️⃣ Exposed Associations
Instead of addressing individual fields of the associated table or CDS View, it is also possible to add the entire association as a single element into the field list. This is called an exposed association.
Example:
@AbapCatalog.sqlViewName: 'Zbbs_8PM_OSQL11'
@EndUserText.label: 'Working with Exposed association'
define view Zbbs_8PM_OSQL11 as select
from kna1 as a association to vbak as _b
on a.kunnr = _b.kunnr
{
a.kunnr,
a.name1,
a.ort01,
_b //Exposed association
}
ABAP Program to Consume:
REPORT Zbbs_8PM_OSQL11.
SELECT KUNNR,
NAME1,
ORT01,
\_b-vbeln,
\_b-audat,
\_b-auart,
\_b-bukrs_vf
FROM ZSP_8PM_OCDS11
INTO TABLE @DATA(IT_RES).
CL_DEMO_OUTPUT=>DISPLAY( IT_RES ).
Consuming from Another CDS View:
@AbapCatalog.sqlViewName: 'ZBBS_8PM_OSQL12'
@EndUserText.label: 'Consume from exposed association CDS view'
define view ZSP_8PM_OCDS12 as select from Zbbs_8PM_OSQL11 as c
{
c.kunnr,
c.name1,
c.ort01,
c._b.vbeln, //path expression
c._b.audat //path expression
}
3️⃣ Filtered Associations
A filtered association in CDS View is an association between two entities that includes a filter condition to restrict the related records that are included in the association. This allows you to define a relationship between two entities that only includes a subset of the related records, based on specific criteria.
Example:
@AbapCatalog.sqlViewName: 'Zbbs_8PM_OSQL13'
@EndUserText.label: 'Working with filtered associations'
define view Zbbs_8PM_OSQL13 as select
from kna1 as a association to vbak as _b
on a.kunnr = _b.kunnr
{
a.kunnr,
a.name1,
a.ort01,
_b[auart = 'TA'].vbeln, //Filtered association
_b.audat,
_b.auart
}
ABAP Program to Consume:
REPORT Zbbs_8PM_OSQL13. SELECT * FROM Zbbs_8PM_OSQL13 INTO TABLE @DATA(IT_RES). CL_DEMO_OUTPUT=>DISPLAY( IT_RES ).
➕ Working with Extension of CDS View
Simple CDS View:
@AbapCatalog.sqlViewName: 'Zbbs_8PM_OCDS14'
@EndUserText.label: 'Simple cds'
define view Zbbs_8PM_OCDS14 as select from kna1
{
kunnr,
name1,
name2,
ort01,
land1
}
Extension of CDS View:
@AbapCatalog.sqlViewAppendName: 'Zbbs_8PM_OSQL15'
@EndUserText.label: 'Extension of cds view'
extend view Zbbs_8PM_OCDS14 with Zbbs_8PM_OSQL15
association to vbak as _B on kna1.kunnr = _B.kunnr
{
CONCAT(name1, name2) as FULL_NAME,
UPPER(name1) as UPPER_NAME,
_B.vbeln,
_B.audat,
_B.bukrs_vf
}
ABAP Program to Consume:
REPORT Zbbs_8PM_OCDS14. TABLES KNA1. SELECT-OPTIONS S_LAND1 FOR KNA1-LAND1. SELECT * FROM Zbbs_8PM_OCDS14 INTO TABLE @DATA(IT_RES) WHERE LAND1 IN @S_LAND1. CL_DEMO_OUTPUT=>DISPLAY( IT_RES ).
