Core Data Services (CDS) ABAP

CDS SQL

The Most Important Elements of the SELECT Statement

High-Level Syntax of CDS Views

DEFINE VIEW 
[<Parameters>]
AS SELECT FROM <Source>
[<Joins>]
[<Associations>]
{
  <Fieldlist>
}
[<WHERE Clause>]
[<GROUP BY Clause>]
[<HAVING Clause>]

Field List

  • Inside curly braces
  • Elements separated by commas
  • Field names either
    • From the source field, or
    • Assigned via AS
  • Key fields with KEY
  • Expressions to calculate the field

Example

@AbapCatalog.sqlViewName: 'ZSQL_MINIMALasdf'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Minimal CDS definition'
define view zcds_minimal as select from snwd_so {
    key client     as Client,
    key so_id      as SoId,
    created_at     as CreatedAt,
    changed_at     as ChangedAt,
    currency_code  as CurrencyCode,
    gross_amount   as GrossAmount,
    net_amount     as NetAmount,
    tax_amount     as TaxAmount,
    overall_status as OverallStatus,
    payment_method as PaymentMethod,
    payment_terms  as PaymentTerms
}

Expressions

An expression is a construct in many programming languages that can be evaluated according to given semantics in a certain context, meaning it produces a value.

In many formal languages, expressions are one of the most important constructs, if not the only one.

Wikipedia: Expression (Programming)

An SQL expression is something that provides a value at runtime.
At design time, the data type the expression will return is known.

Expressions in the Field List

  • Fields from source tables
  • Literals – fixed values, either strings in quotes or numbers
  • Session variables – accessed via $session.<name>
  • Aggregate expressions – MIN( ), MAX( ), AVG( ), SUM( ), COUNT([DISTINCT] )
  • Operator expressions – plus, minus, multiply, (divide)
  • SQL functions
  • CASE expressions
  • Path expressions

Expressions in the Field List – Examples

@AbapCatalog.sqlViewName: 'ZJB_EXPRESSIONS'
@EndUserText.label: 'SQL Expressions'
define view zjb_cds_expressions
  as select from zbc_users as u

  left outer join zjb_cds_aggregation as t
  on u.user_id = t.Assignee
{
  key u.user_id       as UserId,
      u.firstname     as Firstname,
      u.lastname      as Lastname,
      u.email         as Email,
      u.gender        as Gender,
      u.date_of_birth as DateOfBirth,
      u.changed_at    as ChangedAt,

      'EUR'         as CurrencyCode,      
      $session.user as CurrentUser,      
      concat_with_space( Left(u.firstname, 1), u.lastname, 1 ) as Name,      
      case u.gender 
           when 'F' then 'Ms '
           when 'M' then 'Mr '
           else '' end as Salutation,                  

      concat_with_space(
          concat_with_space(
              'User has', 
              cast( t.TaskCount as abap.char(11) ), 
          1 ),
          ' tasks',
      1 ) as CNT
}

Session Variables

Values from the ABAP SY structure

  • $session.user – SY-UNAME
  • $session.client – SY-MANDT
  • $session.system_language – SY-LANGU
  • $session.system_date – SY-DATUM
  • $session.USER_TIMEZONE
  • $session.USER_DATE

These variables are only filled when accessed from ABAP.

SQL Functions

New ones are added with almost every release.

  • Data type-specific
    • String functions
    • Date and time functions
    • Numeric functions
  • Conversion between data types

SQL Functions for Strings

The usual suspects...

SQL Function Argument 1 Argument 2 Argument 3
CONCAT String 1 String 2
CONCAT_WITH_SPACE String 1 String 2 # Spaces
LENGTH String
LEFT String Length
RIGHT String Length
SUBSTRING String Start pos. Length
LOWER String
UPPER String
INSTR String Search string
REPLACE String Search Replace
LPAD String Length Pattern
RPAD String Length Pattern
LTRIM String Character
RTRIM String Character

Replacing with Regular Expressions

Syntax / Parameters

REPLACE_REGEXPR(PCRE => pcre,
                VALUE => arg1,
                WITH => arg2,
                RESULT_LENGTH => res,
                OCCURRENCE => occ,
                CASE_SENSITIVE => case,
                SINGLE_LINE => bool,
                MULTI_LINE => bool,
                UNGREEDY => bool)

Reversing Characters

replace_regexpr(
    pcre          => '(.?)(.?)(.?)(.?)(.?)(.?)(.?)(.?)',
    value         => status,
    with          => '\8\7\6\5\4\3\2\1',
    result_length => 8) as mirroredStatus
  • PCRE – The regular expression
  • VALUE – Value in which something is replaced
  • WITH – Replacement. Use \1 for regex group 1
  • RESULT_LENGTH – Result length
  • OCCURRENCE – Default: replace all

Optional flags (true/false):

  • CASE_SENSITIVE
  • SINGLE_LINE
  • MULTI_LINE
  • UNGREEDY

SQL Functions for Numeric Values

Besides +, -, *, /:

Category SQL Function Arg1 Arg2 Arg3 Result
Sign ABS Value Absolute value
Division DIV Num. Den. Integer
DIVISION Num. Den. Decimals Decimal number
MOD Num. Den. Remainder
Rounding ROUND Value Dec. Rounded value
CEIL Value Next integer
FLOOR Value Lower integer
Conversion UNIT_CONVERSION
CURRENCY_CONVERSION
DECIMAL_SHIFT

SQL Functions for Date and Time

Many functions require <on_error> behavior:
FAIL, INITIAL, NULL (for ADD functions also UNCHANGED).

