Technical Articles
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 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 Please follow my profile
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.
Best regards,
Eric
Thanks Eric, definitely the case, so this solution is for anyone on an earlier release to which the above works.
Cheers
Stephen
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 ,
But the below normal CDS view definition throws error as mentioned by Please follow my profile
( 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 ?
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