ABAP CDS - Data Definitions

CDS data definitions are made using the ABAP CDS DDL in CDS source code. CDS data definitions can be used to defined (and possibly enhance) CDS entities:
  • CDS views
  • CDS table functions
A CDS entity is a structured ABAP CDS object managed by ABAP Dictionary. When a CDS data definition is activated, it is created as metadata in CDS source code. CDS entities are not transported and are created after the transport of a piece of CDS source code when this code is activated in the target system. Each CDS entity is defined in a separate piece of CDS source code in the ABAP Development Tools (ADT). In the development tools, CDS entities are only visible as CDS definitions in their CDS source code. The ABAP Dictionary tool in ABAP Workbench (SE11) does not support any CDS data definitions of CDS entities. The ABAP runtime environment and other frameworks do not access the internal metadata of CDS entities. For example, the ABAP runtime environment does not recognize a CDS entity as a data type of ABAP Dictionary or as a potential data source of Open SQL statements. The name of a CDS must be unique accordingly.

As a data type in ABAP Dictionary, a CDS entity represents a structured type. In ABAP CDS, the CDS entity can be used as a data source in other CDS entities. In ABAP, a CDS entity can be specified after the TYPE addition to reference this structure type and its components in ABAP programs. The components of the structured type are, as usual, specified using the structure component selector cds_entity-comp. In Open SQL read statements, however, CDS entities cannot be accessed in Native SQL.

A CDS entity cannot be used as a data type in ABAP Dictionary. It cannot, for example, be used as a type of a substructure, as an include structure, or as the row type of a table type.

The following sections describe the possible CDS entities and the associated DDL statements:
  • CDS views
  • CDS table functions
  • DDL statements
A further section describes the problems that can arise due to cyclical dependencies between CDS entities.

Notes
  • The CDS source code of a data definition for a CDS entity does not need to have the same name as the entity, but this is recommended.
  • In activations, the names of a piece of CDS source code, the CDS entity, and (if necessary) the CDS database view construct an indivisible defining unit. After a piece of CDS source code is transported for the first time, this defining unit cannot be used elsewhere and none of the names used can be modified. More specifically, the following applies:
    • In a piece of CDS source code, the name of a CDS entity or a CDS database view cannot be renamed.
    • A CDS entity or CDS database view cannot be used in a different piece of CDS source code, if the original source code was deleted.
    • A piece of CDS source code already used for a CDS entity or CDS database view cannot be used for a different CDS entity or CDS database view.
    • The same applies to the CDS source code of a CDS view extension and its append view.
  • The CDS source code in data definition for a CDS entity is edited in a different editor from the CDS source code of a CDS role for CDS access control. The ADT documentation describes how the different types of source code are created.
  • The program DEMO_SHOW_DDL_SOURCE displays CDS source code in SAP GUI.
  • A structure type defined using a CDS entity exists alongside the classic structures in ABAP Dictionary.
➤ ABAP CDS - Views

A CDS view is defined for existing database tables and any other views or CDS views in ABAP Dictionary using the statement DEFINE VIEW in the DDL in ABAP Core Data Services (CDS). This is done in the CDS source code of a CDS data definition in the ABAP Development Tools (ADT). When the CDS data definition of a CDS view is activated, two ABAP Dictionary objects area created:
  • The CDS entity
The CDS entity represents the actual CDS view. It is based on a CDS database view and makes other attributes possible, such as access controls defined in CDS. The name of this entity, cds_entity, is defined in the definition of a CDS view after DEFINE VIEW. The definition of the CDS entity occurs only as CDS source code. The ABAP Dictionary tool in ABAP Workbench (SE11) does not recognize the CDS entity.

As a data type in ABAP Dictionary, the CDS entity represents a structured type with the elements of the CDS view as components and can be used like any CDS entity.
    • In ABAP CDS, the CDS entity can be used as a data source of other CDS entities.
    • In ABAP, the CDS entity can be used as a data type and in Open SQL read statements.
    • The CDS entity cannot be used as a data type for definitions of classic dictionary objects in ABAP Dictionary.
  • A CDS database view
The CDS database view is the technical foundation of the CDS view in ABAP Dictionary. It is a read-only classic database view. The name CDS_DB_VIEW of this view is defined in the definition of a CDS view in the CDS annotation @AbapCatalog.sqlViewName. The ABAP Dictionary tool in ABAP Workbench (SE11) can display a CDS database view, but it cannot be edited here. When the view is activated, the corresponding SQL view is created with the same name.

As a data type in ABAP Dictionary, the CDS database view represents (like all classic database views) a flat structure with the elements of the CDS view as components. The CDS database view of a client-specific CDS view always has a client column, unlike the CDS entity.
  • In ABAP Dictionary, the CDS database view can be used like any classic structure, such as the type of a substructure, as an include structure, or as the row type of a table type.
  • The CDS database view should not, however, be used in ABAP. It can be used wherever database tables or classic database views can also be specified (using its name CDS_DB_VIEW), such as after the TYPE addition, in Open SQL read statements and also in Native SQL or after the TABLES statement (but this is not recommended). More particularly, the use of the CDS database view in reading Open SQL statements is obsolete and forbidden in strict mode from Release 7.50. Instead, the CDS entity should be accessed in ABAP, which covers all attributes of the CDS view.
Both the names cds_entity and CDS_DB_VIEW are in the namespace of the data types in ABAP Dictionary. The view is also known under both names within the ABAP CDS DDL. Furthermore, only the CDS entity should be accessed in other CDS entities using its name cds_entity and not the CDS database view using the name CDS_DB_VIEW.

A CDS view has two types of keys:
  • For CDS entities, KEY can be used to define key elements. This key is the semantic key of the CDS view.
  • The key of the CDS database view is determined implicitly, as in a classic view. This key is the technical key of the CDS view.
In Open SQL, the key used is determined according to whether the name of the CDS entity or the name of the CDS database view is used to access a CDS view.

The following sections describe client handling and SAP buffering for CDS views:
  • Client handling
  • SAP buffering
Notes
  • The programming of CDS views using the DDL of the Core Data Services is one of the tasks included in the implementation of data models in ABAP Dictionary and is not usually the responsibility of regular ABAP application programmers. Once created, CDS views are used in ABAP programs using Open SQL read statements and must be stable enough to allow this.
  • As well as the specific functions available for CDS views, such as the definition of associations, the options for the regular SQL DDL of the CDS DDL are far more comprehensive than the form-based ABAP Dictionary tool in ABAP Workbench used to define classic views.
  • CDS views are defined as platform-independent views. They can, however, contain components that are not currently supported by all database systems. Before an ABAP read, the class CL_ABAP_DBFEATURES can be used to determine whether the feature is supported in the current system, which stops an exception from being raised.
  • If a CDS role is defined for a CDS entity using the DCL statement DEFINE ROLE and CDS access control is not disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck, any reads performed on the CDS entity using Open SQL or using an SADL query are subject to implicit access control and only that data is read that meets the access conditions.
  • A CDS database view is created for each CDS view and this database view supports only transparent tables, which means that pooled tables and cluster tables cannot be accessed using CDS views.
  • For CDS views, CDS view enhancements are a separate way of making enhancements without making modifications.
1. ABAP CDS - Client Handling in CDS Views

The CDS annotation @ClientDependent can be used to switch client dependency on and off for a CDS view in ABAP CDS (the default setting is on). If a client-specific CDS entity is used as a data source in another CDS view, this view is also client-specific. In Open SQL reads, the client dependency of a CDS view has the following consequences for the CDS entity and the associated CDS database view:
  • CDS entity
    • The view fields of the CDS entity of a CDS view with client dependency switched on do not cover a client column. A client column specified explicitly in the SELECT list is also not a component of the structured data type represented by the CDS entity and is removed implicitly from the results set of a SELECT statement. A client column specified in the SELECT list is preserved in the results set only if client dependency is switched off explicitly using the annotation @ClientDependent:false. The column is then applied like a regular view field when the CDS entity is accessed.
    • If SELECT is used to access a CDS entity with client dependency switched on, the data of the current client or the client specified in the addition USING CLIENT is read implicitly. If the addition CLIENT SPECIFIED is specified, the column is added to the results set and is filled with the associated client ID for each row. Before this column can be used in the SELECT statement, a name must be assigned to it after the addition CLIENT SPECIFIED. If the name is not defined, no addressing is possible in a clause and no inline declarations can be made after INTO. The defined name is also used in the case of INTO CORRESPONDING. If no name is defined, the client column is not transported
  • CDS database view
    • The view fields of a CDS database view of a CDS view with client dependency switched on always cover a client column, unless all data sources are cross-client. This applies regardless of whether the client column is specified in the SELECT list. If client dependency is switched off explicitly using the annotation @ClientDependent:false, a CDS database view does not have a client column if it is not specified explicitly in the SELECT list. If client handling is switched off, a client column specified in the SELECT list, however, makes the CDS database view client-specific.
    • In obsolete accesses made to the CDS database view using SELECT, the view is handled in accordance with the usual automatic client handling, where a client column is evaluated implicitly or explicitly using the addition CLIENT SPECIFIED. This applies in particular to CDS database views of CDS views in which client dependency is switched off but a client column is specified in the SELECT list.
    • The name of the client column of a CDS database view with activated client handling is defined in accordance with the following hierarchy: If the client column is specified in the SELECT list, its name or its alternative name is used. If the client column is not specified in the SELECT list, MANDT is used. If MANDT is already used as an (alternative) element name in the SELECT list, CLIENT is used. If neither MANDT or CLIENT is possible, a syntax error occurs.
Notes
  • CDS views for application data should usually be client-specific.
  • CDS views with client columns in the SELECT list display different behavior depending on whether the CDS entity is accessed or the CDS database view and this is one of the reasons why only the CDS entity should be accessed. In this case, switching off client dependency only affects the CDS entity.
  • Native SQL can only be used to access the CDS database view. When client-specific views are accessed, the client ID must be specified as usual and only data in the current client should be accessed.
CDS Views, Client Handling

This example demonstrates various ways in which the client column of CDS views can be handled.

Source Code

REPORT demo_cds_client_handling.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    DATA(out) = cl_demo_output=>new( ).

    out->next_section( 'Data Types' ).

    out->write(
      name = 'Client dependent, no client field, CDS entity'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_SPFLI' )
                        )->components ).

    out->write(
      name = 'Client dependent, no client field, database view'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_PRJCTN' )
                        )->components ).

    out->write(
      name = 'Client dependent, client field, CDS entity'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_SPFLI_CLIENT_1' )
                        )->components ).

    out->write(
      name = 'Client dependent, client field, database view'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_PRJCTN1' )
                        )->components ).

    out->write(
      name = 'Not client dependent, no client field, CDS entity'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_SPFLI_CLIENT_2' )
                        )->components ).

    out->write(
      name = 'Not client dependent, no client field, database view'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_PRJCTN2' )
                        )->components ).

    out->write(
      name = 'Not client dependent, client field, CDS entity'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_SPFLI_CLIENT_3' )
                        )->components ).

    out->write(
      name = 'Not client dependent, client field, database view'
      data = CAST cl_abap_structdescr(
                    cl_abap_typedescr=>describe_by_name(
                      'DEMO_CDS_PRJCTN3' )
                        )->components ).

    out->next_section( 'SELECT Statements' ).

    SELECT *
           FROM demo_cds_spfli
           ORDER BY carrid, connid
           INTO TABLE @DATA(result1)
           UP TO 1 ROWS.
    out->write(
      name = 'Client dependent, no client field, CDS entity'
      data = result1 ).

    SELECT *
           FROM demo_cds_prjctn
           ORDER BY carrid, connid
           INTO TABLE @DATA(result2)
           UP TO 1 ROWS.
    out->write(
      name = 'Client dependent, no client field, database view'
      data = result2 ).

    SELECT *
           FROM demo_cds_spfli CLIENT SPECIFIED demo_cds_spfli~myclient
           WHERE myclient = @sy-mandt
           ORDER BY carrid, connid
           INTO TABLE @DATA(result3)
           UP TO 1 ROWS.
    out->write(
      name = 'Client dependent, no client field, CDS entity,' &
             ' CLIENT SPECIFIED'
      data = result3 ).

    SELECT *
           FROM demo_cds_spfli_client_1
           ORDER BY carrid, connid
           INTO TABLE @DATA(result4)
           UP TO 1 ROWS.
    out->write(
      name = 'Client dependent, client field, CDS entity'
      data = result4 ).

    SELECT *
           FROM demo_cds_spfli_client_2
           ORDER BY carrid, connid
           INTO TABLE @DATA(result5)
           UP TO 1 ROWS.
    out->write(
      name =  'Not client dependent, no client field, CDS entity'
      data = result5 ).

    SELECT *
           FROM demo_cds_prjctn2
           ORDER BY carrid, connid
           INTO TABLE @DATA(result6)
           UP TO 1 ROWS.
    out->write(
      name =  'Not client dependent, no client field, database view'
      data = result6 ).

    SELECT *
           FROM demo_cds_spfli_client_3
           WHERE mandt = @sy-mandt
           ORDER BY carrid, connid
           INTO TABLE @DATA(result7)
           UP TO 1 ROWS.
    out->write(
      name = 'Not client dependent, client field, CDS entity'
      data = result7 ).

    SELECT *
           FROM demo_cds_prjctn3 CLIENT SPECIFIED
           WHERE mandt = @sy-mandt
           ORDER BY carrid, connid
           INTO TABLE @DATA(result8)
           UP TO 1 ROWS.
    out->write(
      name = 'Not client dependent, client field, database view,' &
             ' CLIENT SPECIFIED'
      data = result8 ).

    out->display( ).
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

Description

The program accesses the following CDS views:

@AbapCatalog.sqlViewName: 'DEMO_CDS_PRJCTN'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_spfli
  as select from spfli
     { key spfli.carrid,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }
@AbapCatalog.sqlViewName: 'DEMO_CDS_PRJCTN1'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_spfli_client_1
  as select from spfli
     { key spfli.mandt,
       key spfli.carrid,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }
@AbapCatalog.sqlViewName: 'DEMO_CDS_PRJCTN2'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientDependent: false
define view demo_cds_spfli_client_2
  as select from spfli
     { key spfli.carrid,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }
@AbapCatalog.sqlViewName: 'DEMO_CDS_PRJCTN3'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientDependent: false
define view demo_cds_spfli_client_3
  as select from spfli
     { key spfli.mandt,
       key spfli.carrid,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }

The first view, DEMO_CDS_SPFLI, is a regular client-specific CDS view in which the client column is not specified in the SELECT list. The other views are used to demonstrate the handling behavior in less common cases:
  • DEMO_CDS_SPFLI_CLIENT_1 is a client-specific CDS view in which the client column is specified in the SELECT list.
  • DEMO_CDS_SPFLI_CLIENT_2 is a cross-client CDS view in which the client column is not specified in the SELECT list.
  • DEMO_CDS_SPFLI_CLIENT_3 is a cross-client CDS view in which the client column is specified in the SELECT list.
RTTI is used to read the components of the associated data types:
  • The structure of the client-specific CDS entity without client column in the SELECT list does not have a client column.
  • The database view of the client-specific CDS view without client column in the SELECT list has a client column.
  • Nevertheless, the structure of the client-specific CDS entity with client column in the SELECT list does not have a client column.
  • The database view of the client-specific CDS view with client column in the SELECT list has a client column.
  • The structure of the cross-client CDS entity without client column in the SELECT list does not have a client column.
  • The database view of the cross-client CDS view without client column in the SELECT list does not have a client column.
  • The structure of the cross-client CDS entity with client column in the SELECT list has a client column.
  • The database view of the cross-client CDS view with client column in the SELECT list has a client column, which makes it client-specific.
The statement SELECT is used to make the following accesses:
  • Regular access to a client-specific CDS entity without client column in the SELECT list. The data from the current client is read. The results set does not have a client column.
  • Regular access to a database view of the client-specific CDS entity without client column in the SELECT list. The data from the current client is read. The results set has a client column.
  • Access to a client-specific CDS entity without client column in the SELECT list using CLIENT SPECIFIED. The data of the client specified in the WHERE condition is read. A name myclient must be defined for this. The result set has a client column with this name.
  • Regular access to a database view of the client-specific CDS entity with client column in the SELECT list. The data from the current client is read. The results set does not have a client column.
  • Regular access to a cross-client CDS entity without client column in the SELECT list. The data of all clients is read and no conditions can be specified for the client. The results set does not have a client column.
  • Regular access to a database view of the cross-client CDS entity without client column in the SELECT list. The data of all clients is read and no conditions can be specified for the client. The results set does not have a client column.
  • Regular access to a cross-client CDS entity with client column in the SELECT list. The data of the client specified in the WHERE condition is read. It is not possible to specify the addition CLIENT SPECIFIED here. The results set has a client column.
  • Access to a database view of the cross-client CDS with client column in the SELECT list using CLIENT SPECIFIED. The data of the client specified in the WHERE condition is read. The addition CLIENT SPECIFIED must be specified here and the results set has a client column.
Note

The accesses to the database views of the CDS shown here are for demonstration purposes only. This way of accessing CDS views is obsolete and only the CDS entities should now be accessed.

2. ABAP CDS - SAP Buffering of CDS Views

The CDS annotation @AbapCatalog.buffering.status can be used to define whether and how SAP buffering is allowed for a CDS view in ABAP CDS. Further annotations, AbapCatalog.buffering.type and AbapCatalog.buffering.numberOfKeyFields, define the buffering type and, if generic buffering is used, the number of key fields involved.

SAP buffering for CDS views is performed in the same way as for database views and the same prerequisites apply. Furthermore, a CDS view can be buffered only if it does not contain any views (database views or CDS views) as data sources.

Note: 

In SAP buffering of CDS views, only the key fields of the CDS database view are relevant and not the key elements of the CDS entity defined using KEY.

Example

Enables full buffering for a CDS view.

@AbapCatalog.sqlViewName: '...'
@AbapCatalog.Buffering.status: #ACTIVE
@AbapCatalog.Buffering.type: #FULL
  define view ...
   as select from ...
   { ... }

➤ ABAP CDS - Table Functions

A CDS table function is defined in CDS source code of a CDS data definition in the ABAP Development Tools (ADT) using the statement DEFINE TABLE_FUNCTION in the ABAP Core Data Services (CDS) DDL. A CDS table function includes the following:
  • The CDS entity
A CDS table function is declared as a CDS entity using the name of its CDS entity as it is defined as cds_entity after DEFINE TABLE_FUNCTION. As a data type in ABAP Dictionary, the CDS entity represents a structured type with the elements of the CDS table function as components and can be used like any CDS entity.

1. In ABAP CDS, the CDS entity can be used as a data source of other CDS entities.
2. In ABAP, the CDS entity can be used as a data type and in Open SQL read statements.
3. The CDS entity cannot be used as a data type for definitions of classic dictionary objects in ABAP Dictionary.
  • An AMDP function implementation
CDS table functions are implemented in Native SQL in an AMDP method and the implementation is managed as an AMDP function by the AMDP framework in the database system.

The AMDP method is specified after the addition IMPLEMENTED BY in the definition of the CDS table function using DEFINE TABLE_FUNCTION. It must be declared as a special AMDP function implementation for precisely one CDS table function using the addition FOR TABLE FUNCTION.
The parameter interface of the AMDP function is defined using the input parameters and the element list of the CDS table function.

Like a CDS view, a CDS table function is client-specific by default and client handling is automatic in Open SQL accesses.

