Cheat Sheet CDS ABAP

Cheat Sheet CDS ABAP

The perfect complement to Brandeis Consulting's training courses

This cheat sheet contains a comprehensive overview of syntax, examples and descriptions of CDS ABAP. We also use it in our CDS ABAP training at Brandeis Consulting.

This overview shows only the most important aspects and is not complete. For details please follow the links to the SAP documentation or the linked sources. The examples refer to the slides from the training.

With CDS ABAP, the release of the ABAP server basically plays a major role, as there is still a lot going on here. The examples were created on release 7.53. When looking at the documentation, you should always choose your exact version level.


Below the line are references to the

3 Objects from classic CDS views

For CDS views, three different objects are created in the object catalog (table TADIR):

  • DDL file - The source code file , which is created and edited with the ADT in Eclipse. Only this object is in the object list of transport requests (object type DDLS).
  • CDS entity - The CDS object that represents all the properties of the view, including annotations, associations, and permission checks. This object is used in ABAP programs.
  • SQL database view - A database view that is also displayed as a view in DDic, but should not be used from an ABAP perspective.

The DDL file and the CDS entity usually have the same technical name. The SQL database view must have a different name than the CDS entity, since both are in the same namespace.

With Release 7.55, a new object type has been added to the classic CDS View, the CDS View Entity, which has no SQL database view. It is recommended by SAP to use these objects if you are on the appropriate system level.


SAP Docu

Syntax basics

Keywords in CDS ABAP are either

  • completely in CAPITAL LETTERS or
  • completely in lowercase letters or
  • the first letter capitalized and the rest lowercase

Identifiers of tables, views or columns are not case-sensitive. They may be a maximum of 30 characters long and consist of letters, numbers, underscore and slash.

Strings, as literals, are set in quotation marks in the source code. Numeric literals without decimal separator are of type ABAP.INT1 up to ABAP.INT8, depending on the value. Numeric literals with the dot as decimal separator are of the data type ABAP.FLTP.

Comments can either start at the end of the line with a double slash // or be placed as block comments between /* and */.

Whitespace doesn't matter much and is primarily used for formatting as long as the semantics are clear.


SAP Docu

Annotations

Annotations are used to enrich the source code with meta information. They are relevant for the generation of the views on the one hand and for the consumers of the CDS ABAP objects on the other hand. Each framework has its own annotations for the CDS views, e.g. :

  • @Analytics
  • @Consumtion
  • @EnterpriseSearch
  • @ObjectModel
  • @OData
  • @UI

In this poster, the most important annotations related to the current topics are mentioned. The complete list with over 800 annotations can be found in the SAP documentation.

Annotations syntax

@Annotation: <value>
@Annotation.SubAnnotation.
               SubSubAnnotation: <value>
@Annotation: { SubAnnotation1: <value1>,
               SubAnnotation2: <value2> }

Values of an annotation

  • Individual literals, e.g. the reference to another field name
  • Single values with predefined fixed values. These start with a # and are suggested by the code completion.
  • Single logical values trueand false
  • Arrays as a list of values or subAnnotations, in square brackets separated by commas.

SAP Docu

Syntax overview CDS Views

@AbapCatalog.sqlViewName: 'SQLDBView'
DEFINE VIEW CDSEntity
  [<ParameterDefinition>]
  AS SELECT FROM <sources>
  [<Joins>]
  [<Associations>]
  {
  [KEY] <Expression> [ AS <fieldname> ]
  [,...]
  } 
[WHERE - clause]
[GROUP BY - clause]
[HAVING - clause]
[UNION - operation]

The syntax described here is largely identical for CDS Views and CDS View Entities. There are two important differences in view entities:

  • The annotation @AbapCatalog.sqlViewName is omitted
  • Instead of DEFINE VIEW it is called DEFINE VIEW ENTITY

SAP Docu

Expressions in the field list

The field list consists of expressions. These are mostly field names of the sources. However, there may be other elements in it as well. The following are possible:

The expressions can be nested within each other. For example, a parameter can be used as a comparison value in a CASE expression.

The individual fields are separated by commas. If a field name is not referred to, an alias name assigned with AS is mandatory for the field. The keyword KEY marks the key value.


SAP Docu

