Calculated date in WHERE condition of CDS view

Question

I'm trying to get a list of valid system status for the notification object, in order to not check all the notifications in the table, I want to execute the selection by checking only the last 2 years of data.

Maybe there is a better solution to my problem, but I'm still curious about this technical limitation. To my knowledge, the system status in SAP are kind of hardcoded and can't be determined per object via any table (SAP could add new system status any moment).

I tried to create the below CDS view, but the function dats_add_months can't be used in the where condition, is there a solution to that? Notice that 7.50 doesn't have session parameter for system date so I use an environment variable:

define view ZNOTIF_SYS_STATUS 
    with parameters sydat : abap.dats @<Environment.systemField: #SYSTEM_DATE
    as select distinct from qmel as notif
    inner join jest as notif_status on notif_status.objnr = notif.objnr
                                   and notif_status.inact = ''
    inner join tj02t as sys_status on sys_status.istat = notif_status.stat
                                  and sys_status.spras = $session.system_language
{
    key sys_status.txt04 as statusID,
        sys_status.txt30 as description
} where notif.erdat > dats_add_months($parameters.sydat, -12, '') //THIS CAN'T BE USED!!

Answer 1

Putting built-in functions in RHS position of WHERE is supported only since 7.51 and you have 7.50 as you said. That is why it works for Haojie and not for you.

What can be done here? Possible option is CDS table function which consumes AMDP-class. Consider this sample:

Table function

@EndUserText.label: 'table_func months'
define table function ZTF_MONTHS
with parameters 
@Environment.systemField : #SYSTEM_DATE 
    p_datum : syst_datum
returns {

    mandt : abap.clnt;
    num   : qmnum;
    type  : qmart;
 }
implemented by method zcl_cds_qmel=>get_last_two_years;

AMDP

CLASS zcl_cds_qmel DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    TYPES: tt_statuses TYPE STANDARD TABLE OF qmel.
    CLASS-METHODS get_last_two_years FOR TABLE FUNCTION ztf_months.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.

CLASS zcl_cds_qmel IMPLEMENTATION.
  METHOD get_last_two_years BY DATABASE FUNCTION
          FOR HDB
          LANGUAGE SQLSCRIPT
          OPTIONS READ-ONLY.

    twoyrs := add_months (CURRENT_DATE,-12)

    RETURN SELECT mandt, qmnum AS num, qmart AS type FROM qmel WHERE erdat > twoyrs;
  ENDMETHOD.
  ENDCLASS.

It is very simplified compared to your original task but gives you the idea how to do this.