Notes:
  • CDS table functions can only be used in a database system that supports AMDP.
  • When a CDS table function is created, the CDS entity must be activated first, before the associated AMDP function implementation is created.
  • When a CDS table function is transported, the CDS entity is first transported as part of the dictionary transport objects and then the AMDP function implementation as part of the ABAP transport objects. Depending on the size of the transport, there can be a considerable delay between these two phases where the CDS table function is not in a usable state.
➥ ABAP CDS - Client Handling in CDS Table Functions

The CDS annotation @ClientDependent can be used to switch client dependency on and off for a CDS table function in ABAP CDS (the default setting is on). The client dependency of a CDS table function has the following consequences for the CDS entity and the associated AMDP function implementation:
  • Client-specific CDS table function
    • The element list of a client-specific CDS must have an explicit client field with the predefined dictionary type CLNT as its first element. The client field is a column of the tabular return value of the associated AMDP function implementation and is not a component of the structured data type represented by the CDS entity.
    • When a client-specific CDS table function is accessed using SELECT without the addition CLIENT SPECIFIED, only those rows are selected implicitly from the results set of the function that contain the ID of the current client or the client specified in the addition USING CLIENT in the client field.
    • A CDS table function is always implemented in the associated AMDP method in Native SQL. All required data must be provided by the implementation including the correct use of the client columns in the ON conditions of joins.
    • For performance reasons, it is best to restrict the results set of the function to the required clients at the implementation stage. This usually involves passing the client ID in question to an input parameter of the function. Here, it is advisable to use an input parameter of the dictionary type CLNT, which needs to be annotated with the annotation @Environment.systemField and the predefined value #CLIENT. In this case, SELECT passes the correct client ID implicitly.
    • It is not advisable to access the current client in the implementation of a CDS table function instead of using an input parameter and the ABAP-specific session variable CLIENT. In this case, the addition USING CLIENT of the Open SQL statement SELECT is ignored.
    • If the addition CLIENT SPECIFIED is specified, the column is added to the results set and is filled with the associated client ID for each row. Before this column can be used in the SELECT statement, a name must be assigned to it after the addition CLIENT SPECIFIED. If the name is not defined, no addressing is possible in a clause and no inline declarations can be made after INTO. The defined name is also used in the case of INTO CORRESPONDING. If no name is defined, the client column is not transported
  • Cross-client CDS table function
    • The element list of a cross-client CDS does not need to have an explicit client field with the predefined dictionary type CLNT. If the first element has the type CLNT, it does not function as a client field. Instead, it is a column of the tabular return value of the associated AMDP function implementation and also a regular component of the structured data type represented by the CDS entity.
    • When a cross-client CDS table function is accessed using SELECT, an element of the type CLNT does not have a special meaning and is handled like any other element.

Note: CDS table functions for application data should usually be client-specific.

Examples

1. Client-specific CDS table functions

This example demonstrates client-specific CDS table functions.

Source Code