Brandeis Consulting

Schulungen und Beratung vom Autor des Buches SQLScript für SAP HANA. Bei Fragen zur SQLScript, CDS ABAP oder unseren Schulungen einfach eine Mail an info@brandeis.de schicken.

(C) Brandeis Consulting GmbH

Data types in CDS ABAP and casting

In the CDS entities you can refer to the data elements of the Data Dictionary or directly to the ABAP data types. The data types must always fit exactly, otherwise there will be error messages when activating the CDS ABAP object. The SQL function CAST is used to convert the data types:

CAST( <expression> AS <datatype> [PRESERVING TYPE])

The ABAP data types are specified with abap.<datatype>, for example

  • abap.int4
  • abap.char(<length>)
  • abap.dec(<length>, <decimal places>)

Depending on the data type, the <length> and possibly also the number of <decimal places> must be specified in parentheses. If the data type of the <expression> is technically identical to the specified data element, you can specify the addition PRESERVING TYPE.


SAP Docu

Session variables

There are a handful of session variables that can be accessed in CDS ABAP.

$session.<variable_name>

So far there are the following variable names, which largely correspond to components of the SY or SYST structure in ABAP:

  • USER - The logged on ABAP user, equivalent to SY-UNAME.
  • CLIENT - The client to be used for the query. Corresponds to SY-MANDT, except for accesses with USING CLIENT or in AMDP with AMDP OPTIONS CDS SESSION CLIENT
  • SYSTEM_LANGUAGE - The logon language in internal ABAP format, equivalent to SY-LANGU
  • SYSTEM_DATE - Current system date, equivalent to SY-DATUM
  • SYSTEM_TIMEZONE - Time zone of the user, equivalent to SY-ZONLO
  • USER_DATE - Current date of the user, equivalent to SY-DATLO

SAP Docu

Arithmetic and operator expressions

In CDS ABAP the arithmetic operators for addition (+), subtraction (-) and multiplication (*) work as you would expect. However, the division differs from this. Basically, a whitespace is required before and after the division operator (/).

  • For operator expressions with ABAP.FLTP both operands must have this data type. This is especially disturbing since literals with decimal places are of the type ABAP.FLTP. The only thing that helps here is a CAST.
  • Division with the division operator (/) is allowed in classic CDS views only for floating point data types. Therefore, use the SQL function DIVISION( <numerator>, <denumerator>, <decimal places> ), which rounds automatically.
  • For calculations that result in a decimal number, a check is made to ensure that the result is convenient for the ABAP.DEC data type. This means a maximum of 31 digits, 14 of which are after the decimal point. If this is not the case, the operands must be reduced beforehand per CAST.

SAP Docu

CASE Expressions

A CASE expression in CDS ABAP always returns exactly one value, depending on the conditions. The simple CASE expression compares an expression to several other expressions for equality:

CASE item_categ WHEN '10' THEN 'A'
                WHEN '20' THEN 'B'
                ELSE 'C'
    END 

The complex CASE expression (aka 'searched case') evaluates N independent conditions. The first case to be evaluated to TRUE, returns the result:

CASE WHEN weight_measure < 1 
       OR weight_unit = 'g' THEN 'A'
       WHEN weight_measure >= 1 
       AND weight_measure <= 5 THEN 'B'
       ELSE 'C'
   END 

If no condition was evaluated to TRUE, either the value from the ELSE clause or NULLis returned.


SAP Docu

Associations

Associations can be used to describe the relationships between CDS entities. They define a JOIN that is executed only, if the fields are retrieved from the associated entity. These queries are created with so-called path expressions and can run across multiple CDS entities.

DEFINE VIEW zcds_as_soi  //SalesOrderItem
  AS SELECT FROM snwd_so_i AS soi
  ASSOCIATION [1] TO snwd_pd AS _product 
  ON soi.product_guid = _product.node_key
  ASSOCIATION [1] TO zcds_as_so AS _salesOrder
  ON soi.parent_key = _salesOrder.NodeKey
{
<fieldList>
...
_product, 
_salesOrder
}

The names of the associations always start with an underscore. They are included in the field list and thus they are available to the users of the CDS View.

