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

[stephenl1]

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 parameters

Clicking the Open Data Preview button results in the following

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 stephenl