Skip to Content
Technical Articles
Author's profile photo Stephen Lasham

CDS Views – selection on date plus or minus a number of days or months

Problem

Need to be able to select data in a CDS view where the records selected are less than 12 months old. This needs a comparison of a date field in the view with the system date less 12 months.
The WHERE clause should look something like the following.

Where row_date >= DATS_ADD_MONTHS ($session.system_date,-12,'UNCHANGED')

The problem with this is that the CDS view SQL statement above is not permitted, giving the following error message on activation.

Function DATS_ADD_MONTHS: At position 1, only Expressions, Literals, Columns, Paths, Parameters allowed.

Functions appear to be not permitted for use within a WHERE clause (at least at my release level).

Solution attempt 1

I thought okay, so I need a view that contains the system date less 12 months in it as a field, so I can join to this and compare my date with the calculated field; so I created a new CDS view and tried to insert a field in the output defined as follows

DATS_ADD_MONTHS ($session.system_date,-12,'UNCHANGED') as OneYearAgo

This however generated the same error as when using the function on the where clause, so a no go for me.

Solution attempt 2

I finally solved the issue by creating a specific view to calculate the date less 12 months based on passed parameters.  I made this view flexible to take parameters allowing it to calculate a resulting row with a date plus or minus an increment in either days or months, as follows

@AbapCatalog: { sqlViewName: 'ZIDATEADDINCRMNT',
                compiler.compareFilter: true,
                preserveKey: true }