The cardinality can be specified in square brackets: [Min..Max]. If only Max is specified, the Min value is assumed to be 0. By default, it is [0..1]. Permitted values are e.g. [1], [*], [1..1], [1..*] or [0..*].


SAP Docu

Path expressions

Path expressions can be used to include fields from associated sources in the field list of the CDS view or query on the CDS entity. The path expressions can also go over several levels. It is also possible to add attributes to the evaluation of the associations, for example to filter or to define the join type:

_sales_order.BillingStatus,
_sales_order._buyer.company_name
_sales_order._buyer[LEFT OUTER 
         WHERE legal_form = 'GmbH'].company_name

In ABAP SQL

The path expressions can be used in ABAP SQL. However, the syntax is slightly different. Before associations, and as separator between them, there is always a backslash (\). The component is addressed (as usual in ABAP) with a hyphen (-):

SELECT _sales_order_buyer[LEFT OUTER
          WHERE legal_form = 'GmbH']-company_name
FROM zcds_my_view
INTO TABLE @DATA(lt_tmp).

SAP Docu

NULL in CDS ABAP and ABAP

In the database there is the pseudo-value NULL, which signalizes the absence of a value. This is translated into an initial value in ABAP. This can lead to unexpected situations when aggregating, since two groups are formed - one for NULL and one for initial values in CDS ABAP - but both are initial in ABAP.

Replace NULL

You can catch NULL values with the SQL function COALESCE(<value1>, <value2>), because then this function returns the 2nd value.

Filter with NULL

A normal comparison with NULL always results in the logical value UNKNOWN. Thus, such a comparison is never valid in a WHERE condition. That is why the predicate IS (NOT) NULL is needed.


SAP Docu

SQL functions for strings

Be careful when using blanks at the beginning or at the end of strings (STR). These are partially removed. A look at the documentation is essential here!

SQL functionDescription
CONCAT(<STR1>, <STR2>)Concatenate <STR1> and <STR2>.
CONCAT_WITH_SPACES(<STR1>, <STR2>, <number>)Concatenate strings <STR1> and <STR2> with a <number> of blanks.
LENGTH(<STR>)Length of the string <STR>
LOWER(<STR>)Conversion of the string <STR> to lower case letters
UPPER(<STR>)Conversion of the string <STR> to upper case letters
LEFT(<STR>, <length>)Left part of the string <STR> with <length>
RIGHT(<STR>, <length>)Right part of the string <STR> with <length>
SUBSTRING(<STR>, <pos>, <length>)Substring from <position> in <length>
INSTR(<STR1>, <STR2>)Position of <STR2> in <STR1>
REPLACE(<STR1>, <STR2>, <STR3>)Replaces <STR2> in <STR1> with <STR3>
REPLACE_REGEXPR(

PCRE => <regex>,
VALUE => <STR>,
WITH => <substitute>,
RESULT_LENGTH => <length>,
Optional parameters:
[OCCURRENCE],
[CASE_SENSITIVE],
[SINGLE_LINE],
[MULTI_LINE],
[UNGREEDY] )
Replaces the Perl Compatible Regular Expression (PCRE) <regex> in the string <STR> with the <substitute>. Does NOT work in classic CDS views, only in CDS view entities. All parameters are specified in the format name => <value> . OCCURENCE: 1-N or ALL. The flags have the value '' or 'X'.
LPAD(<STR> , <length> , <example>)Filling the <STR> from left with <example> up to the <length>
RPAD(<STR>, <length>, <example>)Filling the <STR> from right with <example> up to the <length>
LTRIM(<STR> , <character>)Remove all occurrences of a <character> from the left of the <STR>, e.g. leading 0 or blank.
RTRIM(<STR>, <character>)Remove all occurrences of a <character> from the right of the <STR>, e.g. leading 0 or blank.

SAP Docu

Numeric SQL functions

Abbreviations: number or numerator (<N>), denominator (<D>) and decimal place (<DP>).

SQL functionDescription
ROUND(<N>, <DP>) Commercial rounding <DP> positions
CEIL(<N>) Rounding up <N> to the nearest integer
FLOOR(<N>) Rounding down <N> to the nearest integer
DIVISION(<N>, <D>, <DP>)<N> divided by <D> rounded to <DP> digits
DIV(<N>, <D>) Integer part of the division
MOD(<N>, <D>) Remainder of division of <N> by <D> (modulus)
ABS(<N>) Positive absolute value of <N>

