Optional Parameters in CDS Views

[former_member1321]

Hi All,

I am a newbie to SAP-HANA world and was recently exploring different types of CDS Views (particularly CDS Views with Input Parameters).

Though CDS Views with Input Parameters were introduced quite a long time back but I could not find a way to make some of the input parameters as Optional. So I played around with some annotations and found out a way to accomplish the same.

I did that with the help of annotation

@Environment.systemField: #USER

I used the above annotation in my CDS View along with parameters in the following way:

  @AbapCatalog.sqlViewName: 'Z_CDS_OP'

  @AbapCatalog.compiler.compareFilter: true

  @AccessControl.authorizationCheck: #CHECK

  @EndUserText.label: 'Material Query'

  define view ZCDS_OP

    with parameters

      @Environment.systemField: #CLIENT

      p1 : mandt,

      @Environment.systemField: #USER

      p2 : uname,

      p3 : aenam  

    as select from mara as ar

  {

    :p1    as Client,

    :p2    as Name,

    aenam  as Created_By,

    matnr  as Material

  }

  where

        ar.mandt = :p1

    and ar.aenam = :p3; 

Here the parameter p2 is defined for the sy-uname value, but it functions as an optional parameter at run-time.

If no value is passed for p2 parameter, the system defaults it to the sy-uname value.



Note that there is no asterisk (*) over p2 field. Below is the output of this query:



In this case my username is 'FUNCTIONAL' and is displayed in column 'Name'.

 

Now I pass the value to the optional parameter:



Now, the p2 parameter value gets used and I get the following result:



 

This CDS view can also be consumed in an ABAP Program like the following:

    "Select Data from CDS view without optional parameter

    SELECT Client, Name, Created_By, Material

    FROM zcds_op( p3 = 'FUNCTIONAL' )

    INTO TABLE @DATA(li_mara).

and

    "Select Data from CDS view with optional parameter

    SELECT Client, Name, Created_By, Material

    FROM zcds_op( p2 = 'DEEPAK', p3 = 'FUNCTIONAL' )

    INTO TABLE @DATA(li_mara).

Both the code snippets work perfectly fine and generate the desired results.

So in this way I made a parameter optional in a CDS View.