Time zone must often be provided:

  • ABAP_SYSTEM_TIMEZONE(<Client>, <on_error>)
  • ABAP_USER_TIMEZONE(<User>, <Client>, <on_error>)

User and client can be taken from session variables:

  • $session.user
  • $session.client

Date/Time Calculation Functions

Category Function Arg1 Arg2 Arg3 Arg4 Arg5
Add time DATS_ADD_DAYS Date Days OnError
DATS_ADD_MONTHS Date Months OnError
TSTMP_ADD_SECONDS Timestamp Seconds OnError
Differences DATS_DAYS_BETWEEN Date Date
TSTMP_SECONDS_BETWEEN Timestamp Timestamp
Validity DATE_IS_VALID Date
TIMS_IS_VALID Time
TSTMP_IS_VALID Timestamp
Conversion DATS_TIMS_TO_TSTMP Date Time Timezone Client Err
TSTMP_TO_DATS Timestamp Timezone Client Err
TSTMP_TO_TIMS Timestamp Timezone Client Err
Timezones ABAP_SYSTEM_TIMEZONE Client OnError
ABAP_USER_TIMEZONE User Client OnError
Current UTC TSTMP_CURRENT_UTCTIMESTAMP

Type Conversion with CAST and FLTP_TO_DEC

Type conversion with:

  • CAST ( <value> AS <datatype> )
  • FLTP_TO_DEC( <float> AS <DEC> ) – not needed in view entities anymore

The value may be any expression. Datatype may be:

  • Data element
  • Built-in ABAP datatype via abap.<type>

PRESERVING TYPE → conversion only in CDS, not passed to DB.

Example:
CAST( so.created_at AS abap.char(23))

Operator Expressions

Addition, subtraction & multiplication work as expected, but…

If a multiplication results in > 37 digits:

Error for >37 digits

➡️ Convert operands into smaller type before multiplication.

For CDS View Entities, this restriction no longer applies.
Results with > 37 digits are allowed.

Division with / is only allowed for floats in DDIC-based CDS!
Use DIVISION( numerator, denominator, decimals ) instead.

CASE Expressions

A CASE returns exactly one value.

CASE

CASE value
        WHEN val1 THEN res1
        WHEN val2 THEN res2
        ...
        ELSE alternative
END

CASE WHEN

CASE 
     WHEN cond1 THEN res1
     WHEN cond2 THEN res2
     ...
     ELSE alternative
END

Example:

CASE WHEN mara.aenam <> '' 
     THEN mara.aenam
     ELSE mara.ernam 
END AS LastChangedByUser

Demo – Expressions in Field Lists

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Demo Expressions in Field List'
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity zi_demo_expressions
  as select from zbc_users
{
  key user_id            as UserId,
      concat_with_space( concat(left(firstname, 1), '.'), lastname, 1 ) as Name,
      lower(email)              as Email,
      case gender when 'F' then 'Ms'
                  when 'M' then 'Mr'
                  else '' end as Greeting,
      cast(left($session.system_date, 4) as abap.int4) 
        - cast(left(date_of_birth, 4) as abap.int4) as Age
}

Exercise – Expressions in Field Lists

Create a CDS View Entity on tasks in ZBC_TASKS. Produce the following fields:

  • TaskKey
  • SummaryShort – first 20 chars + "..."
  • PrioCat:
    • A priority > 90
    • B priority > 60
    • C otherwise
  • DaysToDueDate
  • TaskNumber – key without project prefix

Some expression combinations only work in view entities.

Aggregation

Using GROUP BY changes the granularity.
For each combination of group fields → one row.

If no GROUP BY is used → exactly one row.

All fields must either:

  • Appear in GROUP BY or
  • Be aggregated.

Aggregation Syntax

SELECT FROM <Source>
{
  <GroupField1>, 
  <GroupField2>,
  ...
  <Aggregate>(<Field>)
}
GROUP BY <GroupField1>,
         <GroupField2>

Aggregation Example

define view entity zi_98_aggregation
  as select from zbc_tasks
{
  key assignee                as Assignee,
      count(*)                as TaskCount,
      min(due_date)           as MinDueDate,
      avg(priority as abap.dec(4)) as AvgPrio,
      sum(estimated_effort)   as SumEstimatedEffort
}
group by
  assignee

UNION (ALL)

UNION creates the union of two queries.

Requirements

Both queries must:

  • Have same number of columns
  • Have same column names
  • Have compatible datatypes
DEFINE VIEW AS
<query1>
UNION [ALL]
<query2>

UNION removes duplicates, UNION ALL does not.

WHERE Clause

Limitations vs SQLScript:

  • Comparison predicates (<, <=, etc.)
  • BETWEEN
  • LIKE (% wildcard, _ single char)
  • IS NULL – DB NULL
  • IS INITIAL – ABAP initial value

Subqueries are not allowed.
Workarounds via JOIN:

  • WHERE EXISTS → INNER JOIN
  • WHERE NOT EXISTS → LEFT OUTER JOIN + IS NULL
  • WHERE field IN (subquery) → INNER JOIN

NULL

NULL can occur due to:

  • NULL values in DB (rare)
  • CASE without ELSE
  • OUTER JOIN with no matching row
  • Errors in SQL functions

In results, NULL becomes the ABAP initial value.

NULL in expressions

ANY computation with NULL → NULL.

Handling NULL

  • In field list → use COALESCE( value, fallback )
  • In filtering → use IS NULL

NULL in ABAP

NULL becomes ABAP initial value.