SAP Docu

Date and time calculation

The functions in the CDS ABAP are the same as those available in the OpenSQL in ABAP. The data types play a major role in the SQL functions, which is why their abbreviation is always prefixed, e.g.:

  • DATS is the popular ABAP data type, for the DB only CHAR(8)
  • DATN is the SQL date format on HANA
  • TIMS is the ABAP data type for time
  • TIMN is the SQL time format on HANA
  • TSTMP is the short timestamp, 15-digit decimal number
  • TSTMPL is the long time stamp with 7 DP digits.
  • UTCL corresponds to the ABAP data type UTCLONG, internally 8 bytes

The names of the CDS ABAP SQL functions start with the data type.

Conversions

  • DATS_TO_DATN
  • DATS_FROM_DATN
  • TIMS_TO_TIMN
  • TIMS_FROM_TIMN
  • DATS_TIMS_TO_TSTMP
  • TSTMP_TO_DATS
  • TSTMP_TO_DST
  • TSTMP_TO_TIMS
  • TSTMPL_TO_UTCL
  • TSTMPL_FROM_UTCL

Validity

Returns 1 if valid, otherwise 0.

  • TSTMP_IS_VALID
  • TIMS_IS_VALID
  • DATS_IS_VALID

Addition

  • DATN_ADD_DAYS
  • DATN_ADD_MONTHS
  • DATS_ADD_DAYS
  • DATS_ADD_MONTHS
  • TSTMP_ADD_SECONDS
  • UTCL_ADD_SECONDS

Differences

  • UTCL_SECONDS_BETWEEN
  • TSTMP_SECONDS_BETWEEN
  • DATS_DAYS_BETWEEN
  • DATN_DAYS_BETWEEN

Current time

  • UTCL_CURRENT
  • TSTMP_CURRENT_UTCTIMESTAMP
  • ABAP_SYSTEM_TIMEZONE
  • ABAP_USER_TIMEZONE

SAP Docu

Parameter

Parameters can be defined for a CDS ABAP View, which are then used as an expression, e.g. for calculation, as a comparison value or as a function parameter.

Default values only work in a few frameworks:

  • @Consumption.defaultValue - Constant value
  • @Environment.systemField Session variables, e.g. system date
@AbapCatalog.sqlViewName: 'ZSQL_DEMO_PARA'
@EndUserText.label: 'Parameter in CDS Views'
DEFINE VIEW zjb_demo_parameter
    WITH PARAMETERS 
    @Consumption.defaultValue: '16'
    iv_vat : abap.int1 ,
    @Environment.systemField:  #SYSTEM_DATE
    iv_date: abap.dats
AS SELECT /bic/aorda001_12 
{
    KEY doc_number,
    net_value * division($parameters.iv_vat , 
                          100,
                          3 ) AS my_tax_value
} WHERE createdon = $parameters.iv_date

SAP Docu

CDS Extractors

Delta extraction with timestamp field

As with the generic delta, a delta can be formed using a timestamp field. Optionally, a safety interval can also be specified.

@Analytics:{ dataCategory: #FACT, 
                        // #DIMENSION, #CUBE
            dataExtraction:{
                enabled: true,
                delta.byElement: {   
                    name:'upd_date', 
                    maxDelayinSeconds: 1800,
                    detectDeletedRecords: false,
                    ignoreDeletionAfterDays : '365'
} } }

Change Data Caption (CDC)

Automatic change recording is possible on systems from SAP S/4HANA 1909 FPS01. For more complex scenarios with JOINs, explicit mapping is required. Details in the blog of Simon Kranig - a very good read.

@Analytics:{
    dataCategory: #DIMENSION 
    dataExtraction: { 
        enabled: true, 
        delta.changeDataCapture: { 
            automatic : true 
} } }

SAP Docu, Blog Simon Kranig

CDS Table Functions

CDS Table Functions are views programmed in SQLScript. They are based on an AMDP function encapsulated by a CDS ABAP object.

CDS ABAP table function

The CDS table function defines the signature:

