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 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.
No comments:
Post a Comment