@ObjectModel: { representativeKey: 'MANDT',
                usageType: { serviceQuality: #A,
                             sizeCategory: 'L',
                             dataClass: #META } }

@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Date add increment view'

define view ZI_DateAddIncrement

  with parameters
    p_IncrementDate : pco_daini @<Environment.systemField:#SYSTEM_DATE, -- Pass in today's date in most case
    p_IncrementAmt  : abap.int4,   -- i.e. -1 or -12 or 1 or 12 for examples
    p_IncrementType : abap.char(1) -- D = Days, M = Months
  as

  select from t001

{
  $parameters.p_IncrementDate as IncrementDate,
  $parameters.p_IncrementAmt  as IncrementAmt,
  $parameters.p_IncrementType as IncrementType,
  case $parameters.p_IncrementType
  when 'D' then DATS_ADD_DAYS( $parameters.p_IncrementDate, $parameters.p_IncrementAmt, 'UNCHANGED' )
  when 'M' then DATS_ADD_MONTHS($parameters.p_IncrementDate, $parameters.p_IncrementAmt, 'UNCHANGED')
  end                         as IncrementedDate
}

where
  t001.bukrs = '1000'

 

The view calculates an incremented date value plus or minus an increment in either days or months, and outputs this as a single row of data.

My view is based over table T001 restricted to delivering just 1 row of output data by use of the WHERE clause, as we only want one output row, containing the date plus or minus the increment. Any table can be used, and preferably a table with only one row ever in it, as this limits any possible performance issues with filtering down to a single row.

Testing the view

In Eclipse I simply open the view in data preview, where I am prompted to enter the parameters as follows.

Testing%20parameters

Testing parameters

Clicking the Open Data Preview button results in the following

Testing%20results

Testing results

Using the view

To use this view, I simply place it as the first data source in my next CDS view, and inner join it with the data source from which I wish to select rows based on the date being less than 12 months. This is illustrated as follows.

define view ZI_ResultsView

as

select from ZI_DateAddIncrement( p_IncrementDate:$session.system_date, p_IncrementAmt:-12, p_IncrementType:'M' ) as OneYearAgo

inner join I_DatedItems as _DatedItem on _DatedItem.mandt = OneYearAgo.mandt
{

_DatedItem.FieldA,
_DatedItem.FieldB

}
where
_DatedItem.DateField >= _OneYearAgo.IncrementedDate

The parameters passed to my initial view ZI_DateAddIncrement return a single row containing the system date less 12 months.  I can then use this on my WHERE clause to limit the selection.

Note: it is important to place the view ZI_DateAddIncrement first in the list so as to only calculate the increment date once when the query is run. The join to the table uses the MANDT field, as this ensures that a join always occurs.

Summary

This solution solves a particular problem encountered where SAP CDS SQL functionality is currently limited, at least at my release level.  It provides a convenient work around solution that can be incorporated into other CDS views that require to select based on an offset to a date, which is a fairly common requirement.  The solution highlights how to implement parameters within a CDS view, which may also be of benefit to those new to CDS views that wish for an example of using parameters.  The naming convention I have used follows that of SAP’s VDM (Virtual Data Model), naming standard for CDS views, and the code illustrates use of some CDS annotations; both these things I would recommend all developers of CDS views adopt.  As part of this blog you have also seen the use of SQL function DATS_ADD_MONTHS and the session variable SYSTEM_DATE.

I had searched the Internet for a solution to this problem, but having found nothing this is what I came up with. It works so I am happy.

Interested in what others think, so please share your thoughts in the comments.

Links

Ask a question or read other’s questions and answers

Read and follow other SQL blogs

Other items I am associated with

Please follow my profile Please follow my profile

 

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Eric Oud
      Eric Oud

      Hi Please follow my profile

      Can this be caused by your S4H release? I've tried your original attempt for one month of data, and on op2021 it works like a charm.

      @AbapCatalog.viewEnhancementCategory: [#NONE]
      @AccessControl.authorizationCheck: #NOT_REQUIRED
      @EndUserText.label: 'Test with dynamic date selection'
      @Metadata.ignorePropagatedAnnotations: true
      
      define view entity ZC_TestDynamicDate
        as select from I_DeliveryDocument as Delivery
      {
        key DeliveryDocument,
            CreationDate
      }
      where
        CreationDate >= DATS_ADD_MONTHS ($session.system_date,-1,'UNCHANGED')

      Best regards,

      Eric

      Author's profile photo Stephen Lasham
      Stephen Lasham
      Blog Post Author

      Thanks Eric, definitely the case, so this solution is for anyone on an earlier release to which the above works.

      Cheers

      Stephen

      Author's profile photo Philip Davy
      Philip Davy

      I have noted that the solution mentioned by https://people.sap.com/g4a_dnl

      works, when the defined view is Entity view. The below Solution works ,

      
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'Test'
      define view entity ZCALDATE_TEST  as select from I_DeliveryDocument as Delivery
      {
        key DeliveryDocument,
            CreationDate
      }
      where
        CreationDate >= DATS_ADD_MONTHS ( $session.system_date, -1, 'UNCHANGED' )

      But the below normal CDS view definition throws error as mentioned by  Please follow my profile

       

      @AbapCatalog.sqlViewName: 'ZDCICALLASTYR'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'Dispocockpit: Calender Date Last Year'
      define view ZDCI_Calender_Last_Year as select from ZDCI_Calender {
          key CalenderDate,
          CalenderYear 
      } where CalenderYear = DATS_ADD_MONTHS ($session.system_date,-12,'UNCHANGED')

      ( Error; Function DATS_ADD_MONTH : At position 1 , only expressions , literals, etc )

      I am also on S4H release 2021. So it does not have to do with the version but the way by which view is declared, I think.

      Ideally it should work in both scenarios while one is permitted.  May be Andrea Schlotthauer can throw some light on this ?

       

       

      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer

      Hi Please follow my profile Philip Davy

      CDS view entities are available since S4H release 2020. They are the recommended option because they offer far more features than classic views.

      Classic views are not further developed since S4H release 2020. => In a classic view, DATS_ADD_MONTHS does not accept session variables and this will not change.

      Classic views have been declared obsolete since S4H release 2022. See the ABAP CDS release news here, list item 14. => you should not create any new classic views because they have many limitations and are not further developed.

      Complete list of new features of CDS view entities can be found here. 

      The topic discussed above, improved expression matrix and nesting of expressions, is described here, list item 7.

      A migration tool from classic view to view entity is available since S4H release 2021. Guideline is here: A new generation of CDS views: how to migrate your CDS views to CDS view entities | SAP Blogs