@EndUserText.label: 'My Table Function'
DEFINE TABLE FUNCTION zjb_demo_tf
RETURNS
{
  mandt      : abap.clnt;
  doc_number : /bi0/oidoc_number;
  net_price  : /bi0/oinet_price;
}
IMPLEMENTED BY 
    METHOD zcl_demo_tf=>my_tf;

The correlating AMDP table function:

CLASS zcl_demo_tf DEFINITION PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS my_tf 
        FOR TABLE FUNCTION zjb_demo_tf.
ENDCLASS.

CLASS zcl_demo_tf IMPLEMENTATION.
  METHOD my_tf BY DATABASE FUNCTION
           FOR HDB LANGUAGE SQLSCRIPT
           USING <sourceTable>.
    RETURN SELECT mandt,
                  doc_number,
                  SUM( net_price ) 
                         AS net_price
            FROM <sourceTable>
            GROUP BY doc_number;
  ENDMETHOD.
ENDCLASS.

SAP Docu

CDS InfoProvider Views

CDS queries are based on CDS InfoProviders with the annotation @Analystics.dataCateogory: [#CUBE | #DIMENSION]

They form a transient InfoProvider named 2C<SQLView Name>. They contain the complete semantic information of the data model that the CDS queries are to use later.

CDS Cube Views

CDS Cube Views are the basis of key figure reports. You can associate additional CDS dimension views to enrich the attributes and texts of features.

...
@Analytics.dataCategory: #CUBE
DEFINE VIEW zjb_demo_cube
  AS SELECT FROM <Quelle>
  ASSOCIATION TO zjb_material_dim AS _material 
          ON $projection.material = _material.material
{
...
  @ObjectModel.foreignKey.association: '_material'
  material,
...

CDS Dimension Views

The CDS Dimension Views provide attributes for a feature. These attributes can be time-dependent.

...
@Analytics.dataCategory: #DIMENSION
@ObjectModel.representativeKey: 'material'
DEFINE VIEW zjb_material_dim
  AS SELECT FROM /bi0/pmaterial AS dim 
  
  ASSOCIATION [0..*] TO zjb_material_txt 
                          AS _material_text
          ON dim.material = _material_text.material
{
  @ObjectModel.text.association: '_material_text'
  KEY material,        
...

CDS Text Views

The CDS Text Views provide the language-dependent texts for fields.

...
@ObjectModel.dataCategory: #TEXT
@ObjectModel.representativeKey: 'material'
DEFINE VIEW zjb_material_txt
  AS SELECT FROM /bi0/tmaterial 
{ 
  KEY material,
  
  @Semantics.language: true
  KEY langu,
  
  @Semantics.text: true
  txtmd 
}

SAP SCN Wiki, My Blog

CDS Query Views

The source for a CDS Query View is always a CDS InfoProvider View. The CDS ABAP Annotation @Analytics.query: true turns a view into a CDS Query View.

Annotations at view level

@Analytics.query: true
@Analytics.settings.zeroValues: { 
          handling:   #HIDE_IF_ALL,// #HIDE, #SHOW       
          hideOnAxis: #ROWS_COLUMNS// #COLUMNS, #ROWS }

Annotations at field level

Filter with prompt

@Consumption.filter:{
   selectionType: #RANGE, // #INTERVAL,
                          // #SINGLE, #HIERARCHY_NODE 
   multipleSelections: false, 
   mandatory: true, 
   hidden: false, //Default:false 
   defaultValue: '0000000000011675' }

AnalyticDetails for features

@AnalyticsDetails.query:{
        display:       #KEY, //#KEY_TEXT, #TEXT
        axis:          #ROWS, //#COLUMNS
        totals:        #HIDE, //#SHOW
        hidden:        false, //Default: false
        sortDirection: #ASC //#DESC }

**AnalyticDetails for key figures**

@AnalyticsDetails.query:{ decimals: 0 ,
                          hidden:   false
                          formula:  ' a - b '  }
    1 AS a_minus_b,

@EndUserText.label: 'number of records' 
@AnalyticsDetails.exceptionAggregationSteps: 
   [{ exceptionAggregationBehavior: #COUNT, 
      exceptionAggregationElements: ['doc_number'] }] 
   1 AS doc_count

SAP Docs, My Blog