REPORT demo_cds_functions_client.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    IF NOT cl_abap_dbfeatures=>use_features(
          EXPORTING
            requested_features =
              VALUE #( ( cl_abap_dbfeatures=>amdp_table_function ) ) ).
      cl_demo_output=>display(
        `System does not support CDS table functions` ).
      RETURN.
    ENDIF.

    DATA carrid TYPE s_carr_id VALUE 'LH'.
    cl_demo_input=>request( CHANGING field = carrid ).
    carrid = to_upper( carrid ).

    SELECT *
           FROM demo_cds_get_scarr_spfli( carrid = @carrid )
           INTO TABLE @DATA(result1)
           ##db_feature_mode[amdp_table_function].
    cl_demo_output=>write( result1 ).

    SELECT *
           FROM demo_cds_get_scarr_spfli_inpcl( carrid = @carrid )
           INTO TABLE @DATA(result2)
           ##db_feature_mode[amdp_table_function].
    ASSERT result2 = result1.

    SELECT *
           FROM demo_cds_get_scarr_spfli( carrid = @carrid )
                CLIENT SPECIFIED demo_cds_get_scarr_spfli~client
           WHERE client = @sy-mandt
           INTO TABLE @DATA(result3)
           ##db_feature_mode[amdp_table_function].
    cl_demo_output=>display( result3 ).

  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

Description

This example program accesses two client-specific CDS table functions.
  • The CDS table function DEMO_CDS_GET_SCARR_SPFLI does not have any input parameters of the type CLNT. The implementation in the AMDP method GET_SCARR_SPFLI_FOR_CDS of the associated AMDP class CL_DEMO_AMDP_FUNCTIONS reads the data of all clients. Only the current client is selected here in SELECTs performed without the addition CLIENT SPECIFIED.
@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function DEMO_CDS_GET_SCARR_SPFLI
  with parameters carrid:s_carr_id
  returns { client:s_mandt;
            carrname:s_carrname;
            connid:s_conn_id;
            cityfrom:s_from_cit;
            cityto:s_to_city; }
  implemented by method
    CL_DEMO_AMDP_FUNCTIONS=>GET_SCARR_SPFLI_FOR_CDS;
  • The CDS table function DEMO_CDS_GET_SCARR_SPFLI_INPCL has one input parameter CLNT of the type CLNT. The annotation @Environment.systemField is assigned to this parameter with the predefined value #CLIENT. The client ID of the current client is passed to this parameter implicitly in SELECTSs. The implementation in the AMDP method GET_SCARR_SPFLI_FOR_CDS of the associated AMDP class CL_DEMO_AMDP_FUNCTIONS_INPCL uses the input parameter to restrict the results set to the current client.
@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function DEMO_CDS_GET_SCARR_SPFLI_INPCL
  with parameters @Environment.systemField: #CLIENT
                  clnt:abap.clnt,
                  carrid:s_carr_id
  returns { client:s_mandt;
            carrname:s_carrname;
            connid:s_conn_id;
            cityfrom:s_from_cit;
            cityto:s_to_city; }
  implemented by method
    CL_DEMO_AMDP_FUNCTIONS_INPCL=>GET_SCARR_SPFLI_FOR_CDS;

The result of the accesses performed without the addition CLIENT SPECIFIED is the same for both CDS table functions. If the addition CLIENT SPECIFIED is used to access the CDS table function without an input parameter for the client, the results set has an extra client column and the current client must be selected explicitly in the WHERE column. The addition CLIENT SPECIFIED cannot be specified for the CDS table function with an input parameter for the client.

2. Cross-client CDS table functions

This example demonstrates cross-client CDS table functions.

Source Code

REPORT demo_cds_functions_no_client.

CLASS demo DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
ENDCLASS.

CLASS demo IMPLEMENTATION.
  METHOD main.
    IF NOT cl_abap_dbfeatures=>use_features(
          EXPORTING
            requested_features =
              VALUE #( ( cl_abap_dbfeatures=>amdp_table_function ) ) ).
      cl_demo_output=>display(
        `System does not support CDS table functions` ).
      RETURN.
    ENDIF.

    DATA carrid TYPE s_carr_id VALUE 'LH'.
    cl_demo_input=>request( CHANGING field = carrid ).
    carrid = to_upper( carrid ).

    SELECT *
           FROM demo_cds_get_scarr_spfli_nocl( clnt   = @sy-mandt,
                                               carrid = @carrid )
           INTO TABLE @DATA(result1)
           ##db_feature_mode[amdp_table_function].
    cl_demo_output=>write( result1 ).

    SELECT *
           FROM demo_cds_get_scarr_spfli_clnt( clnt   = @sy-mandt,
                                               carrid = @carrid )
           INTO TABLE @DATA(result2)
           ##db_feature_mode[amdp_table_function].
    cl_demo_output=>display( result2 ).

  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo=>main( ).

Description

This example program accesses two cross-client CDS table functions.
  • The CDS table function DEMO_CDS_GET_SCARR_SPFLI_NOCL does not have any elements of the type CLNT.
@ClientDependent: false
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function DEMO_CDS_GET_SCARR_SPFLI_NOCL
  with parameters clnt:abap.clnt,
                  carrid:s_carr_id
  returns { carrname:s_carrname;
            connid:s_conn_id;
            cityfrom:s_from_cit;
            cityto:s_to_city; }
  implemented by method
    CL_DEMO_AMDP_FUNCTIONS_NOCL=>GET_SCARR_SPFLI_FOR_CDS;
  • The CDS table function DEMO_CDS_GET_SCARR_SPFLI_CLNT has one element client of the type CLNT.
@ClientDependent: false
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function DEMO_CDS_GET_SCARR_SPFLI_CLNT
  with parameters clnt:abap.clnt,
                  carrid:s_carr_id
  returns { client:s_mandt;
            carrname:s_carrname;
            connid:s_conn_id;
            cityfrom:s_from_cit;
            cityto:s_to_city; }
  implemented by method
    CL_DEMO_AMDP_FUNCTIONS_CLNT=>GET_SCARR_SPFLI_FOR_CDS;

Both CDS table functions have an input parameter clnt of the type CLNT without the annotation @Environment.systemField. The client ID of the current client is passed explicitly to this parameter by the statement SELECT. The implementations in the AMDP method GET_SCARR_SPFLI_FOR_CDS of the associated AMDP classes CL_DEMO_AMDP_FUNCTIONS_NOCL or CL_DEMO_AMDP_FUNCTIONS_CLNT use this input parameter in their WHERE conditions to read the data of the current client only. Both results sets have the same number of rows and are distinguished only by the extra client column of the function DEMO_CDS_GET_SCARR_SPFLI_CLNT.

Note: In both table functions, the input field for the client ID could also be annotated with the predefined value #CLIENT using the annotation @Environment.systemField to stop the client ID from being passed explicitly by SELECTs.

➤ ABAP CDS - DDL Statements

The data definition language DDL in ABAP CDS is used to create CDS data definitions. The DDL follows the general CDS syntax rules and comprises the following statements for the definition and enhancement of CDS entities:
  • Statements for CDS views
    • DEFINE VIEW
    • EXTEND VIEW
  • Statements for CDS table functions
    • DEFINE TABLE FUNCTION
  • Language elements for various CDS entities
    • Input parameters
    • Typings
    • Element annotations
Note

DDL has its own editor in ADT, which is documented there.

➥ ABAP CDS - DEFINE VIEW

Syntax

@AbapCatalog.sqlViewName: 'CDS_DB_VIEW'
[@view_annot1]
[@view_annot2]
...
[DEFINE] VIEW cds_entity [ name_list] [ parameter_list] AS select_statement [;]

Effect

Defines a CDS view in the ABAP CDS in a CDS source code. A CDS is implemented using a query select_statement. The annotation AbapCatalog.sqlViewName must be specified before the view itself is defined using DEFINE VIEW. Further annotations view_annot1, view_annot2, ... can also be specified. This is optional.

Two objects are created for a CDS view (of the ABAP CDS) that is defined using DEFINE VIEW. A name must be specified for each of the two objects:
  • The name CDS_DB_VIEW of the CDS database view must be specified in quotation marks after the annotation @AbapCatalog.sqlViewName. This view is the technical foundation of the CDS view in ABAP Dictionary. The usual rules for ABAP Dictionary views apply to this name and it is not case-sensitive (it is transformed internally into uppercase letters). The associated SQL view is created under this name on the database. The name given to the database view can no longer be changed after the CDS view is transported into a follow-on system.
  • The name cds_entity of the CDS entity is defined after the keywords DEFINE VIEW (DEFINE is optional). No quotation marks need to be specified. This name follows the rules of the CDS database view, but can have 30 characters. The CDS entity represents all properties of the CDS view.
Both names are in the namespace of the data types in ABAP Dictionary and must each be unique. The name cds_entity of the CDS entity can be used in other DDL statements or in ABAP programs to access the CDS view.

The information specified in name_list can be used to define the names of the elements of the view in a name list. parameter_list can be used to assign input parameters to the view. These input parameters can be specified in operand positions of the view and can be assigned actual parameters when the view is used.

Notes
  • The CDS database view of a CDS view defined using DEFINE VIEW can be displayed by entering the name CDS_DB_VIEW in the ABAP Dictionary tool in ABAP Workbench. However the view cannot be edited here. Furthermore, this display does not provide all information. The CDS entity cds_entity cannot be displayed here.
  • The CDS database view CDS_DB_VIEW can also be accessed directly in the DDL of the CDS and in ABAP. It is strongly recommended, however, that only the CDS entity is used, since only this entity covers all properties of the CDS view. This makes the use of the CDS database view in Open SQL obsolete.
  • ABAP annotations can be used to assign further technical and semantic attributes to a view in ABAP Dictionary. Component annotations can be used to give the view specific semantic attributes for other SAP frameworks.
  • If a CDS role is defined for a CDS entity using the DCL statement DEFINE ROLE, implicit access control applies by default when the CDS entity is accessed using Open SQL or an SADL query. CDS access control can be disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck.
  • If a CDS entity is accessed using an Open SQL SELECT statement and the name cds_entity, the syntax check is performed in strict mode, which handles the statement more strictly than the normal syntax check.
  • Every CDS view has its own CDS source code. The CDS source code in a CDS view is edited in a different editor from the CDS source code of CDS role. The ADT documentation describes how the different types of source code are created.
  • The CDS source code of a CDS view does not need to have the same name as the CDS entity, but it is advisable to use this name.
  • After a piece of CDS source code is transported, the combination of its name and the name of the CDS view defined in it and its CDS database view is defined and can no longer be modified by being renamed.
  • The programming of CDS views using the DDL of the Core Data Services is one of the tasks included in the implementation of data models in ABAP Dictionary and is not usually the responsibility of regular ABAP application programmers. Once created, CDS views are used in ABAP programs using Open SQL read statements and must be stable enough to allow this.
Example

The following CDS view works in exactly the same way as the classic projection view DEMO_SPFLI. The program DEMO_CDS_PROJECTION uses SELECT to access the view. Unlike when the classic database view DEMO_SPFLI is accessed, no client column is returned when the CDS entity DEMO_CDS_SPFLI is accessed. The CDS database view DEMO_CDS_PRJCTN returns the client column too.

@AbapCatalog.sqlViewName: 'DEMO_CDS_PRJCTN'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_spfli
  as select from spfli
     { key spfli.carrid,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }

1. ABAP CDS - DEFINE VIEW, view_annot

Syntax

... @annotation ...

Effect

Specifies an annotation annotation as a view annotation in the definition of a CDS view of the ABAP CDS before the statement DEFINE VIEW. The name annotation of the annotation must be prefixed directly with the character @.

The following tables show the possible ABAP annotations that can be specified, and their meanings. The ABAP annotations are evaluated by the ABAP runtime environment for every CDS entity. Annotations with other identifiers are usually SAP annotations. These are not evalutated by an ABAP run time environment but by other SAP frameworks.

➽ AbapCatalog annotations
➽ AccessControl annotations
➽ ClientDependent annotations
➽ DataAging annotations
➽ EndUserText annotations
➽ Examples

The first column displays the name annotation of an ABAP annotation and the second column displays its meaning. The third column shows the possible values value. The fourth column displays the value, which has been set implicitly for value, if the annotation is not used explicitly. If nothing is specified for value, the annotation should be specified without a value.

➽ AbapCatalog annotations

Annotation Meaning Possible Values Default Value
AbapCatalog.buffering.status Enables and disables SAP buffering for the CDS view #ACTIVE:
SAP buffering active

#SWITCHED_OFF:
SAP buffering allowed but not active

#NOT_ALLOWED:
SAP buffering not allowed
#SWITCHED_OFF
AbapCatalog.buffering.type Buffering type in SAP buffering #SINGLE:
Single records

#GENERIC:
Generic area

#FULL:
Full

#NONE:
None
#NONE
AbapCatalog.buffering.numberOfKeyFields Number of key elements in SAP buffering of generic areas Number between 0 and k-1, where k is the number of key elements 0
AbapCatalog.compiler.compareFilter Defines the evaluation of filter conditions in path expressions of the CDS view true:
The filter conditions are compared and, if they match, the associated join expression is evaluated only once.

false:
A separate join expression is created and evaluated for each filter condition.
false
AbapCatalog.sqlViewName Name of the CDS database view in the CDS view Character string with a maximum of 16 characters consisting of letters, numbers and underscores and that starts with a namespace prefix.  -

Notes
  • Each definition of a CDS view must contain the ABAP annotation AbapCatalog.sqlViewName, which defines the name of the CDS database view in ABAP Dictionary.
  • The name given to the database view can no longer be changed after the CDS view is transported into a follow-on system.
➽ AccessControl annotations

AnnotationMeaningPossible ValuesDefault Value
AccessControl.authorizationCheckDefines the implicit access control when Open SQL is used to access the CDS view#CHECK:
If Open SQL is used to access the view, access control is performed implicitly if a CDS role is assigned to the view. If there is no role for the view, a syntax check warning occurs.

#NOT_REQUIRED:
Like #CHECK, but there is no syntax check warning.

#NOT_ALLOWED:
No access control is performed. This produces a syntax check warning in the DCL source code of a role for the view.
#CHECK

➽ ClientDependent annotations

AnnotationMeaningPossible ValuesDefault Value
ClientDependentDefines client handling when Open SQL is used to access the CDS view.true:
The CDS view is client-specific. The view fields of the CDS entity do not cover a client column, from the perspective of an ABAP program. When accessed using SELECT, automatic client handling is performed.

false:
The CDS view is a cross-client view. No automatic client handling is performed.
true

Note

If a CDS view is configured as cross-client with the ABAP annotation ClientDependent false, all CDS views that use this view as a data source are also cross-client.

➽ DataAging annotations

AnnotationMeaningPossible ValuesDefault Value
DataAging.noAgingRestrictionDefines the handling of obsolete data on a SAP HANA database when Open SQL is used to access the CDS viewtrue:
Open SQL reads all data

false:
Open SQL reads current data only
false

➽ EndUserText annotations

AnnotationMeaningPossible ValuesDefault Value
EndUserText.labelTranslatable short text of the CDS viewCharacter string with maximum 60 characters-

Note

ABAP annotations introduced using EndUserText are used to define translatable semantic texts for a CDS object. The value of an annotation like this is saved in special tables that have a language key and that are translatable. The value specified in the source code should consist of text in the original language of the CDS source code and is translated into the required languages. The methods of the class CL_DD_DDL_ANNOTATION_SERVICE read these texts in accordance with an input parameter for the language. There are also special methods GET_LABEL_... that are designed for these texts only.

➽ Examples

Example

Defines additional attributes with ABAP annotations for the CDS view business_partner (except for the mandatory @AbapCatalog.sqlViewName with the name BPA_VW for the ABAP repository). The short text Business partner and the CDS view are cross-client.

@AbapCatalog.sqlViewName: 'BPA_VW'
@EndUserText.label:       'Business partner'
@ClientDependent:         false
define view business_partner as
  select from snwd_bpa
         { key snwd_bpa.bp_id as id,
               snwd_bpa.bp_role as role,
               snwd_bpa.company_name,
               snwd_bpa.phone_number }

Example

Activates SAP buffering with single record buffering for the CDS view business_partner.

@AbapCatalog.sqlViewName:      'BPA_VW'
@AbapCatalog.buffering.status: #ACTIVE
@AbapCatalog.buffering.type:   #SINGLE
define view business_partner as
  select from snwd_bpa
         { key snwd_bpa.bp_id as id,
               snwd_bpa.bp_role as role,
               snwd_bpa.company_name,
               snwd_bpa.phone_number }

Example

For the CDS view business_partner, activates SAP buffering for a generic area and a key element.

@AbapCatalog.sqlViewName:                 'BPA_VW'
@AbapCatalog.buffering.status:            #ACTIVE
@AbapCatalog.buffering.type:              #GENERIC
@AbapCatalog.buffering.numberOfKeyFields: 1
define view business_partner as
  select from snwd_bpa
        { key snwd_bpa.bp_id as id,
              snwd_bpa.bp_role as role,
              snwd_bpa.company_name,

              snwd_bpa.phone_number }

2. ABAP CDS - DEFINE VIEW, name_list

Syntax

... ( name1, name2, ... ) ...

Effect

Defines the element names of a CDS view in ABAP CDS in a name list. The specified names name1, name2, ... are assigned to the elements defined explicitly in the SELECT list of the SELECT statement in the order given. The names work like the alternative names defined in the SELECT list using AS and overwrite these names. If a name list is specified, it must contain a name for each element of the view.

The view field is created under the element name in the CDS database view. Accordingly, an element name must comply with the rules for names of view fields of database views, as well as the general naming rules for names:

which means that it must also meet the
  • naming conventions for component names of structures in ABAP Dictionary and
  • cannot be a reserved component name. The reserved names that cannot be used are in the database table TRESE.
A name list can be specified only if the elements in the SELECT list are specified explicitly. No name lists can be used if the SELECT list is specified as * or if the element list contains the entry $EXTENSION.*.

Notes
  • An explicit name list can be used, for example, to define the element names of a CDS view that defines a union set of multiple SELECT statements using UNION.
  • A CDS view with an explicit name list cannot be expanded using EXTEND VIEW.
Example

Defines the names of the four elements of the CDS view business_partner as id, role, company_name, and phone_number.

@AbapCatalog.sqlViewName: 'BPA_VW'
define view business_partner
  (id, role, company_name, phone_number) as
  select from snwd_bpa
         { key snwd_bpa.bp_id,
               snwd_bpa.bp_role,
               snwd_bpa.company_name,
               snwd_bpa.phone_number }

Example

Defines the names of the three elements of the CDS view employee_sales_figures as financial_year, employee_id, and gross_amount.

@AbapCatalog.sqlViewName: 'SALES_FIG_VW'
view employee_sales_figures
  (financial_year, employee_id, gross_amount) as
   select from sales_2011_tab
          { key '2011' as year,
            key id,
            amount }
   union
     select from sales_2012_tab
            { '2012' as year,
              employee_number,
              gross_amount }

3. ABAP CDS - DEFINE VIEW, parameter_list

Syntax

... WITH PARAMETERS parameter1, parameter2, ...

Effect

Defines input parameters parameter1, parameter2, ... in a CDS view in ABAP CDS in a comma-separated list.

An input parameter called pname can be used as an operand in the following places in the SELECT statement of the view using the syntax :pname or $parameters.pname:
  1. Element in the SELECT list.
  2. Operand of an arithmetic expression.
  3. Operand operand directly after case in a case distinction.
  4. Right side of an expression cond_exp in a WHERE clause or HAVING clause.
  5. Right side of an expression cond_exp in an ON condition in an ABAP join or an association.
  6. Right side of an expression cond_exp in a filter condition of a path expression.
Example

The following CDS view has a list of three input parameters used in the WHERE clause of the SELECT statement. For passing actual parameters in a SELECT statement in ABAP, see SELECT, CDS View with Input Parameters.

@AbapCatalog.sqlViewName: 'DEMO_CDS_PARA'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_parameters
  with parameters p_distance_l:S_DISTANCE,
                  p_distance_o:S_DISTANCE,
                  p_unit:S_DISTID
  as select from spfli        
            { key carrid,
              key connid,
                  cityfrom,
                  cityto,
                  distance,
                  distid }
            where distid = :p_unit and
                           distance between :p_distance_l
                                        and :p_distance_o;

4. ABAP CDS - SELECT

Syntax

SELECT [DISTINCT] { select_list
                    FROM data_source
                    [association1 association2 ...] }
                | { FROM data_source
                    [ association1 association2 ...]
                    {select_list} }
                  [clauses]

Effect

The SELECT statement defines a query performed on the data sources specified in data_source for a CDS view in ABAP CDS. Possible data sources are database tables defined in ABAP Dictionary, classic views, or other CDS views.
  • select_list defines the components read in a list.
  • association1, association2, ... define associations for the current SELECT statement. These associations can be accessed in data_source and in select_list using path expressions.
  • DISTINCT removes duplicates from the results list.
Both forms of the code have the same semantics. Curly brackets { } must be used in the second variant to set select_list.

The optional additions clauses are SELECT clauses that enable conditions, groupings, and union sets to be specified or created.

Example

The CDS view business_partner_role defined here is a view of an existing CDS view, business_partner. An element bp_role is defined in the SELECT list and given the comment Business partner role. Any duplicate values of the element are removed using DISTINCT.

@AbapCatalog.sqlViewName: 'BPA_ROLE_VW'
define view business_partner_role as
  select distinct
         from business_partner
         { business_partner.role //Business partner role
         }

The CDS view business_partner_role can be addressed in ABAP programs using Open SQL, for example:

SELECT * FROM business_partner_role INTO TABLE @itab ...


The addition DISTINCT of SELECT is no longer needed in this case.

1. ABAP CDS - SELECT, data_source


Syntax

... entity[ parameters]| path_expr [[AS] alias] [ join] ...

Effect

Defines a data source of a CDS view in ABAP CDS. A data source can be a database table defined in ABAP Dictionary, a classic view, an external view, or a CDS entity.
  • The data source is specified either directly using its name entity or using a path expression path_expr in which associations are evaluated. The path expression must be closed by an association.
  • If the data source is a CDS view with a list of input parameters, actual parameters must be passed to these using parameters.
  • An alternative name alias for a directly specified data source can be defined after AS. In fact, AS can be left out. An alternative name must be defined if a path expression is used. The name must comply with the naming rules for names.
  • The syntax join can be used to join multiple data sources as joins.
Note

Only transparent tables can be accessed using CDS views. A pooled table or cluster table can be specified as a data source. In this case, no CDS database view is created when the table is activated. When the CDS entity is accessed using the Open SQL statement SELECT, a syntax check warning occurs. When the statement is executed, an exception is raised. This enables CDS views for pooled tables or cluster tables to be created that exist in follow-on systems as transparent tables.

Example

The CDS view business_partner is defined for the database table snwd_bpa, for which an alternative name partner is specified. This name is used in the SELECT list.

@AbapCatalog.sqlViewName: 'BPA_VW'
define view business_partner as
  select from snwd_bpa as partner
         { key partner.bp_id,
           partner.company_name,
           partner.bp_role }

as can also be omitted in front of the alternative name partner. The following example demonstrates another valid spelling that is potentially confusing:

@AbapCatalog.sqlViewName: 'BPA_VW'
define view business_partner as
  select from snwd_bpa
           partner{ key partner.bp_id,
                    partner.company_name,
                    partner.bp_role }

➠ ABAP CDS - SELECT, parameters

Syntax

... ( pname1 : act1, pname2 : act2, ... ) ...

Effect

Passes actual parameters act1, act2, ... to the input parameters pname1, pname2, ... of a CDS view.

The following can be specified for actual parameters:
  • Literals
  • Parameters
  • Session variables
The data types of the actual parameters should match the typing of the input parameters exactly. However the following options are also possible:
  • bind character-like actual parameters to character-like input parameters with a different length.
  • bind numeric actual parameters to numeric input parameters with a greater value range.
Example

The following CDS view uses the CDS view demo_cds_parameters in a join. The input parameters of this view are supplied with the input parameters of the current view as actual parameters. The program DEMO_CDS_PARAMETERS_JOIN uses SELECT to access the view. Here the input parameters are supplied with actual parameters.

@AbapCatalog.sqlViewName: 'DEMO_CDS_PARJOIN'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_parameters_join
  with parameters in_distance_l:S_DISTANCE,
                  in_distance_o:S_DISTANCE,
                  in_unit:S_DISTID
  as select from demo_cds_parameters
                   ( p_distance_l : $parameters.in_distance_l,
                     p_distance_o : $parameters.in_distance_o,
                     p_unit       : $parameters.in_unit ) as flights  
            join scarr on scarr.carrid = flights.carrid  
            { key scarr.carrname,
              key flights.connid,
                  flights.cityfrom,
                  flights.cityto,
                  flights.distance,
                  flights.distid };

➠ ABAP CDS - SELECT, JOIN

Syntax

... { [INNER] JOIN }|{ LEFT|RIGHT OUTER JOIN } data_source ON cond_expr ...

Effect

Defines a join between two data sources of a CDS view in ABAP CDS. The code above is part of the syntax of a data source data_source and contains the recursive syntax of a data source data_source. Two data sources joined using JOIN create a join expression.

In every join expression, a join condition cond_expr must be specified after ON. When specified, special rules apply to this condition.

Both inner and outer joins are possible:
  • A join between two data sources using INNER JOIN or just JOIN selects all entries of the data sources whose fields meet the ON condition.
  • A join between two data sources using LEFT OUTER JOIN selects all entries on the left side. A join between two data sources using RIGHT OUTER JOIN selects all entries on the right side. Entries that meet the ON condition have the same content as in the inner join. In entries that do not meet the ON condition, the elements on the right or left side have the null value that is set to a type-friendly initial value when the CDS view is used in Open SQL.
In nested join expressions, the order of the evaluation is specified by the arrangement of the ON conditions. From left to right, the most adjacent ON conditions are assigned to each JOIN and this expression is parenthesized implicitly. These implicit parentheses can be made explicit using actual parentheses, ( ). This is optional.

Notes
  • A join condition must always be specified.
  • A WHERE condition for a SELECT statement with joins is applied to the results set created using the joins.
  • In nested join expressions, parentheses are recommended for making the code easier to read. They can be specified wherever the ON conditions specify parentheses implicitly.
Example

The following CDS view works in exactly the same way as the classic database view DEMO_SCARR_SPFLI. The program DEMO_CDS_JOIN uses SELECT to access the view. Unlike when the classic database view DEMO_SCARR_SPFLI is accessed, no client column is returned when the CDS entity DEMO_CDS_SCARR_SPFLI is accessed. The CDS database view DEMO_CDS_JOIN returns the client column too.

@AbapCatalog.sqlViewName: 'DEMO_CDS_JOIN'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_scarr_spfli
  (id, carrier, flight, departure, destination)
  as select from spfli
            join scarr on scarr.carrid = spfli.carrid
     { key spfli.carrid,
       key scarr.carrname,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }

Example

The following non-parenthesized chaining of join expressions

... from tab1
      join
        tab2
          join
            tab3 on tab2.id = tab3.id
                 on tab1.id = tab2.id ...

is parenthesized implicitly as follows:

... from tab1
      join
        ( tab2
            join
              tab3 on tab2.id = tab3.id ) on tab1.id = tab2.id ...

No elements from tab1 can be specified in the inner ON condition.

2. ABAP CDS - SELECT, association


Syntax

... ASSOCIATION [ [min..max] ] TO target [AS _assoc] ON cond_exp ...

Additions

1. ... [min..max]

2. ... AS _assoc

Effect

Defines an association of the name _assoc in a SELECT statement of a CDS view in ABAP CDS. An association associates the current CDS view as a source data source with the target data source target specified in the definition of the association using an ON condition cond_exp . A data source target can be a database table defined in ABAP Dictionary, a classic view, an external view, or a CDS entity.

An association of a SELECT statement in a CDS view can be accessed as follows:
  • By specifying its name in a path expression in the same statement and in all places where this is documented.
  • If an association is published using a path expression in the SELECT list of the current SELECT statement,
    • other CDS views
    • or Open SQL statements
can use it in their path expressions.

When a CDS view is activated with path expressions, every association specified here is transformed to a join expression. The source data source represents the left side and the target data source represents the right side. The ON condition of the association is added to the ON condition of the join. By default, the category of the join is determined by where the path expression is used:
  • After FROM, it is an inner join (INNER JOIN)
  • In all other locations, it is a left outer join (LEFT OUTER JOIN)
This setting can be overwritten when specifying the association in a path expression using an attribute.

When specifying the ON condition, the following special rules apply:
  • If the association in the SELECT list of the current SELECT statement is published, the fields of the source data source specified in the ON condition must also be listed in the SELECT list. This ensures that a join expression can be built from the association (when used in a path expression). In the ON condition, the field name can be prefixed by $projection instead of the name of the source data source. An alternative element name defined using AS can be used here instead of the field name.
  • The fields of the target data source must be prefixed in the ON condition by the name of the association (prefix _assoc. separated by a period).
Notes:
  • Associations not listed in the SELECT list can only be used in path expressions of the current SELECT statement.
  • The syntax for defining and using associations is a high-value wrapping of the syntax for joins. Using associations instead of directly programming joins makes it easier to read the definition of a CDS view. Associations can be used to model relationships between CDS entities that can be accessed simply using path expressions in CDS views or in Open SQL.
  • When a CDS view is activated, a join defined by an association is built for every use in a path expression and not for the definition of the association. No joins are constructed for associations that are not used in their CDS views.
  • Associations and join expressions can be used in a SELECT statement of a CDS view.
  • Cyclical dependencies should be avoided when using associations to prevent problems occurring in mass activations of CDS entities. 
Addition 1

... [min..max]

Effect

Defines the cardinality of the target data source of a CDS view, which is defined with an association ASSOCIATION. The square brackets [ ] are part of the syntax. For min and max, positive integers (including 0) and asterisks (*) can be specified:
  • max cannot be 0.
  • An asterisk * for max means any number of rows.
  • min can be omitted (set to 0 if omitted).
  • min cannot be *.
  • When an association is used in a WHERE condition, 1 must be specified for max.
If the cardinality is not explicitly defined, the cardinality "to 1" is implicitly used ([min..1]).

When specified, the cardinality is used mainly to document the semantics of the data model. The cardinality is not validated at runtime but can produce syntax check warnings.

Note: The specified cardinality is evaluated by the syntax check for paths specified in the CDS DDL or in Open SQL. Currently, a warning is produced if a value greater than 1 is specified for max, indicating that a path specified in this way influences the cardinality of the results set.

Addition 2

... AS _assoc

Effect

Defines the name _assoc of an association defined using ASSOCIATION of a CDS view. If no name is explicitly defined with AS, _assoc is set implicitly to the name of the target data source. The name _assoc must comply with the naming rules for names.

Note: We recommend the naming convention of using an underscore _ as the first character of the association name.

Example

Example of a simple association. The following CDS view provides the same result as the CDS view DEMO_CDS_SCARR_SPFLI in the joins example, as shown in the program DEMO_CDS_ASSOCIATION using an assertion. Furthermore, the association spfli_scarr is published to be used from outside in the SELECT list by specifying a path that contains only the name of an association. The program DEMO_CDS_ASSOCIATION also shows how the association can be accessed by specifying a path in Open SQL.

@AbapCatalog.sqlViewName: 'DEMO_CDS_ASSOC'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_association
  (_spfli_scarr, id, carrier, flight, departure, destination )
  as select from spfli
            association [1..1] to scarr as _spfli_scarr
              on $projection.carrid = _spfli_scarr.carrid
     { _spfli_scarr,
       key spfli.carrid,
       key _spfli_scarr.carrname,
       key spfli.connid,
       spfli.cityfrom,
       spfli.cityto }

Example

The following CDS view sales_order_invoice_header returns information about sales invoices and works with the following databases: snwd_so_inv_head, snwd_so, snwd_bpa, snwd_so_inv_item.

Two associations are defined:
  • _buyer stands for a join between the current view and the target data source snwd_bpa.
  • _invoice_items stands for a join between the current view and the target data source snwd_so_inv_item.
The source data source fields used in the ON conditions - node_key and buyer_guid - are part of the SELECT list. Here the recommended prefix $projection is used instead of the prefixes snwd_so_inv_head or snwd_so_inv_head.

The association _buyer is not listed in the SELECT list and can only be used in path expressions of the current SELECT statement. This association can be specified in the WHERE condition due to the cardinality [1..1]. Association _invoice_items is not accessed in path expressions of the current SELECT statement. However, this association is listed in the SELECT list, which means it can be used in path expression of other CDS views. This association cannot be specified in a WHERE condition due to the cardinality [1..*].

@AbapCatalog.sqlViewName: 'SALESO_INVHDR_VW'
define view sales_order_invoice_header as
  select from snwd_so_inv_head
           inner join snwd_so
             on snwd_so_inv_head.so_guid = snwd_so.node_key
         association [1..1] to snwd_bpa as _buyer
           on $projection.buyer_guid = _buyer.node_key
         association [1..*] to snwd_so_inv_item as _invoice_items
           on $projection.node_key = _invoice_items.parent_key
         { key snwd_so_inv_head.node_key,      //used in assoc _invoice_items
               snwd_so_inv_head.buyer_guid,    //used in assoc _buyer
               snwd_so.so_id as sales_order_id,
               _buyer.bp_id as buyer_id,       //from assoc _buyer
               snwd_so_inv_head.payment_status,
              @Semantics.currencyCode
               snwd_so_inv_head.currency_code,
              @Semantics.amount.currencyCode: 'currency_code'
               snwd_so_inv_head.gross_amount,
               _invoice_items                  //publish assoc _invoice_items
         }
          where _buyer.bp_role = '001';          //usage of assoc buyer

The CDS view can be accessed in an ABAP program with a simple SELECT statement (Open SQL).

SELECT sales_order_id, buyer_id, payment_status
       FROM sales_order_invoice_header
       INTO CORRESPONDING FIELDS OF TABLE @itab.

The complexity of the actual query is wrapped transparently in the CDS view for the application programmer. When the view is accessed, the join (defined by the association _invoice_items) between snwd_so_inv_head and snwd_so_inv_item is not built, because there are no path expressions that need to access the join.

The CDS view sales_order_invoice_header mentioned above is used as the data source in the definition of the CDS view sales_order_invoice_items. This data source is used to access the published association _invoice_items. The elements of the association are accessed in this view. There is no visual indication that it is the result of a join. This join between snwd_so_inv_head and snwd_so_inv_item is created when the CDS view sales_order_invoice_items is activated. The other association _buyer of the CDS view sales_order_invoice_header cannot be accessed.

@AbapCatalog.sqlViewName: 'SALESO_INVITM_VW'
define view sales_order_invoice_items as
  select from sales_order_invoice_header as header
  { header.sales_order_id,
    header._invoice_items.inv_item_pos as item_position,
   @Semantics.currencyCode
    header._invoice_items.currency_code,
   @Semantics.amount.currencyCode: 'currency_code'
    header._invoice_items.gross_amount }

➨ ABAP CDS - path_expr

Syntax

... [viewEntity.]_assoc1[ parameters][attributes]
               [._assoc2[ parameters][ attributes] ... ][.element] ...

Effect

Defines a path expression in a SELECT statement of a CDS view in ABAP CDS. A path expression is a string of associations separated by periods (.) whose names are specified using _assoc1, _assoc2 and so on.

Path expressions can be specified in the DDL of ABAP CDS as data sources data_source, as elements of SELECT lists, and as operands of WHERE conditions or HAVING conditions. Path expressions specified as operands of a condition must be closed using an element. As a non-aggregated element of a SELECT list of aggregate expressions and in a WHERE condition or HAVING condition, the result of the path expression must be monovalent. This means that the cardinality of all associations used is either "to 1" or the path expression can contain only filter conditions that are declared as monovalent using the addition 1:.

The following is evaluated when a CDS view is accessed using a path expression:
  • The joins defined by the associations, from left to right.
  • All other conditions of the CDS views in question.
The path expression addresses the full result of this evaluation or a single element appended using .element.
  • If the path expression is closed using an element, this must be an element of the target data source of the final association in the path.
  • If the path expression is closed using an association, this interpreted as follows depending on the position of the path expression:
    • After FROM, the association is a data source data_source.
    • As an element of a SELECT list, the association is published for use in other CDS views or in Open SQL.
In a WHERE clause or HAVING clause, the path expression must be closed using an element.

The name of the CDS view in which the first association is defined can be specified in front of this association. The first association in a path expression must either be defined in the current CDS view or published as an element in a data source of the current view. All further associations must be published in the target data source of the directly prefixed association in the path expression.
  • attributes can be used to specify attributes in angle brackets after every association. These attributes define the following further properties of this section of the path expression:
    • Declaration of monovalency
    • Category of the join expression
    • Specification of filter conditions
  • If the data source entity of a specified association is a CDS view with a list of input parameters, parameters must be used after the name _assoc to pass actual parameters to them.
Notes
  • The most simple path expression is the name of a single association.
  • DDL path expressions can also be used in conditions in the ABAP CDS DCL.
  • An association whose target data source does not publish an association cannot be followed by any further associations in a path expression. More specifically, no further associations can be made into a path expression after associations whose target data sources are database tables or classic views.
  • The ABAP annotation AbapCatalog.compiler.compareFilter can be used to specify whether the filter conditions are compared for the path expressions of a view. If the filter condition matches, the associated join expression is created only once, which generally improves performance. Otherwise a separate join expression is created for each filter condition. However, the results sets of both configurations can differ.
Example

This example shows three CDS views, sales_order, business_partner, and invoice. The CDS view invoice uses its own association and associations from the other two views in path expressions:
  • The association sales_order of the CDS view business_partner is specified as a data source after FROM. A filter condition guarantees that only certain orders can be used as data sources.
  • The separate association invoice_header is used in a path expression as an operand in the WHERE condition.
  • The association note_header of the CDS view sales_order is addressed using the alternative name bpa in business_partner and defined as an element of the SELECT list. This means this association can also be used in CDS views that use invoice as a data source.
@AbapCatalog.sqlViewName: 'SALES_ORDER_VW'
define view sales_order as
  select from snwd_so
         association [0..1] to snwd_text_key as _note_header
           on snwd_so.note_guid = _note_header.node_key
  { * } // Include all fields from snwd_text_key
@AbapCatalog.sqlViewName: 'BPA_VW'
define view business_partner as
  select from snwd_bpa
         association [0..*] to sales_order
           on snwd_bpa.node_key = sales_order.buyer_guid
  { * }
@AbapCatalog.sqlViewName: 'SALESO_INV_VW'
define view invoice as
  select from
         /* Association "sales_order" with filter as data source */
         business_partner.sales_order[
           lifecycle_status <> 'C' and lifecycle_status <> 'X']
           as bpa_so //alias for data source
         /* Association only used in this view definition */
         association [0..1] to snwd_so_inv_head as _invoice_header
           on bpa_so.node_key = _invoice_header.so_guid
        { key bpa_so.node_key, //Field from ON-condition in _invoice_header
              bpa_so.so_id,
              bpa_so.note_guid, //Field from ON-condition in note_header
              bpa_so.lifecycle_status,
              /* Association is not published, but its element */
              _invoice_header.dunning_level,
              /* Association from data source is published here */
              bpa_so.note_header }
          /* Path expression in WHERE clause */

          where _invoice_header.dunning_level > '0';

➥  ABAP CDS - path_expr, attributes

Syntax

... [ [1:] [INNER|{LEFT OUTER} [WHERE]] [ cond_expr] ] ...

Additions

1. ... 1:

2. ... INNER|{LEFT OUTER} [WHERE]

3. ... cond_expr

Effect

A CDS view of the ABAP CDS attributes for this part of the path expression can be defined in square brackets [ ] for every association _assoc of a path expression. Using these attributes:
  • the section with 1: can be declared as monovalent,
  • the type of join expression can be specified
  • a filter condition cond_expr can be specified 
Addition 1

... 1:

Effect

If the attribute 1: is specified, the current association is not declared as polyvalent. The addition makes it possible to use filter conditions in path expressions, which are used in WHERE clauses or HAVING clauses. The addition overwrites the cardinality of the association in this type of clause.

The addition 1: cannot be specified as the only addition in square brackets.

Note: The addition prevents a syntax error, if a path specification with filter conditions or with a quantity value cardinality is used in a WHERE clause or HAVING clause. However, the system can check at runtime if the required uniqueness is reached by the condition.

Addition 2

... INNER|{LEFT OUTER} [WHERE]

Effect

Definition of the type of join expression where the current expression is implemented:
  • INNER is an inner join
  • LEFT OUTER is a left outer join
If the type of join expression is not specified explicitly, the type depends on the place where the path expression is used:
  • After FROM, it is an inner join (INNER JOIN)
  • In all other locations, it is a left outer join (LEFT OUTER JOIN).
If the type of join expression is defined before filter condition cond_expr, the addition must be listed before addition WHERE. If this is not the case, WHERE cannot be specified.

Addition 3

... cond_expr

Effect

Filter condition for the current association. A filter condition is a condition cond_exp that is implemented when resolving the relevant association in a join in an advanced condition for the join. Special rules apply when specifying the condition.

Note: The ABAP annotation AbapCatalog.compiler.compareFilter can be used to specify whether the filter conditions are compared for the path expressions of a view. If the filter condition matches, the associated join expression is created only once, which generally improves performance. Otherwise a separate join expression is created for each filter condition. However, the results sets of both configurations can differ.

3. ABAP CDS - SELECT, select_list


Syntax

... element1, element2, ...
  | * ...

Effect

Defines the elements of a CDS view in ABAP CDS. The elements can be specified as follows:
  • In a comma-separated list, elements can be defined by specifying element1, element2 (...and so on).
  • * can be used to define all fields of the data source of the CDS view as elements and expose associations of the current view. If another CDS view is used as a data source, the associations exposed in its SELECT list are, however, not exposed by the current CDS view. The asterisk * cannot be specified if the current CDS view contains joins or union sets when the data source is specified.
The names of the elements of a CDS view are either
  • the names of the elements taken from the data sources,
  • alternative element names specified using AS, or
  • the names specified in a name list.
These names are also the names of the view fields of the CDS database view and the corresponding rules that must be kept.

Example

The following CDS view sales_order is a view of database table snwd_so. Three elements - so_id, currency_code and gross_amount - are defined for this view.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW'
define view sales_order as
  select from snwd_so
    { key so_id,
          @Semantics.currencyCode currency_code,
          @Semantics.amount.currencyCode: 'currency_code' gross_amount }

Example

The following CDS view sales_order is a view of all the fields of database snwd_so. Accessing the CDS view has the same effect as when the database itself is accessed.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW'
define view sales_order as
  select from snwd_so
         { * }

➥ ABAP CDS - SELECT, element

Syntax

... { [@element_annot1] 
      [@element_annot2] 
      ... 
        [KEY] { { field 
                | path_expr [AS alias] } 
              | { literal 
                | parameter 
                | session_variable 
                | aggregate 
                | arith_expr 
                | builtin_func 
                | case_expr 
                | cast_expr AS alias } 
      [@<element_annot1] 
      [@<element_annot2] 
      ... } 
  | { $EXTENSION.* } }  ... 

Alternatives:

1. ... field|path_expr|literal|parameter|... 

2. ... $EXTENSION.* 

Effect

Defines an element of a SELECT list of a CDS view in ABAP CDS. 

Alternative 1

... field|path_expr|literal|parameter|... 

Additions

1. ... @element_annot ... @<element_annot 

2. ... KEY 

3. ... AS alias 

Effect

Specifies individual elements.
  • When the name field is specified directly, an element of a data source data_source of the current CDS view is defined as an element. The field name can be prefixed with the name of the data source or its alternative name. The prefix is separated using a period (.). AS can be used to define an alternative element name alias.
  • When a path expression path_expr is specified, two cases arise:
    • If the path expression identifies an element, the target of the path expression is defined as an element. The element is part of the results set of the CDS view, a field of the CDS database view, and a component of a type defined in ABAP with respect to the CDS view.
    • If a path expression is specified that ends with an association, this association is published for use in other CDS views or in Open SQL. A different CDS view can evaluate the association in its path expressions. In the same way, the association can be used in path expressions in Open SQL. Like every element, an association published in this way is part of the SELECT list and must also be specified in a name list. It is not, however, part of the results set, a field of the CDS database view of the CDS view, or a component of a type defined in ABAP with respect to the CDS view. AS can be used to define an alternative element name alias.
  • literal can be used to declare a literal. AS must be used to define an alternative element name alias. The literal can be prefixed with the name of a domain.
  • parameter can be used to specify a parameter from the parameter list parameter_list. If AS is used, an alternative element name alias must be defined, which cannot be the name pname of the parameter.
  • session_variable can be used to specify a session variable. AS must be used to define an alternative element name alias.
  • aggregate, arith_exp, builtin_func, and case_exp can be used to specify aggregate expressions, arithmetic expressions, calls of predefined functions in the database, and case distinctions. The expressions and functions are evaluated when the CDS view is accessed in the database system. If AS is used, an alternative element name alias has to be defined, unless there is a name list.
If an association is published in the SELECT list using a path expression path_expr, all fields of the source data source that occur in the ON condition must also be specified as elements of the SELECT list. If a path expression contains more than one association, this type of element must be listed as a path expression closed by the field.

Note: When publishing an association using a path expression path_expr in the SELECT list, it is not possible to pass actual parameters to any input parameters of the target data source to assign start values to the input parameters.

Example

The following CDS view opens its own association _spfli and the association _sflight published by its target data source demo_cds_assoc_spfli. The fields of the source data source, which are used in the ON conditions of the associations, are specified in the SELECT list. In the case of the association published using _spfli._sflight, path expressions _spfli.carrid and _spfli.connid must be used accordingly. The element scarr occurs twice, which means that an alternative element name must be defined using AS.

@AbapCatalog.sqlViewName: 'DEMO_CDS_PUBASC' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_publish_assoc 
    as select from scarr 
       association to demo_cds_assoc_spfli as _spfli 
                   on scarr.carrid = _spfli.carrid 
    { _spfli, 
      scarr.carrid as scarr_carrid, 
      _spfli._sflight, 
      _spfli.carrid, 
      _spfli.connid } 

Addition 1

... @element_annot ... @<element_annot 

Effect

Specifies annotations for the element. The annotations can be specified before the element using @element_annot or after the element using @<element_annot.

Note: An annotation can be used to assign further technical and semantic attributes to an element in ABAP Dictionary. Component annotations can be used to give the element specific semantic attributes for other SAP frameworks. 

Addition 2

... KEY 

Effect

KEY is used to define the current element as the key element of the current CDS entity. Any elements of a SELECT list can be defined as key elements that are placed without gaps at the start of the list.

The key elements of the CDS entity are evaluated as follows if the CDS view is used as a data source of a SELECT statement in Open SQL.
  • By the addition ORDER BY PRIMARY KEY. In this case, the key elements must be defined at the start of the SELECT list without any gaps.
  • In implicit access control.
In other cases, the key elements of the CDS entity are used to document the semantics of the data model. The addition KEY is ignored when the CDS view is activated and when other accesses are performed in program executions.

More specifically, the key elements defined using KEY are ignored by the key of the CDS database view. Like in classic views, the key fields of the database view are derived implicitly from the key fields of the basis tables and the join conditions. If this is not possible, all fields of the database view are key fields.

Notes: 
In SAP buffering, only the key fields of the CDS database view are relevant and not the key elements of the CDS entity defined using KEY.

In the syntax check in Open SQL, the key elements used depend on whether the name of the CDS entity is specified or the CDS database view (with the latter being obsolete). 

Addition 3

... AS alias 

Effect

Defines an alternative element name for the current element. The alternative element name replaces the actual name of the element from the data source data_source. The view field is created under the alternative element name in the CDS database view. Accordingly, the alternative element name must comply with the rules for names of view fields of database views, as well as the general naming rules for names:

which means that it must also meet the
  • naming conventions for component names of structures in ABAP Dictionary and
  • cannot be a reserved component name. The reserved names that cannot be used are in the database table TRESE.
This is only checked, however, if there is no explicit name list that overrides the alternative element names.

Alternative element names can be used in the current CDS view to grant unique names for identically named elements from different entities of the data source. When the current CDS view is accessed, the alternative element names must be used instead of the actual name. Alternative element names cannot be used within the CDS view, with one exception: alternative element names can be specified directly or after $projection in the ON condition of an association. 

Alternative 2

... $EXTENSION.* 

Effect

Specifies all elements of an enhancement of the enhancement concept for classic objects in ABAP Dictionary. If $EXTENSION.* is specified as an element, all fields that occur in an enhancement of a database table or a classic view in ABAP Dictionary in the data source data_source become elements of the current CDS view automatically.

If $EXTENSION.* is specified, it works only for the current CDS view. It is not applied to other CDS views in whose data source the current CDS view is used or to CDS views in the data source of the current CDS view.

$EXTENSION.* cannot be specified if aggregate expressions aggregate occur in the current SELECT list or if the current CDS view is a union set created using UNION.

Notes: 
  • $EXTENSION.* is specified independently of when an enhancement is made. It is also applied when a database table or a view is enhanced only after the activation of the CDS view.
  • The statement EXTEND VIEW can be used to enhance the current CDS view.
Example

The data source of the CDS view sales_order is an inner join of the database tables snwd_bpa and snwd_so and contains three directly defined elements sales_order_id, business_partner_id, and company_name and (because $EXTENSION.* is specified) all fields that exist due to enhancements in the database tables snwd_bpa and snwd_so. The alternative name partner is defined for the database snwd_bpa and is used in the ON condition. The names of the elements sales_order_id and business_partner_id are alternative element names. The element sales_order_id is defined as a key element.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_bpa as partner 
    inner join 
      snwd_so on partner.node_key = snwd_so.buyer_guid 
  { key so_id as sales_order_id, 
        bp_id as business_partner_id, 
        company_name, //from snwd_bpa 
        $extension.* }

➥ ABAP CDS - SELECT, element_annot

Syntax

... @annotation ... 
... @<annotation ...

Effect

Specifies an annotation annotation as an element notation in the definition of an element of a SELECT list in a CDS view in ABAP CDS. Element annotations can be specified before and after the element.
  • In front of the element, the character @ must be placed before the name annotation of the annotation.
  • Following the element, the characters @< must be placed before the name annotation of the annotation.
The elements of CDS entities can be assigned to the ABAP element annotations grouped under element_annot.

Example

In the CDS view corporation_sales_order, a short text and a tooltip are assigned to the element role. The element currency_code is flagged as a currency key and is assigned as a currency key to the element gross_amount.

@AbapCatalog.sqlViewName: 'CORP_SO_VW' 
define view corporation_sales_order as 
  select from snwd_bpa as business_partner 
           inner join snwd_so as sales_order 
             on business_partner.node_key = sales_order.buyer_guid 
         { key sales_order.so_id as sales_order_id, 
               business_partner.bp_id as corporation_id, 
               business_partner.company_name, 
              @EndUserText.label: 'Corporation Role' 
              @EndUserText.quickInfo: 'Customer or supplier' 
                 business_partner.bp_role as role, 
              @Semantics.currencyCode 
                 sales_order.currency_code, 
              @Semantics.amount.currencyCode: 'currency_code' 
                 sales_order.gross_amount } 
         where business_partner.legal_form = 'Inc.'; //Corporations only

4. ABAP CDS- SELECT, clauses


Syntax

... [WHERE cond_expr]
    [GROUP BY field1, field2, ...]
    [HAVING cond_expr]
    [UNION [ALL] SELECT ...] ...

Effect

The optional clauses of the SELECT statement of CDS view in ABAP CDS have the following semantics:
  • The WHERE clause restricts the rows in the results set when the CDS view is accessed.
  • The GROUP BY clause groups rows in the results set when the CDS view is accessed. It is applied after rows are selected using the WHERE clause.
  • The HAVING clause restricts the results set further after a GROUP BY clause. Here, aggregate expressions can be specified in the condition.
  • UNION can be used to create the union of the results sets from two SELECT statements.
➥ ABAP CDS - SELECT, WHERE

Syntax

... WHERE cond_expr ...

Effect

Defines a WHERE condition for the results set of a CDS view in ABAP CDS. When the CDS view is accessed, the results set contains only the data from the data source data_source that meets the condition cond_expr specified after WHERE.

The fields evaluated in the condition do not need to be defined as elements of the CDS view in the SELECT list. Special rules apply when specifying the condition.

Note

Unlike in the HAVING condition, aggregate expressions cannot be specified in the WHERE condition.

Example

When the CDS view sales_order is accessed, the results set contains only orders where the company name of the business partner starts with "S" and that were created between January and March 2013.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
         association to snwd_bpa as _partner 
                     on snwd_so.buyer_guid = _partner.node_key 
         { key so_id, 
               snwd_so.buyer_guid, 
               _partner.company_name, 
               snwd_so.created_at, 
               @Semantics.currencyCode currency_code, 
               @Semantics.amount.currencyCode: 'currency_code' gross_amount 
         } 
         where _partner.company_name like 'S%' and 
                       created_at between 2013010100000.0 
                                     and 20130401000000.0; //Jan-March

➥ ABAP CDS - SELECT, GROUP BY

Syntax

... GROUP BY field1, field2, ... 
             path_expr1, path_expr2, ...

Effect

Groups those rows in the results set of a CDS view in ABAP CDS that have the same content in the elements specified by the fields field1, field2, ... or path expressions path_expr1, path_expr2 ... as a single row. The fields must be specified using the same names as the fields in the data source data_source. The current CDS view cannot be specified using alternative element names, which are defined with AS.

Any elements of the CDS view that are not specified after GROUP BY must be defined in the SELECT list using aggregate expressions. Conversely, GROUP BY must be used if aggregate expressions are contained in the SELECT list and all elements not defined using aggregate expressions must be specified after GROUP BY. Literals and other expressions cannot be specified after GROUP BY. If expressions or calls of predefined functions are specified as elements of the SELECT list using aggregate expressions, all the fields used must be specified in the GROUP BY clause.

When the CDS view is accessed, the results of the aggregate expressions are calculated from the values of the corresponding fields of the combined rows and the results are placed in the element of the resulting row in the results set.

Note

A WHERE condition is evaluated before the rows are combined using GROUP BY.

Example

When a CDS view is accessed, the view returns sales_order for every role of a business partner and returns the number of business partners and the total of all gross amounts for every currency.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
    inner join 
      snwd_bpa on buyer_guid = snwd_bpa.node_key 
      { bp_role as role, //e.g. customer or supplier 
        count(distinct buyer_guid) as partners_count, 
        @Semantics.currencyCode snwd_so.currency_code, 
        @Semantics.amount.currencyCode: 'currency_code' 
          sum(snwd_so.gross_amount) as sum_gross_amount } 
      group by bp_role, snwd_so.currency_code;

➥ ABAP CDS - SELECT, HAVING

Syntax

... HAVING cond_expr ...

Effect

Defines a HAVING condition for the results set of a CDS view in ABAP CDS after a GROUP BY clause is evaluated. A HAVING condition can only be specified together with GROUP BY. Special rules apply when specifying the condition.

Removes all rows from the results set that do not meet the condition cond_expr specified after HAVING.

Note

Unlike in the WHERE condition, aggregate expressions can be specified in the HAVING condition.

Example

When accessed, the CDS view sales_order returns the number of business partners for each business partner role in which the total gross amount in Euros is greater than 100000.00.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
    inner join 
      snwd_bpa on buyer_guid = snwd_bpa.node_key 
  {  key bp_role as role, //e.g. customer or supplier 
     count(distinct buyer_guid) as partners_count, 
     sum(snwd_so.gross_amount) as sum_gross_amount } 
  where snwd_so.currency_code = 'EUR' 
  group by bp_role 
  having sum(snwd_so.gross_amount) > 100000.00;

➥ ABAP CDS - SELECT, UNION

Syntax

... UNION [ALL] select_statement ...

Effect

Creates the union of rows in the results sets of two SELECT statements of a CDS view in ABAP CDS. A prerequisite is that the structures of the results sets are compatible. This means that the results sets must have the same number of elements and that the pairs of elements in each position have a compatible data type. If no name list is specified, the element names of the result sets must match.

A union results set can itself be the left side of a further union. The properties of the union results set are defined as follows:
  • The element names are taken either from an explicitly specified name list or, if no list is specified, the matching names from the SELECT lists of all SELECT statements are used.
  • The definition of the key elements is taken from the SELECT list of the first SELECT statement.
  • The data type of each element is a data type from ABAP Dictionary whose value range covers the data types of the associated columns of all results sets involved.
  • If the addition ALL, is not specified, all duplicate entries are removed from the results set. They are not removed if ALL is specified.
In CDS views where UNION is used to create union sets, no associations can be defined in SELECT statements.

Notes
  • Union sets can be a good way of transforming non-standardized database tables into a standardized view of the data.
  • A CDS view with union sets cannot be expanded using EXTEND VIEW.
Examples

Union set without name list. The element names of the SELECT lists must match.

@AbapCatalog.sqlViewName: '...' 
define view ... as 
  select 
    from demo_join1 
      { a as c1, b as c2, c as c3, d as c4 } 
    union 
      select 
        from demo_join2 
          { d as c1, e as c2, f as c3, g as c4 }

Union set with name list. The element names of the SELECT lists do not need to match.

@AbapCatalog.sqlViewName: '...' 
define view ... ( c1, c2, c3, c4 ) as 
  select 
    from demo_join1 
      { a, b, c, d } 
    union 
      select 
        from demo_join2 
          { d, e, f, g }

5. ABAP CDS - SELECT, Operands and Expressions

  • Literals
  • Fields of data sources
  • Parameters
  • Session variables
  • Aggregate expressions
  • Arithmetic expressions
  • Case distinction
  • Casting
  • Conditions
➥ ABAP CDS - literal

Syntax

... [#|:domain.]'character_literal'|numeric_literal ...

Addition:

... #|:domain.

Effect

Literal in a SELECT statement of a CDS view in ABAP CDS. It is either a character literal or a number literal:
  • A character literal 'character_literal' is a character string enclosed in quotation marks. If the character string consists exclusively of digits, the data type of the literal is NUMC. If this is not the case, the data type is CHAR. When activated, the length of the corresponding field in the database system is set to a size at least as large as the actual length of the literal.
  • A numeric literal numeric_literal is a series of digits between 0 and 9. It can be directly prefixed by a + or - sign and can contain a decimal point .) after the first digit. A numeric literal without a decimal point is handled as a field of the type INT1, INT2, or INT4, depending on the value. There is no implicit handling as a field of type INT8. A numeric literal with a decimal point is floating point number of type FLTP.
Literals can be used at all operand positions where this is documented.

Notes
  • 0.5 is a valid numeric literal, but .5 is not.
  • No byte-like literals are currently supported.
Example

The CDS view demonstrates various literals at operand positions.

@AbapCatalog.sqlViewName: 'CORP_SO_VW'
define view corp_sales_order as
  select from snwd_so
         association [1..1] to snwd_bpa as _partner
           on snwd_so.buyer_guid = _partner.node_key
         { key snwd_so.so_id as sales_order_id,
               snwd_so.buyer_guid,
               _partner.bp_id as corporation_id,
               _partner.company_name as corporation_name,
              'corporation' as legal_form,
             @Semantics.currencyCode
              'EUR' as currency_code,
             @Semantics.amount.currencyCode: 'currency_code'
               snwd_so.gross_amount as sales_order_gross_amount }
         where _partner.legal_form = 'Inc.' //Corporations only
           and snwd_so.currency_code = 'EUR'
           and snwd_so.gross_amount > 100000

Addition

... #|:domain.

Effect

In certain operand positions, a literal can be prefixed with a name of a domain domain introduced using the character # or : and separated by a period, .. In this case, a check is made to verify whether the value of the literal is defined as a fixed value in the value range of the domain. If this is not the case or if the domain does not exist as an active domain, a syntax check warning occurs. Apart from this, the literal is handled like a regular literal.

A domain can be specified in front of a literal in the following operand positions:
  • Element of the SELECT list
  • Right side of a comparison condition
  • Parameters passed to predefined functions
Notes
  • In the case of character literals consisting only of letters, numbers, underscores (_), and slashes (/) and that start with a letter, underscore, or slash, the quotation marks can be omitted after the domain.
    • #domain.xXx has the same semantics as
    • #domain.'xXx'.
  • Numeric literals can only be checked using fixed values of numeric domains.
  • Currently, not all potential literal values can be represented as fixed values of domains. For example, a numeric domain can only have positive integers as fixed values and there are no fixed values for domains of type FLTP.
Example

Fixed value EXA of the domain ABDOCTYPE as the operand of the WHERE condition of a view.

AbapCatalog.sqlViewName: 'ABAPDOCTREE'
  define view abapdoc_tree as
  select from abapdocu_tree
         { * }
         where node_type = #ABDOCTYPE.'EXA';

➥ ABAP CDS - field

Syntax

... [entity.]field|[alias.]field ...

Effect

Field of a data source entity of the current CDS view. FIELD expects the actual name of the element. Any alternative element names defined using AS cannot be used, with the exception of the ON condition of an association.

If the field name field is not unique, the data source must be prefixed with a name entity (separated by a period .)) or an alias name alias defined using AS. A name can be prefixed if the field name is unique, but this is not mandatory.

➥ ABAP CDS - parameter

Syntax

... :pname|$parameters.pname ...

Effect

Specifies an input parameter pname from the parameter list parameter_list in an operand position of a SELECT statement of a CDS view in ABAP CDS.

The name of the parameter pname must be prefixed by a colon (:) or $parameters..

Example

Specifies the parameters p1 and p2 using both syntax options in the SELECT list.

@AbapCatalog.sqlViewName: 'SPFLI_PROJECTION'
define view spfli_proj
  with parameters p1 : abap.int4,
                  p2 : abap.int4 as
  select from scarr
         { key carrid,
               :p1 as para1,
               $parameters.p2 as para2
         };

➥ ABAP CDS - session_variable

Syntax

... $session.vname ...

Effect

Specifies a session variable vname in an operand position in a SELECT statement of a CDS view in ABAP CDS. The variable is case-sensitive. $session.vname, $Session.Vname, and $SESSION.VNAME can all be used. No other spellings are allowed.

Session variables have a predefined name and can be set to a predefined value when the CDS view is used in Open SQL. This also applies to CDS views that are used as data sources in other CDS views. The following session variables exist:

vname Value in Open SQL accesses
user Current user name, nominal value of the ABAP system field sy-uname.
client Current client, nominal value of the ABAP system field sy-mandt.
system_language Language of the current text environment, nominal value of the ABAP system field sy-langu

Notes
  • From a technical perspective, session variables indicate global variables of the current database that are set to their value when the CDS view is used in Open SQL. If Open SQL is not used to access a CDS view with session variables, the content of the variables is undefined (with the exception of the SAP HANA database.
  • On a SAP HANA database used as a central AS ABAP database, the ABAP-specific session variables are called APPLICATIONUSER, CLIENT, and LOCALE_SAP and exist and are set independently of ABAP CDS and Open SQL. They can be accessed using the database function SESSION_CONTEXT.
  • For input parameters of CDS views annotated with the special annotation environment.systemField, Open SQL can pass the values of the session variables specified here automatically too. It is generally preferable to use this type of local input parameter.
  • In existing CDS views without corresponding input parameters, session variables can be passed to the input parameters of CDS views or CDS table functions used there.
  • If the Open SQL statement SELECT is used to access a CDS view that uses session variables, the additions USING CLIENT and CLIENT SPECIFIED cannot be used.
Example

The following CDS view contains the possible session variables in the SELECT list. The program DEMO_CDS_SESSION_VARIABLES uses Open SQL to access the view and fills the session variables with the associated values.

@AbapCatalog.sqlViewName: 'DEMO_CDS_SESSVAR' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_session_variables 
   as 
   select 
     from demo_expressions 
       { id, 
         $session.user            as system_user, 
         $session.client          as system_client, 
         $session.system_language as system_language     
        } 
  
Example

The following CDS view DEMO_CDS_SELECT_T100 accesses a further CDS view DEMO_CDS_SELECT_T100_LANGU with an input parameter for the language to which the annotation environment.systemField with the value system_language is assigned. Unlike in Open SQL, there is no implicit pass of the actual parameter to the input parameter here and the corresponding session variable $session.system_language is passed instead. The program DEMO_CDS_T100 uses Open SQL to access both views and the result is identical. When DEMO_CDS_SELECT_T100_LANGU is accessed, the value of the system field sy-langu is passed implicitly to the input parameter. When DEMO_CDS_SELECT_T100 is accessed, the session variable $session.system_language is filled with this value.

@AbapCatalog.sqlViewName: 'DEMO_CDS_T100' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_select_t100 
  as select from 
    demo_cds_select_t100_langu( p_langu:$session.system_language ) 
    { * } 
@AbapCatalog.sqlViewName: 'DEMO_CDS_T100_LG' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_select_t100_langu 
  with parameters @Environment.systemField:#SYSTEM_LANGUAGE 
                  p_langu : lang 
  as select from t100 { * } 
     where sprsl = :p_langu and 
           arbgb = 'SABAPDEMOS'

➥ ABAP CDS - aggregate

Syntax

... { MAX( [DISTINCT] operand )
    | MIN( [DISTINCT] operand )
    | AVG( [DISTINCT] operand )
    | SUM( [DISTINCT] operand )
    | COUNT( DISTINCT operand )
    | COUNT(*) } ...

Effect

Aggregate expression in a SELECT statement of a CDS view in ABAP CDS. An aggregate expression calculates a single value from an operand operand by calling an aggregate function from multiple rows of a results set. The following table shows the possible aggregate functions:

Aggregate Function Meaning
MIX Greatest value of operand
MIN Least value of operand
 AVG Average value of operand (the operand must be numeric). The type INT8 and the types DF16_... and DF34_... for decimal floating point numbers are not supported.
SUM Sum of operand (the operand must be numeric).
COUNT If DISTINCT operand is specified, the number of distinct values of operand; if * is specified, the number of rows in the results set.

If DISTINCT is used, only distinct values of operand are respected. The following can be specified for operand:
  • A literal
  • A field of a data source data_source of the current CDS view
  • A path expression that identifies a field of a data source data_source
  • A case distinction CASE that returns a value
The data type of the result is defined when the CDS view is activated and is determined by the aggregate function and the value range of the data types of the operands.

Aggregate expressions can be used as elements of a SELECT list. Here they need an alternative element name defined using AS and require a GROUP BY clause to be used. Aggregate expressions can be compared with literals in a HAVING condition.

Notes
  • The names of the aggregate functions AVG, COUNT, MAX, MIN, and SUM are protected and cannot be used as self-defined names.
  • A CDS view with aggregate expressions in the SELECT list cannot be expanded using EXTEND VIEW.
Example

In the SELECT list of the CDS view sales_order, aggregate expressions calculate the sum, the minimum, and the maximum of the gross amounts of each business partner. Also, the number of requests is determined. An alternative element name is defined for each aggregation. The non-aggregated fields buyer_guid and currency_code are specified in the GROUP BY clause.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
  { key buyer_guid, 
       @Semantics.currencyCode 
        currency_code, 
       @Semantics.amount.currencyCode: 'currency_code' 
        sum(gross_amount) as sum_gross_amount, 
       @Semantics.amount.currencyCode: 'currency_code' 
        min(gross_amount) as min_gross_amount, 
       @Semantics.amount.currencyCode: 'currency_code' 
        max(gross_amount) as max_gross_amount, 
       @Semantics.amount.currencyCode: 'currency_code' 
        avg(gross_amount) as avg_gross_amount, 
        count(*) as sales_orders_count } 
  group by buyer_guid, currency_code

➥ ABAP CDS - arith_expr

Syntax

... [-]operand1 [+|-|*|/ [-]operand2 [+|-|*|/ [-]operand3 ... ]] ...

Effect

Arithmetic expression in a SELECT statement of a CDS view in ABAP CDS. An arithmetic expression uses arithmetic operators to calculate a numeric value from numeric operands. The possible operators are as follows:

Operator Meaning
+ Adds the operands
- Subtracts the right operand from the left
* Multiplies the operands
/ Divides the left operand by the right

A - in front of an operand multiplies it by -1. The data type of the operands must be numeric and be based on one of the predefined data types INT1, INT2, INT4, INT8, DEC, CURR, QUAN, or FLTP. The following can be specified:
  • Numeric literals without a domain prefix
  • Numeric fields of a data source data_source of the current CDS view
  • Parameter with numeric data type
  • Path expressions that identify a numeric field of a data source data_source
  • Predefined functions that return a numeric type
  • Casting expressions that return a numeric type
  • When a division is performed, the operands of type FLTP or numeric literals with fractional digits and the right operand cannot have the value 0.
The subexpressions of an arithmetic expression can be placed in parentheses (...).

If an expression contains an operand of type DEC, CURR or QUAN, the expression is a decimal expression. In this case, the syntax check checks that the result of each operation is in the value range of the type DEC with length 31 and a maximum of 14 decimal places. If any operands are specified that could produce other values, a syntax error occurs.

Arithmetic expressions can be used as elements of a SELECT list, where they need alternative element names defined using AS.

Note: When a division is performed with two numbers of type DEC, the SQL function DIVISION can be used.

Example

SELECT list of a CDS view with arithmetic expressions.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
         association [1..*] to snwd_so_i as _item 
           on snwd_so.node_key = _item.parent_key 
         { key snwd_so.node_key, 
               gross_amount, 
               gross_amount - tax_amount as pre_tax_amount, 
               cast(gross_amount as abap.fltp) 
                 + (cast( -gross_amount as abap.fltp) * 0.03) 
                   as reduced_amount, 
            cast(gross_amount as abap.fltp) * 0.03 as overall_savings, 
            _item.so_item_pos as item_position, 
            _item.gross_amount as item_gross_amount, 
            cast(_item.gross_amount as abap.fltp) * 0.97 as item_savings 
}

➥ ABAP CDS - case_expr

Syntax Forms

Simple case distinction 

1. ... CASE operand
           WHEN operand1 THEN result1
          [WHEN operand2 THEN result2]
           ...
          [ELSE resultn]
      END ...

Complex case distinction 

2. ... CASE WHEN cond_expr1 THEN result1
          [WHEN cond_expr2 THEN result2]
          [WHEN cond_expr3 THEN result3]
            ...
          [ELSE resultn]
      END ...

Effect

Case distinction in a SELECT statement of a CDS view in ABAP CDS. Either a simple case distinction (simple case) or a complex case distinction (searched case).

Case distinctions can be specified in the SELECT list and in operand positions of other expressions.

◈ ABAP CDS - simple_case_expr

Syntax

... CASE operand
         WHEN operand1 THEN result1
        [WHEN operand2 THEN result2]
         ...
        [ELSE resultn]
    END ...

Effect

Simple case distinction (simple case) in a SELECT statement of a CDS view in ABAP CDS. The case distinction compares the values of the operand operand with the operands operand1, operand2, ... in the order given. The result provided is the operand result after THEN for which the comparison is true for the first time. If no matches are found, the result specified after ELSE is selected. If ELSE is not specified, the result is the zero value.
  • The following can be specified for operand:
    • Fields of a data source data_source of the current CDS view
    • Parameters
    • Session variables
    • Path expressions that identify a field of a data source data_source
    • Predefined functions
    • Arithmetic expressions
  • The following can be specified for operand1, operand2, ... :
    • Literals without a domain prefix
    • Fields of a data source data_source of the current CDS view
    • Path expressions that identify a field of a data source data_source
    • Predefined functions
    • A further case distinction
  • The following can be specified for result1, result1, ...:
    • Literals without a domain prefix
    • Fields of a data source data_source of the current CDS view
    • Path expressions that identify a field of a data source data_source
    • Aggregate expressions
    • Casting expressions
    • Predefined functions
    • Arithmetic expressions
    • A further case distinction
The operand operand must be comparable with operand1, operand2, ... When the CDS view is activated, a result type is determined from the operands result1, result1, ... that covers their types. The operands must be compatible with each other.

Example

Case distinction in a SELECT list.

case partner.bp_role
    when '01' then 'customer'
    when '02' then 'supplier'
end as partner_role

◈ ABAP CDS - searched_case_expr

Syntax

... CASE WHEN cond_expr1 THEN result1
        [WHEN cond_expr2 THENresult2]
        [WHEN cond_expr3 THENresult3]
          ...
        [ELSE resultn]
    END ...

Effect

Complex case distinction (searched case) in a SELECT statement of a CDS view in ABAP CDS. Case distinction evaluates the sequence of conditions cond_expr1, cond_expr2, ... and returns the operand result as the result after THEN, for which the condition is true for the first time. If none of the conditions are true, the result specified after ELSE is selected. If ELSE is not specified, the result is the zero value. Special rules apply when specifying the conditions.

Example

The following CDS view has a complex case distinction in the SELECT list.

@AbapCatalog.sqlViewName: 'DEMO_CDS_SCASE'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_searched_case as
  select from spfli
    { key carrid,
      key connid,
      distance,
      distid,
      case
        when distance >= 2000 then 'long-haul flight'
        when distance >= 1000 and
             distance <  2000 then 'medium-haul flight'
        when distance <  1000 then 'short-haul flight'
                              else 'error'
      end as flight_type }
    where distid = 'MI'

➥ ABAP CDS - cast_expr

Syntax

... CAST( operand AS dtype ) ...

Effect

Cast (type adjustment) in a SELECT statement of a CDS view in ABAP CDS. The cast expression converts the value of the operand operand to the dictionary type specified by dtype. The result has the type dtype. One of the following can be specified for dtype:
  • Any data element
  • A predefined data type in ABAP Dictionary
If a data element is specified for dtype, the predefined data type, the length, and the number of decimal places of the operand and target data type must match exactly (with the exception that the length can differ in the case of type CHAR)

The following table shows the syntax for specifying predefined data types:

dtype Dictionary Type
abap.char( len ) CHAR with length len
abap.clnt[(3)]  CLNT
abap.cuky( len ) CHAR with length len
abap.curr(len,decimals)  CURR with length len and decimals decimal places
abap.dats[(8)] DATS
abap.dec(len,decimals)  DEC with length len and decimals decimal places
abap.fltp[(16,16)]  FLTP
abap.int1[(3)] INT1
abap.int2[(5)] INT2
abap.int4[(10)] INT4
abap.int8[(19)] INT8
abap.lang[(1)] LANG
abap.numc( len ) NUMC with length len
abap.quan(len,decimals)  QUAN with length len with decimals decimal places
abap.raw(len)  RAW
abap.sstring(len)  SSTRING
abap.tims[(6)]  TIMS
abap.unit( len )  CHAR with length len

he actual length of the result is defined when the CDS view is activated and is must be at least as long as an explicitly defined length len. The predefined values can be specified for types with fixed lengths and decimal places, but this is not mandatory.

The following can be specified for operand:
  • A literal without a domain prefix
  • A parameter
  • A session variable
  • A field of a data source data_source of the current CDS view
  • A path expression that identifies a field of a data source data_source
  • A predefined function
  • An arithmetic expression
  • A case distinction with CASE
  • A nested cast expression
Cast expressions can be specified in the SELECT list and in operand positions of expressions.

Notes
  • If a data element is specified for dtype, the result of the expression is given its semantic attributes. An exception to this is the use of the CAST expression within a case distinction using CASE,
  • Casts to data elements are used mainly to modify the semantic attributes of the operand. To avoid unnecessary type conversions on the database, the syntax of this cast is restricted to type-friendly data types. To specify a data element for an incompatible data type regardless, a nested CAST expression can be used to create the data type required for the data element.
  • When performing a conversion between currency fields with type CURR, it should be noted that CAST respects the decimal places defined for the type. In ABAP applications, on the other hand, the position of the decimal point is usually ignored.
  • The characters in the surrogate area of the system code page UTF-16 are handled as two characters in cast expressions for strings. Care should be taken to avoid splitting these characters in cutoff operations.
Example

Cast expressions in a SELECT list.

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
         association [1..*] to snwd_so_i as _item 
           on snwd_so.node_key = _item.parent_key 
         { key snwd_so.node_key, 
               gross_amount as original_amount, 
               cast(gross_amount as abap.fltp) + 
                 (cast( -gross_amount as abap.fltp) * 0.03) 
                   as reduced_amount, 
               cast(gross_amount as abap.fltp) * 0.03 
                 as overall_savings, 
               _item.so_item_pos as item_position, 
               _item.gross_amount as item_gross_amount, 
               cast(_item.gross_amount as abap.fltp) * 0.97 
                 as item_savings }

Example

In the following view, the column char1 of the database DEMO_EXPRESSIONS is cast to the data element demo_char_text.

@AbapCatalog.sqlViewName: 'DEMO_CDS_CAST_DE' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_cast_data_element as 
  select from demo_expressions 
   { cast ( char1 as DEMO_CHAR_TEXT ) as char_with_text };
The following function module call returns the attributes of the view field The text shows that the semantic attributes of the data element were applied. The column char1 does not have its own text.

DATA dfies_tab TYPE TABLE OF dfies. 
CALL FUNCTION 'DDIF_FIELDINFO_GET' 
  EXPORTING 
    tabname   = 'DEMO_CDS_CAST_DE' 
    fieldname = 'CHAR_WITH_TEXT' 
    langu     = sy-langu 
                TABLES 
                dfies_tab = dfies_tab. 
cl_demo_output=>display( dfies_tab[ 1 ]-fieldtext ).

Example

In the following view, a literal is given the semantic attributes of the data element S_MANDT. To do this, the literal must first be converted into the suitable data type CLNT.

@AbapCatalog.sqlViewName: 'DEMO_CDS_CSTCLNT' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_cast_clnt as   
  select from scarr 
   { key cast ( cast( 'XXX' as abap.clnt ) as s_mandt ) 
         as pseudo_client, 
     key carrid, 
         carrname };

1. ABAP CDS - cast_expr, Conversion Rules

The following sections describe the conversion rules for cast expressions in the ABAP CDS DDL.

Numeric Source Type


Source type INT1, INT2, INT4, INT8
  • Numeric target type
The possible target types are INT1, INT2, INT4, INT8, DEC, CURR, QUAN, and FLTP.
    • The source value is converted to the internal representation for the target type.
    • If the value range is exceeded, an exception is raised that can be caught in ABAP using CX_SY_OPEN_SQL_DB.
    • In the case of the target types DEC, CURR, and QUAN, a syntax check warning is produced if a static check identifies that an exception can be raised.
  • Character-like target type
The possible target types are CHAR and CLNT (in the case of INT1).
  • The number value is converted to a character string in mathematical notation without decimal separators and is left-aligned.
  • The length of the target type must be enough for all potential values including the sign. The minimum lengths for INT1, INT2, INT4, and INT8 are 3, 6, 11, and 20.
Source type DEC, CURR, QUAN
    • Numeric target type
The possible target types are DEC, CURR, QUAN, and FLTP.
The source value is converted to the internal representation for the target type.
    • In the case of the target types DEC, CURR, and QUAN an exception is raised if the value range in the integer digits is exceeded. This exception can be caught in ABAP using CX_SY_OPEN_SQL_DB. Any surplus decimal places are cut off without rounding.
    • In the case of the target type FLTP, values that cannot be represented as binary floating point numbers are converted to the next adjacent number.
  • Character-like target type
The potential target type is CHAR.
  • The number value is converted to a character string in mathematical notation and is left-aligned.
  • The length of the target type must be enough for all potential values in the source type including the sign and decimal separator.
Note

When the source type DEC is converted to a character-like target type, the database specifies whether non-relevant zeros are displayed before and after the decimal separator.

Character-Like Source Type


Source type CHAR
  • Character-like target type
The possible target types are CHAR, NUMC, CLNT, LANG, DATS, TIMS, UNIT, and CUKY.
  • The character string is written to the target field as a left-aligned string including leading blanks. If the target type is too short, the string is truncated on the right and a syntax check warning occurs. If the target field is too long, it is padded on the right with blanks.
  • There is no special handling for the special target type for numeric text, NUMC, and for the date/time types DATS and TIMS. It is advisable to only convert valid values. In the case of the target type NUMC, the predefined function LPAD can be used to create leading zeroes.
Source type NUMC
  • Numeric target type
The possible target types are INT1, INT2, INT4, INT8, DEC, CURR, QUAN, and FLTP.
    • The validity of the content of the source field is not checked, which means that there can be decimal places.
    • The source field must contain a character string that can be interpreted as a number. If not, an exception is raised that can be caught in ABAP using CX_SY_OPEN_SQL_DB.
    • In the case of the target types INT1, INT2, INT4, and INT8, the length of the source type cannot be too great for the target type. The source value cannot contain any decimal places.
    • In the case of the target types DEC, CURR, and QUAN an exception is raised if the value range in the integer digits is exceeded. This exception can be caught in ABAP using CX_SY_OPEN_SQL_DB. A syntax check warning occurs it a static check identifies that an exception can be raised. Any surplus decimal places are cut off without rounding.
    • In the case of the target type FLTP, values that cannot be represented as binary floating point numbers are converted to the next adjacent number.
  • Character-like target type
The possible target types are CHAR, NUMC, CLNT, DATS, and TIMS.
    • The validity of the source field is not checked.
    • The character string is written to the target field as a left-aligned string including leading blanks. If the target type is too short, the string is truncated on the right and a syntax check warning occurs. If the target field is too long, it is padded on the right with blanks.
    • The lengths of the source type and target type must match for the special target type for numeric text, NUMC, and for the date/time types DATS and TIMS. No other special handling takes place. It is advisable to only convert valid values. In the case of the target type NUMC, the predefined function LPAD can be used to create leading zeroes.
Source types DATS and TIMS
  • Character-like target type
The potential target type is CHAR.
  • The validity of the source field is not checked.
  • The character string is written to the target field as a left-aligned string including leading blanks. If the target type is too short, the string is truncated on the right and a syntax check warning occurs. If the target field is too long, it is padded on the right with blanks.
➥ ABAP CDS - cond_expr

Syntax

... rel_expr
  | [NOT] cond_expr [AND|OR cond_expr] ...

Effect

Formulates a condition in a CDS view in ABAP CDS whose result is true or false. A condition is either a single relational expression, rel_expr, or an expression constructed from the Boolean operators NOT, AND, and OR in which parenthesized full conditions cond_expr using the parentheses ( ... ) are possible.

The relational expressions rel_expr from which a condition cond_expr can be constructed are:
  • Comparisons with relational operators
  • Interval comparisons using BETWEEN
  • Pattern comparisons using LIKE
  • Checks on the null value using IS NULL
The Boolean operators work as follows:
  • NOT negates the result of the following condition.
  • An AND join is true if both joined conditions are true.
  • An OR join is true if at least one of the joined conditions is true.
The operator NOT is a stronger binding than AND, which itself is a strong binding than OR. This produces implicit parenthesizing, which can be overridden by explicit parenthesizing.

◈ ABAP CDS - cond_expr, Relational Operators

Syntax

... lhs operator rhs ...

Effect

Makes a comparison in a condition in a CDS view in ABAP CDS. The following table shows the possible operators operator of the relational expressions rel_exp:

rel_expr True if
lhs = rhs Value of lhs is equal to the value of rhs
lhs <> rhs Value of lhs is not equal to the value of rhs
lhs < rhs Value of lhs is less than the value of rhs
lhs > rhs Value of lhs is greater than the value of rhs
lhs <= rhs Value of lhs is less than or equal to the value of rhs
lhs >= rhs Value of lhs is greater than or equal to the value of rhs
  • Context-dependent rules apply when specifying the operands lhs and rhs.
  • The tables of comparable types show which data types can be compared with each other.
ABAP CDS - cond_expr, LIKE 


◈ ABAP CDS - cond_expr, BETWEEN

Syntax

... lhs BETWEEN rhs1 AND rhs2 ...

Effect

Compares an interval in a condition in a CDS view in ABAP CDS. The relational expression is true if the value of lhs is between the values of rhs1 and rhs2 The expression has the same meaning as the following join of two comparisons:

... lhs >= rhs1 AND lhs <= rhs2 ...

The corresponding context-dependent rules apply to the operands lhs, rhs1, and rhs2. The same conditions for the data types also apply.

◈ ABAP CDS - cond_expr, LIKE

Syntax

... lhs LIKE rhs [ESCAPE esc]...

Effect

Compares a pattern in a condition in a CDS view in ABAP CDS. The relational expression is true if the string in lhs matches the pattern in rhs. The same rules apply to lhs as to comparisons. Only character-like data types can be used. Only character-like literals without domain prefix are allowed for rhs.

The wildcard characters % for any strings and _ for any character can be used in the pattern in rhs. The optional addition ESCAPE can be used to define an escape character. esc expects a single-character character-like literal without domain prefix. In the pattern in rhs, an escape character may only be placed before a wildcard character or before the escape character itself. In this case, these lose their special meaning.

Notes: 
  • Patterns in rhs closed by the wildcard characters % or _ should not be used to find trailing blanks, since the result is determined by the database platform in question and may be unexpected.
  • Unlike the LIKE condition in Open SQL, lhs LIKE '%' is not true if lhs contains the null value.
◈ ABAP CDS - cond_expr, NULL

Syntax

.... lhs IS [NOT] NULL ...

Effect

Identifies the null value in a CDS view in ABAP CDS. The relational expression is true if the value of lhs is (not) the null value. The same context-dependent rules apply to the operand lhs as to comparisons.

◈ ABAP CDS - cond_expr, Operands

The following rules apply when specifying the operands lhs and rhs in the relational expressions of the SELECT statement in ABAP CDS:
  • General Rules
    • Character literals cannot be used in comparisons with numeric values.
    • Numeric literals that represent a value outside the value range of INT4 must be specified as floating point literals with a decimal point.
  • Rules for use in a WHERE condition
  • Rules for use in a HAVING condition
  • Rules for use in an ON condition of a join expression
  • Rules for use in an ON condition of an association
  • Rules for use in a filter condition of a path expression
  • Rules for use in a complex case distinction.
Note


For comparisons, the tables of comparable types show which data types can be compared with each other.

ABAP CDS - cond_expr, WHERE

Rules for conditions cond_exp in a WHERE condition of a CDS view in ABAP CDS:
  • All relational operators are allowed.
  • lhs expects a field of a data source data_source.
  • A field of a data source data_source, a literal with optional domain prefix, a parameter, or a session variable can be specified for rhs (with the exception of the operator LIKE).
  • A data source field can be specified using a path expression, if this expression is not polyvalent:
    • The cardinality of the contained associations is "to 1".
    • The path expression only contains filter conditions with addition 1:.
  • Other expressions and function calls are not allowed.
ABAP CDS - cond_expr, HAVING

Rules for conditions cond_exp in a HAVING condition of a CDS view in ABAP CDS:
  • The relational operator BETWEEN is not allowed.
  • The Boolean operator NOT is not allowed.
  • A field of a data source data_source or an aggregate expression for this kind of field can be specified for lhs.
  • An element of the SELECT list, an aggregate expression, a literal with an optional domain prefix, or a parameter can be specified for rhs (with the exception of the operator LIKE). If lhs is an aggregate expression, a literal must be specified.
  • A data source field can be specified using a path expression, if this expression is not polyvalent:
    • The cardinality of the contained associations is "to 1".
    • The path expression only contains filter conditions with addition 1:.
  • Other expressions and function calls are not allowed.
ABAP CDS - cond_expr, ON, Join

Rules for conditions cond_exp in an ON condition of a join of a CDS view in ABAP CDS:
  • All relational operators are allowed.
  • lhs expects a field of the data source data_source that represents the left side of the join.
  • A field of both data sources data_source of the join, a literal with optional domain prefix, a parameter, or a session variable can be specified for rhs (with the exception of the operator LIKE).
  • At least one comparison for equality between a field of the left data source and a field of the right data source of the join must be performed.
  • No path expressions or other expressions or function calls can be specified.
ABAP CDS - cond_expr, ON, Association

Rules for conditions cond_exp in an ON condition of an association of a CDS view in ABAP CDS:
  • All relational operators are allowed.
  • A field of one of the two data sources data_source of the association can be specified for lhs.
  • A field of both data sources data_source of the association, a literal with optional domain prefix, a parameter, or a session variable can be specified for rhs (with the exception of the operator LIKE).
  • A field of the source data source cannot be specified using an alternative element name of the current CDS defined using AS.
  • At least one comparison for equality between a field of the initial data source and a field of the target data source of the association must be performed.
  • No path expressions or other expressions or function calls can be specified.
ABAP CDS - cond_expr, Filter

Rules for conditions cond_exp in a filter condition of a path expression of a CDS view in ABAP CDS:
  • All relational operators are allowed.
  • lhs expects a field of the target data source of the current association.
  • A field of the target data source of the current association, a literal with optional domain prefix, a parameter, or a session variable can be specified for rhs (with the exception of the operator LIKE).
  • No path expressions or other expressions or function calls can be specified.
ABAP CDS - cond_expr, CASE

Rules for conditions cond_exp in a complex case distinction of a CDS view in ABAP CDS:
  • All relational operators are allowed.
  • lhs expects a field of a data source data_source.
  • A field of a data source data_source, a literal with optional domain prefix, a parameter, or a session variable can be specified for rhs (with the exception of the operator LIKE).
  • A data source field can be specified using a path expression, if this expression is not polyvalent:
    • The cardinality of the contained associations is "to 1".
    • The path expression only contains filter conditions with addition 1:.
  • Other expressions and function calls are not allowed.

6. ABAP CDS - SELECT, Predefined Functions


The following predefined functions can be specified in the SELECT list and at operand positions of other expressions.

1. SQL Functions
2. Coalesce Function
3. Special Functions

1. ABAP CDS - sql_functions

Syntax

... func( arg1[, arg2] ... ) ...

Effect

Calls predefined SQL functions in a SELECT statement of a CDS view in ABAP CDS. For passed arguments arg1, arg2, ..., a predefined SQL function returns a numeric, character-like, or byte-like result. The possible functions are:
  • Numeric functions
  • String functions
  • Byte string functions
If an argument contains the null value, the result is always the null value. If an argument contains an invalid value or the value range of a result type is exceed, an exception is raised.

➥ ABAP CDS - Numeric Functions

The following table shows the potential numeric SQL functions in a CDS view in ABAP CDS, plus the requirements made on the arguments.. The meaning of the functions can be found under SQL Functions for Numeric Values.

Function Valid Argument Types Result Type
ABS(arg) INT1, INT2, INT4, INT8, DEC, CURR, QUAN, FLTP Data type of arg
CEIL(arg) INT1, INT2, INT4, INT8, DEC, CURR, QUAN, FLTP INT4, INT8 (if arg is of type INT8)
DIV(arg1, arg2) INT1, INT2, INT4, INT8, DEC, CURR, QUAN without decimal places. Data type arg1: here DEC, CURR and QUAN are implemented after INT4.
DIVISION(arg1, arg2, dec) arg1, arg2: INT1, INT2, INT4, INT8, DEC, CURR, QUAN

dec: Integer numeric literal greater than or equal to 0 and not greater than the maximum value of 6 and the length of arg2 plus the number of decimal places of arg1 plus 1
DEC with dec decimal places. The length of the result is the length of arg1 minus the decimal places in arg1 plus the decimal places in arg2 plus dec. This value must not be greater than 31.
FLOOR(arg) INT1, INT2, INT4, INT8, DEC, CURR, QUAN Data type of arg for the integer types, else DEC without decimal places
MOD(arg1, arg2) INT1, INT2, INT4, INT8 Data type of arg1
ROUND(arg, pos) arg: INT1, INT2, INT4, INT8, DEC, CURR, QUAN

pos: Literal, field of a data source or input parameter of type INT1, INT2, INT4
Data type of arg, where INT1 and INT2 are updated to INT4.

The following can be specified as the arguments arg:
  • Literals of a suitable type. The literal can be prefixed with the name of a domain.
  • Suitable fields of a data source data_source of the current CDS view.
  • Path expressions that identify a suitable field of a data source data_source.
  • Input parameters from the parameter list parameter_list.
  • The following predefined functions and expressions (if they return a matching type):
    • Other predefined SQL functions
    • Arithmetic expressions
    • Type modifications using CAST
Note

The SQL functions DIV and MOD behave differently with respect to the signs than the ABAP operators DIV and MOD. In the SQL function DIV, the amounts of the arguments are divided and then the sign is assigned (positive if the arguments have the same signs and negative if they have different signs). Accordingly, the result of MOD can be negative, so that multiplying the result of DIV with expr2 plus the result of MOD produces the value of expr1. The ABAP operator MOD, on the other hand, only produces positive results. See Example.

Example

The following CDS view applies predefined numeric SQL functions in the SELECT list to columns of the database table DEMO_EXPRESSIONS. The program DEMO_CDS_SQL_FUNCTIONS_NUM uses SELECT to access the view.

@AbapCatalog.sqlViewName: 'DEMO_CDS_NUMFUNC' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
  define view demo_cds_sql_functions_num 
   as select from demo_expressions 
   { abs(       num1          ) as r_abs, 
     ceil(      fltp1         ) as r_ceil, 
     floor(     dec1          ) as r_floor, 
     div(       num1, num2    ) as r_div, 
     mod(       num1, num2    ) as r_mod, 
     division(  dec2, dec3, 3 ) as r_division, 
     round(     dec3, 2       ) as r_round }

➥ ABAP CDS - String Functions

The following table shows the potential SQL functions for strings in a CDS view in ABAP CDS, plus the requirements made on the arguments.. The meaning of the functions can be found under SQL Functions for Strings.

Function Valid Argument Types Result Type
CONCAT(arg1, arg2) See below SSTRING if an argument has the type SSTRING, else CHAR with the length of the result.
CONCAT_WITH_SPACE(arg1, arg2, spaces ) arg1, arg2: see below spaces: positive numeric literal greater than 0 and less than or equal to 1331 SSTRING if an argument has the type SSTRING, else CHAR with the length of the result.
INSTR(arg, sub) arg: see below sub: non-empty numeric literal INT4
LEFT(arg, len) arg: see below len: positive numeric literal greater than 0 and less than or equal to 1333 SSTRING if arg has the type SSTRING, else CHAR with length len
LENGTH(arg) See below INT4
LPAD(arg, len, src) arg: see below len: positive numeric literal greater than 0 and less than or equal to 1333 src: character Literal SSTRING if arg has the type SSTRING, else CHAR with length len
LTRIM(arg, char) arg: see below char: Character literal with length 1 SSTRING if arg has the type SSTRING, else CHAR with the length of arg.
REPLACE(arg1, arg2, arg3) See below SSTRING if arg1 or arg3 has the type SSTRING, else CHAR with the maximum possible length of the result.
RIGHT(arg,len) arg: see below len: positive numeric literal greater than 0 and less than or equal to 1333 SSTRING if arg has the type SSTRING, else CHAR with length len
RPAD(arg, len, src) arg: see below len: positive numeric literal greater than 0 and less than or equal to 1333 src: character literal SSTRING if arg has the type SSTRING, else CHAR with length len
RTRIM(arg, char) arg: see below char: Character literal with length 1 SSTRING if arg has the type SSTRING, else CHAR with the length of arg.
SUBSTRING(arg, pos, len) arg: see below pos and len: positive numeric literal not equal to zero SSTRING, if arg has the type SSTRING, else CHAR or NUMC with a length of at least len

The following can be specified as the arguments arg:
  • Literals of a suitable type. The literal can be prefixed with the name of a domain.
  • Suitable fields of a data source data_source of the current CDS view.
  • Path expressions that identify a suitable field of a data source data_source.
  • Input parameters from the parameter list parameter_list.
  • The following predefined functions and expressions (if they return a matching type):
    • Other predefined SQL functions
    • Arithmetic expressions
    • Type modifications using CAST
The valid argument types for arg, arg1, arg2, and arg3 are CHAR, CLNT, LANG, NUMC, CUKY, UNIT, DATS, TIMS, and SSTRING.

In functions where an explicit length len is specified, the actual length of the result is defined when the CDS view is activated and is at least as long as len.

In all functions with the exception of LPAD and RPAD, the trailing blanks of all arguments are removed before the actual processing and the trailing blanks of the result are removed before the return operation. In LPAD and RPAD, the trailing blanks of the argument src are preserved.

Note

The characters in the surrogate area of the system code page UTF-16 are handled as two characters by the CDS string functions. This must be respected when the length is determined and these characters must not be split by mistake.

Example

The following CDS view applies predefined SQL functions for strings in the SELECT list to columns of the database table DEMO_EXPRESSIONS. The program DEMO_CDS_SQL_FUNCTIONS_STRING uses SELECT to access the view.

@AbapCatalog.sqlViewName: 'DEMO_CDS_STRFUNC' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
  define view demo_cds_sql_functions_string 
   as select from demo_expressions 
   { length(            char1               ) as r_length, 
     instr(             char1, 'CD'         ) as r_instr, 
     concat(            char1, char2        ) as r_concat, 
     concat_with_space( char1, char2, 10    ) as r_concat_with_space, 
     left(              char1, 3            ) as r_left, 
     right(             char2, 3            ) as r_right, 
     lpad(              char1, 10, 'x'      ) as r_lpad, 
     rpad(              char2, 10, 'y'      ) as r_rpad, 
     ltrim(             char1, 'A'          ) as r_ltrim, 
     rtrim(             char1, 'E'          ) as r_rtrim, 
     replace(           char2, 'GHI', 'XXX' ) as r_replace, 
     substring(         char2, 2, 3         ) as r_substring }

➥ ABAP CDS - Byte String Functions

The following table shows the potential SQL functions for byte strings in a CDS view in ABAP CDS, plus the requirements made on the arguments.. The meaning of the functions can be found under SQL Functions for Byte Strings.


Function Valid Argument Types Result Type
BINTOHEX(arg) RAW in a maximum length of 255 CHAR with twice the length of arg
HEXTOBIN(arg) CHAR or NUMC in a maximum length of 510 CHAR with half the length of arg

The following can be specified as the arguments arg:
  • Only fields of data sources for BINTOHEX.
  • Only literals and fields of data sources for HEXTOBIN. Literals can contain only an even number of case-sensitive hexadecimal characters "0" to "9" and "A" to "F". Fields of data sources can also contain leading and trailing blanks.
Example

The following CDS view applies predefined SQL functions for byte strings in the SELECT list to columns of the database table DEMO_EXPRESSIONS. The program DEMO_CDS_SQL_FUNCTIONS_BYTE uses SELECT to access the view.

@AbapCatalog.sqlViewName: 'DEMO_CDS_BINFUNC' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
  define view demo_cds_sql_functions_byte 
   as select from demo_expressions 
   { bintohex(  raw1  ) as r_bintohex, 
     hextobin(  char1 ) as r_hextobin }

2. ABAP CDS - coalesce

Syntax

... COALESCE( arg1, arg2 ) ...

Effect

Coalesce function in a SELECT statement of a CDS view of the ABAP CDS. The coalesce function returns the value of the argument arg1 (if this is not the null value); otherwise it returns the value of the argument arg2. The arguments can be literals, fields of a data source, input parameters, predefined functions, or expressions of data types CHAR, SSTR, CLNT, LANG, NUMC, CUKY, UNIT, DATS, TIMS, FLTP, DEC, CURR, QUAN, INT1, INT2, or INT4 or INT8.

The data types of both arguments must either match of the data type of one argument must represent the full value of the other data type. The result has the dictionary type of the argument with the greater value range.

3. ABAP CDS - Special Functions

These functions are delivered by SAP and are not provided as native functions on every database platform

➤ Conversion functions

Syntax

... UNIT_CONVERSION( p1 => a1, p2 => a2, ... )
  | CURRENCY_CONVERSION( p1 => a1, p2 => a2, ... )
  | DECIMAL_SHIFT( p1 => a1, p2 => a2, ... ) ...

Variants:

1. ... UNIT_CONVERSION( p1 => a1, p2 => a2, ... )

2. ... CURRENCY_CONVERSION( p1 => a1, p2 => a2, ... )

3. ... DECIMAL_SHIFT( p1 => a1, p2 => a2, ... )

Effect

Functions for conversions between units and currencies. The functions have keyword parameters p1, p2, ... (some of which are optional), to which the actual parameters a1, a2, ... must be assigned when called using =>.

Variant 1

... UNIT_CONVERSION( p1 => a1, p2 => a2, ... )

Effect

The function UNIT_CONVERSION performs a unit conversion for the value passed to formal parameter quantity. The result has the data type QUAN with the length 31 and 14 decimal places. The unit conversion is performed on the basis of the client-specific rules stored in transaction CUNI and in the database tables T006... of the package SZME.

The table below shows the actual parameters p1, p2, ... and their meaning.

Formal Parameter Optional Meaning Data Type Actual Parameter
quantity - Source value QUAN, DEC, INT1, INT2, INT4, FLTP Field of a data source, parameter
source_unit - Source currency from column MSEHI of database table T006 UNIT Field of a data source, literal, parameter
target_unit - Target unit from column MSEHI of database table T006 UNIT Field of a data source, literal, parameter
client x Client whose rules are used to perform the unit conversion, default value: Content of sy-mandt CLNT Field of a data source, literal, parameter
error_handling x Error handling. If "FAIL_ON_ERROR" (default value), an error raises an exception; if "SET_TO_NULL", the result is reset to the null value; if "KEEP_UNCONVERTED", the source value is not changed. CHAR with length 20 Literal

Note

The precision of the result of the unit conversion depends on the database platform. The highest precision is achieved on databases that support decimal floating point numbers. Due to rounding, the result can be different from a unit conversion performed using ABAP methods, such as a standard function module.

Example

The following CDS view calls a unit conversion in the SELECT list for column DEC3 of database table DEMO_EXPRESSIONS. The source unit is a literal that is cast to the required type. The target unit must be passed as a parameter. In the event of an error, for example if a conversion between the entered units is impossible, the result is reset to zero.

@AbapCatalog.sqlViewName: 'DEMO_CDS_UNTCNV' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
  define view demo_cds_unit_conversion 
   with parameters to_unit:abap.unit(3) 
   as select from demo_expressions 
   { id, 
     dec3 as original_value, 
     cast( 'MI' as abap.unit(3) ) as original_unit, 
     unit_conversion( quantity => dec3, 
                      source_unit => cast( 'MI' as abap.unit(3) ), 
                      target_unit => :to_unit, 
                      error_handling => 'SET_TO_NULL' ) as converted_value, 
     :to_unit as converted_unit }

The program DEMO_CDS_UNIT_CONVERSION accesses the view in a SELECT statement. Here, the target unit is passed to the parameter in question. As a comparison, the same conversion is also performed using the function module UNIT_CONVERSION_SIMPLE. As prerequisite for the example, the units and their conversion rules must be available in the corresponding database tables. 

Variant 2

... CURRENCY_CONVERSION( p1 => a1, p2 => a2, ... ) 

Effect

The function CURRENCY_CONVERSION performs a currency conversion for the value passed to formal parameter amount. The result has the data type CURR with the same technical attributes as the actual parameter passed to amount. The currency conversion is performed on the basis of the client-specific rules stored in the database tables TCUR... of package SFIB. These rules can be edited using transaction OB08.

Formal Parameter Optional Meaning Data Type Actual Parameter
amount Source value CURR Field of a data source, parameter
source_currency  Source currency from column WAERS of database table TCURC CUKY Field of a data source, literal, parameter
target_currency  Target currency from column WAERS of database table TCURC CUKY Field of a data source, literal, parameter
exchange_rate_date - Exchange rate date from column WAERS of database table TCURR DATS Literal, parameter
exchange_rate_type  x Exchange rate type from column KURST of the database table TCURR, default value: "M" CHAR with length 4 Literal, parameter
client Client whose rules are used to perform the currency conversion, default value: Content of sy-mandt CLNT Field of a data source, literal, parameter
round If "X" (default value), the intermediate result of the conversion is rounded to the end result using commercial rounding; otherwise, it is truncated CHAR Literal
decimal_shift If "X" (default value), the decimal places of the source value are moved as specified by the decimal places of the source currency. CHAR Literal
decimal_shift_back x If "X" (default value), the decimal places of the result are moved as specified by the decimal places of the target currency. CHAR Literal
error_handling x Error handling. If "FAIL_ON_ERROR" (default value), an error raises an exception; if "SET_TO_NULL", the result is reset to the null value; if "KEEP_UNCONVERTED", the source value is not changed.  CHAR with length 20 Literal

The literals #cdsboolean.TRUE, #cdsboolean.true, #cdsboolean.FALSE, and #cdsboolean.false can also be specified for the input parameters round, decimal_shift, and decimal_shift_back with the domain prefix CDSBOOLEAN (case-sensitive) or the literals "true" or "false" (not case-sensitive). Internally, these literals are handled like the values "X" or " ".

Handling the Decimal Places
  • The value passed is rounded to two decimal places before it is converted.
  • Before the conversion, the value passed is multiplied by 10 to the power of the number of decimal places of the source currency.
  • If the value "X" or "TRUE" is passed to the parameter decimal_shift, the value passed is multiplied by 10 to the power of two minus the number of decimal places of the source currency before it is converted.
  • If the value "X" or "TRUE" is passed to the parameter decimal_shift_back, the result is divided by 10 to the power of two minus the number of decimal places of the target currency before it is converted.
  • After the conversion, the result is divided by 10 to the power of the number of decimal places of the target currency.
Note
  • The conversion is performed on the database, which means that part of the calculation takes place using different rounding rules from ABAP. However the conversion is made, the same results cannot be expected as when using standard function modules for currency conversion, since these modules are generally less precise and round the intermediate results accordingly.
  • The parameter decimal_shift is intended to set the source value to the number of decimal places of the source currency before the conversion. This assumes that its technical type, CURR, has two decimal places as usual. The parameter decimal_shift_back is intended to perform the reverse operation.
  • If the technical type CURR of the source value does not have two decimal places, the function CURRENCY_CONVERSION may display unexpected behavior.
Example

The following CDS view calls a currency conversion in the SELECT list for column AMOUNT of database table DEMO_PRICES. The target currency must be passed as a parameter. In the event of an error, for example when a currency does not exist, the result is reset to zero.

@AbapCatalog.sqlViewName: 'DEMO_CDS_CURRCO' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
  define view demo_cds_curr_conv 
   with parameters to_currency:abap.cuky(5), 
                   exc_date:abap.dats 
   as select from demo_prices 
   { id, 
     currency_conversion( amount => amount, 
                          source_currency => currency, 
                          round =>  'X', 
                          target_currency => :to_currency, 
                          exchange_rate_date => :exc_date, 
                          error_handling => 'SET_TO_NULL' ) as amount, 
     :to_currency as currency }

Program DEMO_CDS_CURRENCY_CONVERSION accesses the view in a SELECT statement, in which the target currency is passed. As a comparison, the same conversion is also performed using the function module CONVERT_TO_LOCAL_CURRENCY. As prerequisite for the example, the currencies and conversion rules must be available in the corresponding database tables. 

Variant 3

... DECIMAL_SHIFT( p1 => a1, p2 => a2, ... ) 

Effect

The function DECIMAL_SHIFT sets the decimal separator of the value that is passed to formal parameter amount in accordance with a currency. The result has the data type CURR with the length 31 and 14 decimal places. Its value is produced by multiplying the input parameter rounded to two decimal places by 10 the power of two minus the decimal places defined by the currency passed.

Possible currencies and their decimal places are based on the database tables TCUR... of package SFIB.

Formal ParameterOptionalMeaningData TypeActual Parameter
amountSource valueCURRField of a data source, parameter
currency Currency from column WAERS of database table TCURC; the associated decimal places determined from the column CURRDEC of TCURXCUKYField of a data source, literal, parameter
error_handlingError handling. If "FAIL_ON_ERROR" (default value), an error raises an exception; if "SET_TO_NULL", the result is reset to the null value; if "KEEP_UNCONVERTED", the source value is not changed.CHAR with length 20Literal

Note

If the type of the actual parameter passed to amount has two decimal places, its value is set to the number of decimal places of the passed currency.

Example

In the SELECT list, the following CDS view sets the decimal separator for the column AMOUNT of the database table DEMO_PRICES, in accordance with currencies with decimal places between 0 and 5. The column AMOUNT has two decimal places, which means that the decimal places are determined directly by the currencies passed. In the event of an error, for example when a currency does not exist, the result is reset to zero.

@AbapCatalog.sqlViewName: 'DEMO_CDS_DCSHFT' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
  define view demo_cds_decimal_shift 
   as select from demo_prices 
   { id, 
     @Semantics.amount.currencyCode:'currency' 
     amount as original, 
     @Semantics.currencyCode 
     currency, 
     decimal_shift( amount => amount, 
                    currency => cast( '0 ' as abap.cuky(5) ), 
                    error_handling => 'SET_TO_NULL' ) as shift_0, 
     decimal_shift( amount => amount, 
                    currency => cast( '1 ' as abap.cuky(5) ), 
                    error_handling => 'SET_TO_NULL' ) as shift_1, 
     decimal_shift( amount => amount, 
                    currency => cast( '2 ' as abap.cuky(5) ), 
                    error_handling => 'SET_TO_NULL' ) as shift_2, 
     decimal_shift( amount => amount, 
                    currency => cast( '3 ' as abap.cuky(5) ), 
                    error_handling => 'SET_TO_NULL' ) as shift_3, 
     decimal_shift( amount => amount, 
                    currency => cast( '4 ' as abap.cuky(5) ), 
                    error_handling => 'SET_TO_NULL' ) as shift_4, 
     decimal_shift( amount => amount, 
                    currency => cast( '5 ' as abap.cuky(5) ), 
                    error_handling => 'SET_TO_NULL' ) as shift_5 }

The program DEMO_CDS_DECIMAL_SHIFT accesses the view in a SELECT statement. As prerequisite for the example, the currencies and their number of decimal places must be available in the corresponding database tables.

➤ Date functions and time functions

◉ Date Functions

Syntax

... DATS_IS_VALID(date)
  | DATS_DAYS_BETWEEN(date1,date2)
  | DATS_ADD_DAYS(date,days,on_error)
  | DATS_ADD_MONTHS(date,months,on_error) ...

Variants:

1. ... DATS_IS_VALID(date)

2. ... DATS_DAYS_BETWEEN(date1,date2)

3. ... DATS_ADD_DAYS(date,days,on_error)

4. ... DATS_ADD_MONTHS(date,months,on_error)

Effect

These functions perform operations with arguments of the predefined data type DATS. The functions have positional parameters to which actual parameters need to be assigned when called. There are currently no optional parameters. Suitable fields of a data source, literals, parameters, path expressions, predefined functions, or expressions can all be specified as actual parameters. Only literals can be passed to the parameter on_error. If an actual parameter contains the null value, every function except DATS_IS_VALID returns a null value.

Note

It is not currently possible to access the current system date directly in a CDS view. Instead, a CDS view can be given an appropriate input parameter. The special annotation @Environment.systemField makes it possible to pass the value of the ABAP system field sy-datum to this parameter.

Variant 1


... DATS_IS_VALID(date)


Effect

The function DATS_IS_VALID determines whether date (if specified) contains a valid date in the format YYYYMMDD. The actual parameter must have the predefined data type DATS. The result has the data type INT4. A valid date produces the value 1 and all other input values (including the null value) produce the value 0.

Note

The value "00010101" is a valid date but the value "00000000" is not.

Variant 2


... DATS_DAYS_BETWEEN(date1,date2)


Effect

The function DATS_DAYS_BETWEEN calculates the difference between two specified dates, date1 and date2, in days. The actual parameters must have the predefined data type DATS and should contain a valid date in the format YYYYMMDD. Any invalid dates specified are initialized or set to the value "00010101" before the calculation. The result has the data type INT4. If date2 is greater than date1, the result is positive. In the reverse case, it is negative.

Note

Before the difference is calculated, the specified dates are converted to integers, like in ABAP, and the corresponding rules apply.

Variant 3


... DATS_ADD_DAYS(date,days,on_error)


Effect

The function DATS_ADD_DAYS adds days days to a specified date date.
  • The actual parameter date must have the predefined data type DATS and should contain a valid date in the format YYYYMMDD. Any invalid date specified is initialized or set to the value "00010101" before the calculation.
  • The actual parameter days must have the predefined data type INT4.
  • The actual parameter on_error must have the predefined data type CHAR with the length 10 and must have one of the following values:
    • "FAIL" (an error raises an exception)
    • "NULL" (an error returns the null value)
    • "INITIAL" (an error returns the initial value)
    • "UNCHANGED" (an error returns the unmodified value of date)
The format is not case-sensitive. Any incorrectly specified values raise an exception.

The result has the data type DATS. If days is positive, the number of days is added to date. In other cases, the number of days is subtracted. If the calculation produces an invalid date, the error is handled as specified in on_error.

Note

For the calculation, the specified date is converted to an integer, like in ABAP, and the result is converted to a date again while applying the corresponding rules.

Variant 4


... DATS_ADD_MONTHS(date,months,on_error)


Effect

The function DATS_ADD_MONTHS adds months months to a specified date date.
  • The actual parameter date must have the predefined data type DATS and should contain a valid date in the format YYYYMMDD. Any invalid date specified is initialized or set to the value "00010101" before the calculation.
  • The actual parameter months must have the predefined data type INT4.
  • The same applies to the actual parameter on_error as to DATS_ADD_DAYS.
The result has the data type DATS. If months is positive, the number of months is added to date. In other cases, the number of months is subtracted.

An attempt is made to create a date with the same day in an earlier or later month. If the maximum day possible in a month is exceeded, the greatest possible day is used. If the calculation produces an otherwise invalid date, the error is handled as specified in on_error.

Example

The following CDS view applies date functions in the SELECT list to columns of the database table DEMO_EXPRESSIONS. The program DEMO_CDS_DATE_FUNCTIONS uses SELECT to access the view. The columns DATS1 and DATS2 in the database table and the actual parameters for the input parameters of the view can be given any values. In the case of DATS_ADD_DAYS and DATS_ADD_MONTHS. invalid values or values that produce invalid results are handled as specified in the view.

@AbapCatalog.sqlViewName: 'demo_cds_datfnc'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_date_functions
  with parameters p_days:abap.int4,
                  p_months:abap.int4
  as select from demo_expressions {
    id,
    dats1 as date1,
    dats_is_valid(dats1) as valid1,
    dats2 as date2,
    dats_is_valid(dats2) as valid2,
    dats_days_between(dats1,dats2) as difference,
    dats_add_days(dats1,:p_days,'INITIAL')  as day1,
    dats_add_months(dats2,:p_months,'FAIL') as day2 }


◉ Time Functions

Syntax

... TIMS_IS_VALID(time) ...

Variants:

1. ... TIMS_IS_VALID(time) ...

Effect

These functions perform operations with arguments of the predefined data type TIMS. The functions have positional parameters to which actual parameters need to be assigned when called. There are currently no optional parameters. Suitable fields of a data source, literals, parameters, path expressions, predefined functions, or expressions can all be specified as actual parameters. If an actual parameter contains the null value, every function except TIMS_IS_VALID returns a null value.

Note

It is not currently possible to access the current system time directly in a CDS view. Instead, a CDS view can be given an appropriate input parameter. The special annotation @Environment.systemField makes it possible to pass the value of the ABAP system field sy-uzeit to this parameter.

Variant 1


... TIMS_IS_VALID(time) ...


Effect

The function TIMS_IS_VALID determines whether time (if specified) contains a valid time in the format HHMMSS. The actual parameter must have the predefined data type TIMS. The result has the data type INT4. A valid date produces the value 1 and all other input values (including the null value) produce the value 0.

Example

The following CDS view applies time functions in the SELECT list to columns of the database table DEMO_EXPRESSIONS. The program DEMO_CDS_TIME_FUNCTIONS uses SELECT to access the view. The column TIMS1 of the database table can be given any values.

@AbapCatalog.sqlViewName: 'demo_cds_timfnc'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_time_functions
   as select from demo_expressions {
    id,
    tims1 as time1,
    tims_is_valid(tims1) as valid1 }

◉ Time Stamp Functions

Syntax

... TSTMP_IS_VALID(tstmp)
  | TSTMP_CURRENT_UTCTIMESTAMP()
  | TSTMP_SECONDS_BETWEEN(tstmp1,tstmp2,on_error)
  | TSTMP_ADD_SECONDS(tstmp,seconds,on_error) ...

Variants:

1. ... TSTMP_IS_VALID(tstmp)

2. ... TSTMP_CURRENT_UTCTIMESTAMP()

3. ... TSTMP_SECONDS_BETWEEN(tstmp1,tstmp2,on_error)

4. ... TSTMP_ADD_SECONDS(tstmp,seconds,on_error)

Effect

These functions perform operations with arguments of the predefined data type DEC with length 15 or of the data element TIMESTAMP. The content of an argument of this type is interpreted as an ABAP-specific time stamp.

With the exception of TSTMP_CURRENT_UTCTIMESTAMP, these functions have positional parameters to which actual parameters must be assigned when called. There are currently no optional parameters. Suitable fields of a data source, literals, parameters, path expressions, predefined functions, or expressions can all be specified as actual parameters. Only literals can be passed to the parameter on_error. If an actual parameter contains the null value, every function except TSTMP_IS_VALID returns a null value.

Notes

The time stamp functions enable time stamps to be edited on the database that are stored in the ABAP-specific format in database tables. As in ABAP, other types of access to these fields interpret these time stamps as regular numeric values.
There is currently no function that can transform a time stamp into date and time fields.

Variant 1


... TSTMP_IS_VALID(tstmp)

Effect

The function TSTMP_IS_VALID determines whether tstmp (if specified) contains a valid time stamp in the format YYYYMMDDHHMMSS. The actual parameter must have the predefined data type DEC with length 15 and no decimal places. The result has the data type INT4. A valid time stamp produces the value 1 and all other input values (including the null value) produce the value 0.

Variant 2


... TSTMP_CURRENT_UTCTIMESTAMP()

Effect

The function TSTMP_CURRENT_UTCTIMESTAMP returns a UTC time stamp in accordance with the POSIX standard. The result has the data type DEC with length 15 and no decimal places.

Example

The date and time of the current UTC time stamp is extracted using the SQL function substring in the following CDS view. This requires a conversion to a character-like type using CAST.

@AbapCatalog.sqlViewName: 'demo_cds_datim'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_date_time
  as select from demo_expressions {
    substring( cast( tstmp_current_utctimestamp()
      as abap.char(17) ), 1, 8 ) as dat,
    substring( cast( tstmp_current_utctimestamp()
      as abap.char(17) ), 9, 6 ) as tim }

Variant 3


... TSTMP_SECONDS_BETWEEN(tstmp1,tstmp2,on_error)


Effect

The function TSTMP_SECONDS_BETWEEN calculates the difference between two specified time stamps, tstmp1 and tstmp2 in seconds. The actual parameter must have the predefined data type DEC with length 15 and no decimal places and contain valid time stamps in the format YYYYMMDDHHMMSS. Any invalid time stamps produce an error. If tstmp2 is greater than tstmp1, the result is positive. In the reverse case, it is negative.

The actual parameter on_error controls error handling. It must have the predefined data type CHAR with the length 10 and must have one of the following values:

"FAIL" (an error raises an exception)
"NULL" (an error returns the null value)
"INITIAL" (an error returns the initial value)
The format is not case-sensitive. Any incorrectly specified values raise an exception.

Variant 4


... TSTMP_ADD_SECONDS(tstmp,seconds,on_error)

Effect

The function TSTMP_ADD_SECONDS adds seconds seconds to a time stamp tstmp. The actual parameter tstmp must have the predefined data type DEC with length 15 and no decimal places and contain a valid time stamp in the format YYYYMMDDHHMMSS. An invalid time stamp produces an error. The actual parameter seconds must also have the predefined data type DEC with length 15 and no decimal places. Any negative values are subtracted. If the result is invalid, an error occurs.

The actual parameter on_error controls error handling. The same applies as to the function TSTMP_SECONDS_BETWEEN. The additional value "UNCHANGED" can be used to specify that an error caused the unchanged value of tstmp to be returned.

Example

The following CDS view applies time stamp functions in the SELECT list to columns of the database table DEMO_EXPRESSIONS. The program DEMO_CDS_TIMESTAMP_FUNCTIONS uses SELECT to access the view. The column NUM1 of the database table is given a value that is added to a time stamp in the column TIMESTAMP1 as seconds. The difference is found between this sum and a time stamp retrieved on the database by the function TSTMP_CURRENT_UTCTIMESTAMP. A delay, wait, can be integrated between the time stamp in the ABAP program and the time stamp created on the database.

@AbapCatalog.sqlViewName: 'demo_cds_tsfnc'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_timestamp_functions
  as select from demo_expressions {
    id,
    timestamp1 as timestamp1,
    tstmp_is_valid(timestamp1) as valid1,
    tstmp_seconds_between(
      tstmp_current_utctimestamp(),      
      tstmp_add_seconds(
        timestamp1,
        cast( num1 as abap.dec(15,0) ),
        'FAIL'),
     'FAIL') as difference }

➥ ABAP CDS - EXTEND VIEW

Syntax

@AbapCatalog.sqlViewAppendName: 'CDS_APPEND_VIEW'
[@extension_annot1]
[@extension_annot2]
...
EXTEND VIEW cds_entity WITH cds_view_extension
            [association1 association2 ...]
            { select_list_extension } [;]

Effect

Extends an existing CDS view cds_entity using a CDS view extension cds_view_extension in CDS source code. The extension adds the following to the SELECT list of the available view without making changes:
  • The elements of the specified extension list select_list_extension as view fields
  • Optional associations association1, association2, ... for the SELECT statement of the extended CDS view
The annotation AbapCatalog.sqlViewAppendName must be specified before the view extension itself is defined using EXTEND VIEW. Further annotations extension_annot1, extension_annot2, ... can also be specified. This is optional.

Two ABAP Dictionary objects are created for a CDS view extension that is defined using EXTEND VIEW. A name must be specified for each of the two objects:
  • The actual name cds_view_extension of the CDS view extension is specified after the keywords EXTEND VIEW. This name follows the same rules as the name of an append view, but can have up to 30 characters.
  • The name CDS_APPEND_VIEW for a classic append view must be specified in quotation marks after the annotation @AbapCatalog.sqlViewAppendName created when the CDS view extension was activated. This name is subject to the same rules as the name of a classic view in ABAP Dictionary. The new append view extends the CDS database view of the extended CDS view. The name given to the append view can no longer be changed after the CDS view is transported into a follow-on system.
The extended CDS view must be specified under the name of its CDS entity. The name of the CDS database view cannot be specified here. It is possible to access all fields of the data sources used in the extended CDS view in the extension list select_list_extension. The list can have all elements of a SELECT list, except aggregate expressions. You can also specify the following:
  • Input parameters of the extended CDS view
  • Path expressions for various associations and for associations of the extended CDS view
  • Other literals, expressions and functions
If an appended element already occurs in the existing SELECT list or if a different extension occurs, it must be given an alternative element name using AS. An appended field cannot be defined as a key field using KEY.

The following CDS views cannot currently be extended:
  • Views with an explicit name list
  • Views with aggregate expressions and a GROUP-BY clause
  • Views with a UNION clause for union sets
CDS view extensions themselves cannot be extended.

Notes
  • Every CDS view extension has its own CDS source code. The CDS source code in a CDS view extension is edited in a different editor from the CDS source code of a CDS view. The ADT documentation describes how these types of source code are created.
  • An existing CDS view can be extended using multiple CDS view extensions.
  • The name of the new append view and of the actual CDS view extension should be located in the customer namespace (or in the namespace of a partner or special development) to protect it against being overwritten by upgrades or new releases.
  • Currently it is not possible to define extension categories for CDS views. The following restrictions apply for this reason:
    • CDS views have the property can be extended in any way with respect to the extension category of structures. The consequences of this must be respected when extending a CDS view.
    • Functional extensions, such as adding aggregate functions and the associated changes to the GROUP-BY clause or extensions to join expressions or other clauses, are not supported.
    • The attributes of a CDS view defined using annotations, such as switching on SAP buffering, cannot currently be modified using extensions.
  • It is advisable to contact SAP before extending CDS views from basis packages.
  • The CDS source code of a CDS view extension does not need to have the same name as the CDS view extension entity, but it is advisable to use the name of the entity.
  • After a piece of CDS source code is transported, the combination of its name and the name of the CDS view extension defined in it and its APPEND view is defined and can no longer be modified by being renamed.
Example

The following CDS view extension:

@AbapCatalog.sqlViewAppendName: 'DEMO_CDS_EXTENS'
extend view demo_cds_original_view with demo_cds_view_extension
  { spfli.distance,
    spfli.distid as unit };
adds two view fields to the existing CDS view:

@AbapCatalog.sqlViewName: 'DEMO_CDS_ORIG'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_original_view
  as select from spfli
            join scarr on scarr.carrid = spfli.carrid
     { key scarr.carrname as carrier,
       key spfli.connid   as flight,
       spfli.cityfrom     as departure,
       spfli.cityto       as destination };

The classic append view DEMO_CDS_EXTENS is created in ABAP Dictionary. The program DEMO_CDS_VIEW_EXTENSION uses the statement SELECT to access the extended view and also displays the components of the dictionary structures in question.

1. ABAP CDS - EXTEND VIEW, extension_annot

Syntax

... @annotation ...

Effect

Specifies an annotation annotation as an extension annotation in the definition of a CDS view in ABAP CDS before the statement EXTEND VIEW. The name annotation of the annotation must be prefixed directly with the character @.

The following tables show the possible ABAP annotations that can be specified, and their meanings. The ABAP annotations are evaluated by the ABAP runtime environment for every CDS entity. Annotations with other identifiers are usually SAP annotations. These are not evalutated by an ABAP run time environment but by other SAP frameworks.

AbapCatalog annotations


annotation Meaning Possible Values Default Value
AbapCatalog.sqlViewAppendName Name of the append view of the CDS view extension Character string, maximum 16 characters, which consists of letters, numbers and underscores and starts with a namespace prefix. -

Notes

1. Each definition of a CDS view must contain the ABAP annotation AbapCatalog.sqlViewAppendName that defines the name of the append view in ABAP Dictionary.
2. The name given to the append view can no longer be changed after the CDS-view is transported into a follow-on system.

➥ ABAP CDS - DEFINE TABLE FUNCTION

Syntax

[@function_annot1]
[@function_annot2]
...
[DEFINE] TABLE FUNCTION cds_entity
         [parameter_list]
         element_list
         IMPLEMENTED BY METHOD amdp_function [;]

Effect

Defines a CDS table function in CDS source code. The implementation of the CDS table function Native SQL is performed in an AMDP method amdp_function, which is created using the AMDP framework in the database system as an AMDP function.
  • @function_annot is used to specify optional annotations for the CDS table function.
  • parameter_list is used to declare a list of optional input parameters for the CDS table function. These also define the input parameters of the AMDP function.
  • element_list is used to declare the elements of the CDS table function. These also define the columns of the tabular return value of the AMDP function.
  • When amdp_function is used, an AMDP function implementation must be specified in the form amdp_class=>amdp_method. Here amdp_class is an AMDP class and amdp_method is an AMDP function implementation contained in this class. When this function implementation is declared, the current CDS table function is specified after the addition FOR TABLE FUNCTION. The AMDP function implementation must not exist when the CDS table function is created and activated. . The name of an AMDP function implementation amdp_function can only be specified in a single CDS table function.
A CDS table function returns a tabular result set. This can be used (like every CDS entity) as a data source in other CDS entities or in Open SQL read statements. The prerequisite for use is that the specified AMDP function implementation exists and is active.

Notes
  • Table functions constitute a database extension that is not supported by all database systems. However, the ABAP CDS DDL allows table functions to be created and accessed independently of the database system. If SELECT is used to access a table function or a view that contains table functions as data sources but the current database system does not support them, a non-handleable exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised.
  • In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system supports table functions. This requires the constant AMDP_TABLE_FUNCTION of the class to be passed to the method in an internal table.
  • The CDS source code of a CDS table function does not need to have the same name as the CDS table function, but it is advisable to use the name of the CDS table function.
  • After a piece of CDS source code is transported, the combination of its name and the name of the CDS table function view is defined and can no longer be modified by being renamed.
Example

The following DDL source code shows a client-specific CDS table function. It contains an input parameter (with the annotation @Environment.systemField and the predefined value #CLIENT) for the client, which is implicitly supplied with the ID of the current client when used as a data source of the Open SQL statement SELECT. For more information about how to use this function, see the example for AMDP Functions.

@ClientDependent: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function DEMO_CDS_GET_SCARR_SPFLI_INPCL
  with parameters @Environment.systemField: #CLIENT
                  clnt:abap.clnt,
                  carrid:s_carr_id
  returns { client:s_mandt;
            carrname:s_carrname;
            connid:s_conn_id;
            cityfrom:s_from_cit;
            cityto:s_to_city; }
  implemented by method
    CL_DEMO_AMDP_FUNCTIONS_INPCL=>GET_SCARR_SPFLI_FOR_CDS;

1. ABAP CDS - DEFINE TABLE FUNCTION, function_annot

Syntax

... @annotation ...

Effect

Specifies an annotation annotation as a function annotation in the definition of a CDS table function in ABAP CDS in front of the statement DEFINE TABLE FUNCTION. The name annotation of the annotation must be prefixed directly with the character @.

The following tables show the possible ABAP annotations that can be specified, and their meanings. The ABAP annotations are evaluated by the ABAP runtime environment for every CDS entity. Annotations with other identifiers are usually SAP annotations. These are not evalutated by an ABAP run time environment but by other SAP frameworks.
  • AccessControl Annotations
  • ClientDependent Annotations
  • DataAging Annotations
  • EndUserText Annotations
The first column displays the name annotation of an ABAP annotation and the second column displays its meaning. The third column shows the possible values value. The fourth column displays the value, which has been set implicitly for value, if the annotation is not used explicitly. If nothing is specified for value, the annotation should be specified without a value.

AccessControl Annotations

annotation Meaning Possible Values Default Value
AccessControl.authorizationCheck Defines implicit access control when Open SQL is used to access the CDS table function #CHECK:
If Open SQL is used to access the table function, access control is applied implicitly if a CDS role is assigned to the table function. If there is no role for the table function, a syntax check warning occurs.
#NOT_REQUIRED:
Like #CHECK, but there is no syntax check warning.
#NOT_ALLOWED:
No access control is performed. This produces a syntax check warning in the DDL source code of a role for the table function.
#CHECK

ClientDependent Annotations

annotation Meaning Possible Values Default Value
ClientDependent Defines client handling when Open SQL is used to access the CDS table function. true:
The CDS table function is client-specific. When accessed using SELECT, automatic client handling is performed.

false:
The CDS table function is a cross-client table function. No automatic client handling is performed.
true

Note

If a CDS table function is configured to be cross-client with the ABAP annotation ClientDependent false, all CDS views that use this table function as a data source are also cross-client.

DataAging Annotations

annotation Meaning Possible Values Default Value
DataAging.noAgingRestriction Defines the handling of obsolete data on a SAP HANA database when Open SQL is used to access the CDS table function true:
Open SQL reads all data

false:
Open SQL reads current data only
false

EndUserText Annotations


annotation Meaning Possible Values Default Value
EndUserText.label Translatable short text of the CDS table function Character string with maximum 60 characters -

Note

ABAP annotations introduced using EndUserText are used to define translatable semantic texts for a CDS object. The value of an annotation like this is saved in special tables that have a language key and that are translatable. The value specified in the source code should consist of text in the original language of the CDS source code and is translated into the required languages. The methods of the class CL_DD_DDL_ANNOTATION_SERVICE read these texts in accordance with an input parameter for the language. There are also special methods GET_LABEL_... that are designed for these texts only.

2. ABAP CDS - DEFINE TABLE FUNCTION, parameter_list
3. ABAP CDS - DEFINE TABLE FUNCTION, element_list
     3.1 Folder ABAP CDS - DEFINE TABLE FUNCTION, element
           3.1.1 ABAP CDS - DEFINE TABLE FUNCTION, element_annot

➥ ABAP CDS - parameter
➥ ABAP CDS - typing
➥ ABAP CDS - element_annot

➤ ABAP CDS - Cyclical Dependencies

No comments:

Post a Comment